[SOLVED] Upload datafeed to my sql

jerastraub · March 23, 2007

Here's what i have so far:

<?
             $dbh=mysql_connect ("localhost", "Username", "password") 
            or die ('I cannot connect to the database because: ' . mysql_error()); 
            mysql_select_db ("Database"); 
        
                  // File can be anywhere on the Internet
             $FeedFile = 'http://fssrv.com/api.aspx?req=3&fmt=2&adid=77&afid=1213797&fid=104&cid=-1&s=1&kt=1';
                
          mysql_query("drop table example_temp");
                  mysql_query("CREATE TABLE example_temp (
           PRIMARY KEY (MfgCode),
	  ProductName varchar(100) NOT NULL, 
          MfgCode varchar(100) NOT NULL,
	  SKU varchar(50) NOT NULL,
	  UPC varchar(50) NOT NULL,
	  Category varchar(100) NOT NULL,
	  SubCategory varchar(100) NOT NULL,
	  Brand varchar(50) NOT NULL, 
          Description blob NOT NULL, 
	  Keywords blob NOT NULL, 
	  Price varchar(7) NOT NULL default '0.00', 
	  MSRP varchar(7) NOT NULL default '0.00', 
	  IsNew varchar(50) NOT NULL,
	  InStock varchar(50) NOT NULL,
	  ProductURL varchar(100) NOT NULL,
	  SmallImageURL varchar(100) NOT NULL,
	  BigImageURL varchar(100) NOT NULL,
	  Shipping varchar(100) NOT NULL,
	  BulkPrice varchar(100) NOT NULL,
	  BulkMinimum varchar(100) NOT NULL)") or die(mysql_error());
                
                  $feed = gzopen($FeedFile, 'r');
                
                  $rowNum = 0;
                  $recCount = 0;
                
                  while($data = fgetcsv($feed, 3000, "\t")){
                      if($rowNum > 0){
        


$iProductName         = addslashes($data[0]);
$iMfgCode         = addslashes($data[1]);
$iSKU         = addslashes($data[2]);
$iUPC         = addslashes($data[3]);
$iCategory         = addslashes($data[4]);
$iBrand         = addslashes($data[5]);
$iDescription         = addslashes($data[6]);
$iKeywords         = addslashes($data[7]);
$iPrice         = addslashes($data[8]);
$iMSRP         = addslashes($data[9]);
$iIsNew         = addslashes($data[10]);
$iInStock         = addslashes($data[11]);
$iProductURL         = addslashes($data[12]);
$iSmallImageURL         = addslashes($data[13]);
$iBigImageURL         = addslashes($data[14]);
$iShipping         = addslashes($data[15]);
$iBulkPrice         = addslashes($data[16]);
$iBulkMinimum         = addslashes($data[17]);

$pieces = explode(" ", $iCategory);
echo $pieces[0]; // piece1
echo $pieces[1]; // piece2
echo $pieces[2]; // piece3
        
          $sql = mysql_query("insert into example_temp (ProductName, MfgCode, SKU, UPC, Category, SubCategory, Brand, Description, Keywords, Price, MSRP, IsNew, InStock, ProductURL, SmallImageURL, BigImageURL, Shipping, BulkPrice, BulkMinimum)
                values
         (''$iProductName', '$iMfgCode', '$iSKU', '$iUPC', '$pieces[0]', '$pieces[2]', '$iBrand', '$iDescription', '$iKeywords', '$iPrice', '$iMSRP', '$iIsNew', '$iInStock', '$iProductURL', '$iSmallImageURL', '$iBigImageURL', '$iShipping', '$iBulkPrice', '$iBulkMinimum' )") or die(mysql_error());
                $recCount++;
      }
      $rowNum++;
  }
                
                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 ($feed);
             echo "CJ <Merchant Name> Import Completed Successfully"; ?>

I am getting the follow error and can't figure out how to fix it:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AC Racing A-Arm Guards for Honda 400EX 98-06,\"F-21496,\",\",atv

Please help!

artacus · March 23, 2007

Well it looks to me like your quotes and commas are possibly messed up.

jerastraub · March 23, 2007

Can you tell me where your refering?

artacus · March 24, 2007

It means your csv file is not being parsed correctly.

I downloaded your source data and your line should look like this:

<?php
$data = fgetcsv($feed, 3000, ',', '"')

Use that and do a print_r($data) to be sure its working correctly.

jerastraub · March 24, 2007

I used

 while($data = fgetcsv($feed, 3000, ',', '"')){
                      if($rowNum > 0){

Yay, A different error....

atvs/~general/~ATVSkidPlatesYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AC Racing A-Arm Guards for Honda 400EX 98-06', 'F-21496', '', ''

I don't know where I am going wrong.

jerastraub · March 24, 2007

It's creating the table correctly:

CREATE TABLE `example_temp` (
  `ProductName` varchar(100) NOT NULL default '',
  `MfgCode` varchar(100) NOT NULL default '',
  `SKU` varchar(50) NOT NULL default '',
  `UPC` varchar(50) NOT NULL default '',
  `Category` varchar(100) NOT NULL default '',
  `SubCategory` varchar(100) NOT NULL default '',
  `Brand` varchar(50) NOT NULL default '',
  `Description` blob NOT NULL,
  `Keywords` blob NOT NULL,
  `Price` varchar(7) NOT NULL default '0.00',
  `MSRP` varchar(7) NOT NULL default '0.00',
  `IsNew` varchar(50) NOT NULL default '',
  `InStock` varchar(50) NOT NULL default '',
  `ProductURL` varchar(100) NOT NULL default '',
  `SmallImageURL` varchar(100) NOT NULL default '',
  `BigImageURL` varchar(100) NOT NULL default '',
  `Shipping` varchar(100) NOT NULL default '',
  `BulkPrice` varchar(100) NOT NULL default '',
  `BulkMinimum` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`MfgCode`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Seems like the issue starts when it starts processing the products cells after the title columns.

So something is amiss, ut i can't find it!

artacus · March 25, 2007

Did you do a print_r($data) like I said?

artacus · March 25, 2007

and change mysql_query($query) or die(mysql_error() . "<pre>\n$query");

So you can see what your sql looks like

jerastraub · March 25, 2007

Opps sorry forgot to post the results, Here is the output from the Print_r

Array ( [0] => ProductName [1] => MfgCode [2] => SKU [3] => UPC [4] => Category [5] => Brand [6] => Description [7] => Keywords [8] => Price [9] => MSRP [10] => OnSale [11] => IsNew [12] => InStock [13] => ProductURL [14] => SmallImageURL [15] => BigImageURL [16] => Shipping [17] => BulkPrice [18] => BulkMinimum ) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ProductName', 'MfgCode', 'SKU', 'UPC', 'Category', '', 'Brand',

I'm not sure where you are wanting me to put this:

and change mysql_query($query) or die(mysql_error() . "

\n$query");So you can see what your sql looks like

artacus · March 25, 2007

Really need to do your print_r for the second line where you're dealing with the data and not the headers.

And save your query as a variable first.

<?php
$sql = "INSERT INTO example_table...";
$result = mysql_query($sql) or die(mysql_error() . "/n<pre>$sql");

jerastraub · March 25, 2007

Here's my code so far:

<?
            
		<?
             $dbh=mysql_connect ("localhost", "Username", "password") 
            or die ('I cannot connect to the database because: ' . mysql_error()); 
            mysql_select_db ("Database"); 
        
                  // File can be anywhere on the Internet
             $FeedFile = 'http://fssrv.com/api.aspx?req=3&fmt=2&adid=77&afid=1213797&fid=104&cid=-1&s=1&kt=1';
                
          mysql_query("drop table example_temp");
                  mysql_query("CREATE TABLE example_temp (
           PRIMARY KEY (MfgCode),
	  ProductName varchar(100) NOT NULL, 
          MfgCode varchar(100) NOT NULL,
	  SKU varchar(50) NOT NULL,
	  UPC varchar(50) NOT NULL,
	  Category varchar(100) NOT NULL,
	  SubCategory varchar(100) NOT NULL,
	  Brand varchar(50) NOT NULL, 
          Description blob NOT NULL, 
	  Keywords blob NOT NULL, 
	  Price varchar(7) NOT NULL default '0.00', 
	  MSRP varchar(7) NOT NULL default '0.00', 
	  IsNew varchar(50) NOT NULL,
	  InStock varchar(50) NOT NULL,
	  ProductURL varchar(100) NOT NULL,
	  SmallImageURL varchar(100) NOT NULL,
	  BigImageURL varchar(100) NOT NULL,
	  Shipping varchar(100) NOT NULL,
	  BulkPrice varchar(100) NOT NULL,
	  BulkMinimum varchar(100) NOT NULL)") or die(mysql_error());
                
                  $feed = fopen($FeedFile, 'r');
			  
                  $rowNum = 1;
                  $recCount = 0;
                
                  while($data = fgetcsv($feed, 3000, ',')){
                      if($rowNum > 0){
        


$iProductName         = addslashes($data[0]);
$iMfgCode         = addslashes($data[1]);
$iSKU         = addslashes($data[2]);
$iUPC         = addslashes($data[3]);
$iCategory         = addslashes($data[4]);
$iBrand         = addslashes($data[5]);
$iDescription         = addslashes($data[6]);
$iKeywords         = addslashes($data[7]);
$iPrice         = addslashes($data[8]);
$iMSRP         = addslashes($data[9]);
$iIsNew         = addslashes($data[10]);
$iInStock         = addslashes($data[11]);
$iProductURL         = addslashes($data[12]);
$iSmallImageURL         = addslashes($data[13]);
$iBigImageURL         = addslashes($data[14]);
$iShipping         = addslashes($data[15]);
$iBulkPrice         = addslashes($data[16]);
$iBulkMinimum         = addslashes($data[17]);

$pieces = explode(" ", $data[4]);

$pieces[0]; // piece1
$pieces[1]; // piece2
$pieces[2]; // piece3

print_r ($data);
        
          $sql = mysql_query("insert into example_temp (ProductName, MfgCode, SKU, UPC, Category, SubCategory, Brand, Description, Keywords, Price, MSRP, IsNew, InStock, ProductURL, SmallImageURL, BigImageURL, Shipping, BulkPrice, BulkMinimum)
                values
         (''$iProductName', '$iMfgCode', '$iSKU', '$iUPC', '$pieces[0]', '$pieces[2]', '$iBrand', '$iDescription', '$iKeywords', '$iPrice', '$iMSRP', '$iIsNew', '$iInStock', '$iProductURL', '$iSmallImageURL', '$iBigImageURL', '$iShipping', '$iBulkPrice', '$iBulkMinimum' )") or die(mysql_error());


                $recCount++;
      }
      $rowNum++;
  }
                
                mysql_query("drop table example");
           mysql_query("ALTER TABLE example_temp RENAME example") or die(mysql_error());
                
             


		 ?>

I don't understand where you are talking about putting:

$sql = "INSERT INTO example_table...";
$result = mysql_query($sql) or die(mysql_error() . "/n<pre>$sql");

jerastraub · March 25, 2007

Okay,

Don't pay attention to the last post.

I finally got it to where it will insert values in to the temp database:

<?
            
		 $dbh=mysql_connect ("localhost", "Username", "password") 
            or die ('I cannot connect to the database because: ' . mysql_error()); 
        
                  // File can be anywhere on the Internet
             $FeedFile = 'http://fssrv.com/api.aspx?req=3&fmt=2&adid=77&afid=1213797&fid=104&cid=-1&s=1&kt=1';
                
          mysql_query("drop table example_temp");
                  mysql_query("CREATE TABLE example_temp (
          ProductName varchar(100) NOT NULL, 
          MfgCode varchar(100) NOT NULL,
	  SKU varchar(50) NOT NULL,
	  UPC varchar(50) NOT NULL,
	  Category varchar(100) NOT NULL,
	  SubCategory varchar(100) NOT NULL,
	  Brand varchar(50) NOT NULL, 
          Description blob NOT NULL, 
	  Keywords blob NOT NULL, 
	  Price varchar(7) NOT NULL default '0.00', 
	  MSRP varchar(7) NOT NULL default '0.00', 
	  IsNew varchar(50) NOT NULL,
	  InStock varchar(50) NOT NULL,
	  ProductURL varchar(100) NOT NULL,
	  SmallImageURL varchar(100) NOT NULL,
	  BigImageURL varchar(100) NOT NULL,
	  Shipping varchar(100) NOT NULL,
	  BulkPrice varchar(100) NOT NULL,
	  BulkMinimum varchar(100) NOT NULL)") or die(mysql_error());
                
                  $feed = fopen($FeedFile, 'r');
			  
                  $rowNum = 1;
                  $recCount = 0;
                
                  while ($data = fgetcsv ($feed, 1000, ",")) {
			  
			  
			  $pieces = explode("/~", $data[4]);

$pieces[0]; // piece1
$pieces[1]; // piece2
$pieces[2]; // piece3
			  
    $query = "insert into example_temp (`ProductName`, `MfgCode`, `SKU`, `UPC`, `Category`, `SubCategory`, `Brand`, `Description`, `Keywords`, `Price`, `MSRP`, `IsNew`, `InStock`, `ProductURL`, `SmallImageURL`, `BigImageURL`, `Shipping`, `BulkPrice`, `BulkMinimum`) VALUES('".$data[0]."', '".$data[1]."', '".$data[2]."', '".$data[3]."', '".$pieces[0]."', '".$pieces[2]."', '".$data[5]."', '".$data[6]."', '".$data[7]."', '".$data[8]."', '".$data[9]."', '".$data[10]."', '".$data[11]."', '".$data[12]."', '".$data[13]."', '".$data[14]."', '".$data[15]."', '".$data[16]."', '".$data[17]."')";
    $result = mysql_query($query) or die("Invalid query: " . mysql_error());
    $row++;
}
                
                mysql_query("drop table example");
           mysql_query("ALTER TABLE example_temp RENAME example") or die(mysql_error());
                
             


		 ?>

However I am now getting the follow error:

Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's left wheel spacer and allows all maintenance jobs such as tire

I'm getting closer, just not there yet!

MadTechie · March 25, 2007

Ok the data your pulling in contains single quotes (')

so the inserts are getting messed up,,

your need to clean them up str_replace shoukd work

str_replace($data[4], "'", "''");

jerastraub · March 25, 2007

Thanks guys,

Here is the end result code that works perfectly:

<?
            
$dbh=mysql_connect ("localhost", "Username", "password") 
            or die ('I cannot connect to the database because: ' . mysql_error()); 
            mysql_select_db ("Database"); 
        
                  // File can be anywhere on the Internet
             $FeedFile = 'http://fssrv.com/api.aspx?req=3&fmt=2&adid=77&afid=1213797&fid=104&cid=-1&s=1&kt=1';
                
          mysql_query("drop table example_temp");
                  mysql_query("CREATE TABLE example_temp (
          ProductName varchar(100) NOT NULL, 
          MfgCode varchar(100) NOT NULL,
	  SKU varchar(50) NOT NULL,
	  UPC varchar(50) NOT NULL,
	  Category varchar(100) NOT NULL,
	  SubCategory varchar(100) NOT NULL,
	  Brand varchar(50) NOT NULL, 
          Description blob NOT NULL, 
	  Keywords blob NOT NULL, 
	  Price varchar(7) NOT NULL default '0.00', 
	  MSRP varchar(7) NOT NULL default '0.00', 
	  IsNew varchar(50) NOT NULL,
	  InStock varchar(50) NOT NULL,
	  ProductURL varchar(100) NOT NULL,
	  SmallImageURL varchar(250) NOT NULL,
	  BigImageURL varchar(250) NOT NULL,
	  Shipping varchar(100) NOT NULL,
	  BulkPrice varchar(100) NOT NULL,
	  BulkMinimum varchar(100) NOT NULL)") or die(mysql_error());
                
                  $feed = fopen($FeedFile, 'r');
			  
                  $rowNum = 1;
                  $recCount = 0;
                
                  while ($data = fgetcsv ($feed, 3000, ",")) {
			  
			  
$pieces = explode("/~", $data[4]);

$pieces[0]; // piece1
$pieces[1]; // piece2
$pieces[2]; // piece3

$data[0] = str_replace("'", "‚", $data[0]);
$data[1] = str_replace("'", "‚", $data[1]);
$data[2] = str_replace("'", "‚", $data[2]);
$data[3] = str_replace("'", "‚", $data[3]);
$data[4] = str_replace("'", "‚", $data[4]);
$data[5] = str_replace("'", "‚", $data[5]);
$data[6] = str_replace("'", "‚", $data[6]);
$data[7] = str_replace("'", "‚", $data[7]);
$data[8] = str_replace("'", "‚", $data[8]);
$data[9] = str_replace("'", "‚", $data[9]);
$data[10] = str_replace("'", "‚", $data[10]);
$data[11] = str_replace("'", "‚", $data[11]);
$data[12] = str_replace("'", "‚", $data[12]);
$data[13] = str_replace("'", "‚", $data[13]);
$data[14] = str_replace("'", "‚", $data[14]);
$data[15] = str_replace("'", "‚", $data[15]);
$data[16] = str_replace("'", "‚", $data[16]);
$data[17] = str_replace("'", "‚", $data[17]);


			  
    $query = "insert into example_temp (`ProductName`, `MfgCode`, `SKU`, `UPC`, `Category`, `SubCategory`, `Brand`, `Description`, `Keywords`, `Price`, `MSRP`, `IsNew`, `InStock`, `ProductURL`, `SmallImageURL`, `BigImageURL`, `Shipping`, `BulkPrice`, `BulkMinimum`) VALUES('".$data[0]."', '".$data[1]."', '".$data[2]."', '".$data[3]."', '".$pieces[0]."', '".$pieces[2]."', '".$data[5]."', '".$data[6]."', '".$data[7]."', '".$data[8]."', '".$data[9]."', '".$data[10]."', '".$data[11]."', '".$data[12]."', '".$data[13]."', '".$data[14]."', '".$data[15]."', '".$data[16]."', '".$data[17]."')";
    $result = mysql_query($query) or die("Invalid query: " . mysql_error());
    $row++;
}
                
                mysql_query("drop table example");
           mysql_query("ALTER TABLE example_temp RENAME example") or die(mysql_error());
                
             
		 echo "Datafeed Import Completed Successfully"; 


$sql = "SELECT COUNT(*) FROM example";
       $result = mysql_query($sql);
if($result)
{
  $row = mysql_fetch_row($result);
  $count = $row[0];
  echo "<br />";
   echo "There are ";
   echo $count;
   echo " records in the database.";

} 
else
{
  echo "no result from database.";
}

?>

Also I used ‚ as the replacement character rather than ", as I need the apostrophe to diplay in the merchant URLs and product names and descriptions.

One last thing though, is there a way to shorten the piece of code and still get the same result :

$data[0] = str_replace("'", "‚", $data[0]);
$data[1] = str_replace("'", "‚", $data[1]);
$data[2] = str_replace("'", "‚", $data[2]);
$data[3] = str_replace("'", "‚", $data[3]);
$data[4] = str_replace("'", "‚", $data[4]);
$data[5] = str_replace("'", "‚", $data[5]);
$data[6] = str_replace("'", "‚", $data[6]);
$data[7] = str_replace("'", "‚", $data[7]);
$data[8] = str_replace("'", "‚", $data[8]);
$data[9] = str_replace("'", "‚", $data[9]);
$data[10] = str_replace("'", "‚", $data[10]);
$data[11] = str_replace("'", "‚", $data[11]);
$data[12] = str_replace("'", "‚", $data[12]);
$data[13] = str_replace("'", "‚", $data[13]);
$data[14] = str_replace("'", "‚", $data[14]);
$data[15] = str_replace("'", "‚", $data[15]);
$data[16] = str_replace("'", "‚", $data[16]);
$data[17] = str_replace("'", "‚", $data[17]);

Thanks again guys!

MadTechie · March 25, 2007

Cool click solved

PS i used 2xsingle quotes (') on the replace

jerastraub · March 25, 2007

Cool click solved

PS i used 2xsingle quotes (') on the replace

I don't see a solved button anywhere. I feel like a noob!

MadTechie · March 25, 2007

OK on the bottom of this screen (the one your reading from right now) on the blue bar

PS i couldn't first time i looked

jerastraub · March 25, 2007

Okay, one last question. How do I exclude the first row of the CVS when uploading, as these are the row titles?

ie.

ProductName	MfgCode	SKU	UPC	Category		Brand	Description	Keywords	Price	MSRP	OnSale	IsNew	InStock	ProductURL	SmallImageURL	BigImageURL	Shipping	BulkPrice

jerastraub · March 25, 2007

I added

mysql_query(" DELETE FROM example_temp WHERE ProductName='ProductName'") or die(mysql_error());

so i asked my own question, rofl

Sign In

[SOLVED] Upload datafeed to my sql

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information