Error on CSV upload script

Experts,
I am having problems with a CSV upload script I am using from the knowledgebase.  I have two questions...
Can I use a URL for my .csv file?  
Can you please advise what may be causing the following error:  

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 'In-Wall,NewItem,NewItemDate,Discontinued,Redirect) values('Sku','UPC','Category'' at line 2

Thanks!
//
// CSV into MySQL import script by Mark Randall (mark@hostcobalt.com)
//
// Include the mysql connect page
//include("connect.php");
// Set the filename that you want to import
$filename="http://www.*******.com/Reseller_Datafeed/ResFeed-Current-With-Subcats.csv";
//open the file
$handle = fopen($filename, "r");
//begin looping through the lines
while (($data = fgetcsv($handle, ",")) !== FALSE)
{
//setup the mysql query

$import="INSERT into `CT-products`
(Sku,UPC,Category,Subcategory,ProductName,Headline,BlockCopy,MSRP,Weight,Warranty,Color,PictureSku,Requirements,PackageContains,In-Wall,NewItem,NewItemDate,Discontinued,Redirect) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$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]','$data[18]','$data[19]')";
//execute the mysql query
mysql_query($import) or die(mysql_error());
}
//close the file
fclose($handle);
//output a message saying its done.
echo "Import done";
echo "<BR>";

Open in new window

rlb1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

midhungirishCommented:
well judging from the error i think there is a single or double quote in one of you data which causes mismatch in query... possiblily in the subcategory...
try removing all single quotes from data.. or purify it using mysql_real_escape_string....
0
Shinesh PremrajanEngineering ManagerCommented:
Place the column names in the quotes something like:

$import="INSERT into `CT-products`
(`Sku`,`UPC`,`Category`,`Subcategory`,`ProductName`,`Headline`,`BlockCopy`,`MSRP`,`Weight`,`Warranty`,`Color`,`PictureSku`,`Requirements`,`PackageContains`,`In-Wall`,`NewItem`,`NewItemDate`,`Discontinued`,`Redirect`) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$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]','$data[18]','$data[19]')";


Hope this helps
0
SwafnilCommented:
As midhungirish suggested, use mysql_real_escape_string () to escape each value, otherwise you'll get messages like the one you got when a single quote or backslash is inserted.

Reading remote files is possible, but requires setting "allow_url_fopen" to on in your php.ini:

http://de3.php.net/manual/en/filesystem.configuration.php#ini.allow-url-fopen
http://de3.php.net/manual/en/function.fopen.php

// loop all column values and escape special characters
foreach ($data as $key => $value){
  $data[$key] = mysql_real_escape_string($value);
}
$import="INSERT into `CT-products`
(Sku,UPC,Category,Subcategory,ProductName,Headline,BlockCopy,MSRP,Weight,Warranty,Color,PictureSku,Requirements,PackageContains,In-Wall,NewItem,NewItemDate,Discontinued,Redirect) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$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]','$data[18]','$data[19]')";

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

rlb1Author Commented:
OK, thanks for your help.  I am having an issue with the column names in the feed.  Originally, I had (for example) ProductName.  It should have been Product Name.  How do I overcome the error I am getting because of the space in the field name on the feed?

Product Name
Block Copy
Picture Sku
Package Contains
New Item
New Item Date

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 'Sku,Requirements,`Package Contains`,`In-Wall`,`ROHS?`,`New Item`,`New Item Date`' at line 2

Thanks for your help!!!
$import="INSERT into `CT-products`
(Sku,UPC,Category,Subcategory,`Product Name`,Headline,`Block Copy`,MSRP,Weight,Warranty,Color,Picture Sku,Requirements,`Package Contains`,`In-Wall`,`ROHS?`,`New Item`,`New Item Date`,Discontinued,Redirect) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$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]','$data[18]','$data[19]','$data[20]')"; 

Open in new window

0
rlb1Author Commented:
Code with escape string.
foreach ($data as $key => $value){
  $data[$key] = mysql_real_escape_string($value);
}
$import="INSERT into `CT-products`
(Sku,UPC,Category,Subcategory,`Product Name`,Headline,`Block Copy`,MSRP,Weight,Warranty,Color,Picture Sku,Requirements,`Package Contains`,`In-Wall`,`ROHS?`,`New Item`,`New Item Date`,Discontinued,Redirect) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$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]','$data[18]','$data[19]','$data[20]')"; 

Open in new window

0
SwafnilCommented:
The problem is that you forgot to use accent grave (= `) around "Picture Sku", so it's not interpreted as one column, instead mysql looks for a column "Picture" which will then be aliased as "Sku".
foreach ($data as $key => $value){
  $data[$key] = mysql_real_escape_string($value);
}
$import="INSERT into `CT-products`
(Sku,UPC,Category,Subcategory,`Product Name`,Headline,`Block Copy`,MSRP,Weight,Warranty,Color,`Picture Sku`,Requirements,`Package Contains`,`In-Wall`,`ROHS?`,`New Item`,`New Item Date`,Discontinued,Redirect) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$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]','$data[18]','$data[19]','$data[20]')"; 

Open in new window

0
rlb1Author Commented:
Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.