autoupdating datafeed database

[jerastraub]

Here what I have thus far:

[code]<?
              $dbh=mysql_connect ("localhost", "<UserName>", "<Password>")
            or die ('I cannot connect to the database because: ' . mysql_error());
            mysql_select_db ("<DatabaseName>");
     
                // File can be anywhere on the Internet
                $FeedFile = 'http://<path to file>/YourCJFeed.txt.gz';
             
                mysql_query("drop table example_temp");
                mysql_query("CREATE TABLE example_temp (
          id INT NOT NULL AUTO_INCREMENT,
          PRIMARY KEY (id),
          Name varchar(100) NOT NULL default '',
          Merchant blob NOT NULL,
          Description blob NOT NULL,
          Sku varchar(50) NOT NULL default '',
          Brand varchar(50) NOT NULL default '',
          SalePrice varchar(7) NOT NULL default '0.00',
          Price varchar(7) NOT NULL default '0.00',
          RetailPrice varchar(7) NOT NULL default '0.00',
          LinkURL blob NOT NULL,
          ImpressionURL blob NOT NULL,
          ImageURL blob NOT NULL,
          Currency blob NOT NULL,
          Category varchar(100) NOT NULL default '')") or die(mysql_error());
             
                $feed = gzopen($FeedFile, 'r');
             
                $rowNum = 0;
                $recCount = 0;
             
                while($data = fgetcsv($feed, 3000, "\t")){
                    if($rowNum > 0){
     
              $iName            = addslashes($data[3]);
              $iMerchant            = addslashes($data[0]);
              $iDescription            = addslashes($data[5]);
              $iSku            = addslashes($data[6]);
              $iBrand            = addslashes($data[7]);
              $iSalePrice            = addslashes($data[12]);
              $iPrice            = addslashes($data[13]);
              $iRetailPrice            = addslashes($data[14]);
              $iLinkURL            = addslashes($data[16]);
              $iImpressionURL            = addslashes($data[17]);
              $iImageURL            = addslashes($data[18]);
    $iCurrency        = addslashes($data[11]);   
              $iCategory            = addslashes($data[19]);
     
        $sql = mysql_query("insert into example_temp (Name, Merchant, Description, Sku, Brand, SalePrice, Price, RetailPrice, LinkURL, ImpressionURL, ImageURL, Currency, Category)
              values
        ('$iName', '$iMerchant', '$iDescription', '$iSku', '$iBrand', '$iSalePrice', '$iPrice', '$iRetailPrice', '$iLinkURL', '$iImpressionURL', '$iImageURL', '$iCurrency', '$iCategory')") or die(mysql_error());
              $recCount++;
                }
             
                mysql_query("drop table example");
        mysql_query("ALTER TABLE example_temp RENAME example") or die(mysql_error());
             
                $to = "<Your email Address>";
                $subj = "Commission Junction Database Table Update Report";
            $mssg = "This is an automated email. The CJ Table update has
            completed successfully.\n\nThe total number or items loaded was
            $recCount.\n\n";
                $hdrs = "From: $to\n";
                mail($to, $subj, $mssg, $hdrs);
              gzclose ($FeedFile);
          echo "CJ <Merchant Name> Import Completed Successfully"; ?> [/code]

but I can't figure out how to separate the Category. As most of the time the category field looks something like:

Special Occasions~~Gifts For Her

or

Camp/Travel/Camping/Hiking/Lighting/Lanterns

I was doing good til I got to this point!

[jerastraub]

anyone have any ideas on how to do this, i have looked through several forum to no avail.

[fert]

you mean the WHERE keyword?

[jerastraub]

This a php script to auto upload a datafeed file to a database. I am trying to figure out how to separate the Category. As most of the time the category field looks something like:

Special Occasions~~Gifts For Her

or

Camp/Travel/Camping/Hiking/Lighting/Lanterns

and create additional columns in the table. Such as in this example:

Special Occasions~~Gifts For Her

it would create a Category and subcategory.

[jerastraub]

Isn't there someone out there that knows or has an ideas on how to get this?   

[jerastraub]

Am I in the wrong area for this question?