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 to create additional columns in the table. This would create a Category and subcategory field in my database.
. As most of the time the category field looks something like:

Special Occasions~~Gifts For Her

or

Camping/Lighting/Lanterns


I was doing good til I got to this point!

[fenway]

I'd not sure I understand what you mean.

[jerastraub]

Let me explain it further:

Okay this is the makeup of most merchant datafeeds:

[code] Name Merchant Description Sku Brand SalePrice Price RetailPrice LinkURL ImpressionURL ImageURL Currency Category [/code]

Where the Category field in the speadsheet is:

Special Occasions~~Gifts For Her

or

Camping/Lighting/Lanterns

Since I am trying to make this speadsheet upload script, I would like to know if it is possible to make it so when it creates the database that is will make a Category and Subcategory field from what is given on the speadsheet.

so in this example : Special Occasions~~Gifts For Her

It would make a Column in the database with Special Occasions in Category and Gifts For Her in the SubCategory Column.

If there is Camping/Lighting/Lanterns:

It would make a Column in the database with Camping in Category and Lighting in the SubCategory and Lanterns in the Department Column.

As I have a few merchants that has rows in the thousands and tens of thousands. And right now I am having to manually edit the speadsheets.  Which leaves less time for promoting.
I have a script that does word replacement to help with duplicate listing. I try to provide content that is different while still keep with the merchants requirements.
I hope this explains it well!

Jerald

[fenway]

Sounds like you should "split" the column.... but it's probably much easier to do after the data is already in the database.

[jerastraub]

How would I use this "split" column feature you are refering to?

[jerastraub]

"Bump"

[fenway]

What I mean is to dump that concatenating value into a temporary field, and then parse it appropriately, putting the right pieces into the right columns.  There is no such "feature", thoug.

[jerastraub]

Are you talking about creating another temporary table? I'm am not sure if I follow you!

[fenway]

No, I mean but the concatenated value into an extra column in that table.