Resolved Help needed discerning this error

mOrloff · 03-29-2010, 05:20 PM

I have a script which is throwing an error, and I'm not making much sense of it.
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 '6'' at line 2

Since this error in no way reflects what is at line two of my PHP script, I'm not sure where to look.
I did look at the SQL within the script, and other than the connection stuff (which has nothing resmbling the error), the first MySQL string is an INSERT statement broken over 2 lines.
I echoed it out, and I'm not seeing a match to the error.

Am I on the right/wrong track ??

~ Mo

tomws · 03-29-2010, 05:47 PM

Quote:

Originally Posted by mOrloff

I echoed it out, and I'm not seeing a match to the error.

But you didn't paste it here?

mOrloff · 03-29-2010, 05:51 PM

Here's a snip of the echo (which goes on for several hundred thousand rows).

0) INSERT INTO `temp` (`partnumber` ,`description` ,`condition` ,`category` ,`qty` ,`brand`,`uniqueid` ,`price`) VALUES ('Part Number','Description','Condition','Category','Quantity','Mfg','UniqueID','Price')

1) INSERT INTO `temp` (`partnumber` ,`description` ,`condition` ,`category` ,`qty` ,`brand`,`uniqueid` ,`price`) VALUES (' F02A250V4A',' F02A250V4A','','','0','','5b2cc04434ce4622b008032a985f0328','0')

2) INSERT INTO `temp` (`partnumber` ,`description` ,`condition` ,`category` ,`qty` ,`brand`,`uniqueid` ,`price`) VALUES (' FHL18G2-2',' FHL18G2-2','','','0','','e83a5c0b236a4a7a8f843109b377a59a','0')

3) INSERT INTO `temp` (`partnumber` ,`description` ,`condition` ,`category` ,`qty` ,`brand`,`uniqueid` ,`price`) VALUES (' FHL18G2-9',' FHL18G2-9','','','0','','75f07713e4d743aeb1fb31e13fe553d7','0')

tomws · 03-29-2010, 05:54 PM

There's no 6' in that section. Check the others that you didn't paste. You may have picked up a stray apostrophe. It's a simple mysql syntax error, so one of the queries is flawed.

By the way, did you know you can insert multiple rows with one INSERT statement? Then you end up using far fewer queries than this.

masterofollies · 03-29-2010, 06:19 PM

Appears to be missing an opening single quote before the 6.

mOrloff · 03-29-2010, 06:28 PM

Ahh, I had searched for '6'', not noticing that the outer single-quotes were just delineating the string (without any results of course).

As for 6', there are probably a couple thousand matches to that tiny string. Do you have any pointers for a quick way to locate the problem ??

Going on the presumption that I picked up a stray apostrophe, I ran a find and ticked my way through the first 17 thousand rows, and all '6' strings were properly surrounded by commas, and my brain is becoming too numb to accurately plink through the rest of the records this same way.
Pointers ??

Quote:

Originally Posted by tomws

... By the way, did you know you can insert multiple rows with one INSERT statement?...

No, I didn't. That's awesome.
I'll try to pull up some info on that. Thanks.
(Any further input is always welcome.)

mOrloff · 03-29-2010, 06:35 PM

Quote:

Originally Posted by masterofollies

Appears to be missing an opening single quote before the 6.

A search for ,6 comes up with no results as well.

~ mo

_Aerospace_Eng_ · 03-29-2010, 07:17 PM

In the query call do you have an or die there that contains some idemtifying text and pronts out the failed query?

tomws · 03-29-2010, 07:17 PM

You can narrow it down by checking the last successfully inserted `partnumber` or `uniqueid`, then find that in the query dump. The next INSERT may be the problem.

Fou-Lu · 03-29-2010, 08:02 PM

Run this:

Code:

SELECT @@sql_mode;

Followed by this:

Code:

SHOW CREATE TABLE `temp`;

And post the results please. Either through CLI or PHP will be fine, but make sure you view source to preserve the newlines.

Edit:
Also, can you post the PHP code you're using to build the SQL for the insertions please?

masterofollies · 03-29-2010, 08:03 PM

Quote:

Originally Posted by mOrloff

A search for ,6 comes up with no results as well.

~ mo

You posted comma 6 which isn't a single quote. Either post the full script if possible, or search for every query that has 6 in it.

Also if it doesn't have 6, that means it's a pass query result, meaning the value from what is being inserted or updated is the value of 6, so it wouldn't show in the script.

mOrloff · 03-29-2010, 10:09 PM

- PAUSE -

Thanks-a-bunch for all the help thus far.

I've gotta split, and will be offsite through all of tomorrow.
I will pick this back up on Wed morn.

Till then,
~ Mo

Thanks again.

mOrloff · 03-29-2010, 10:16 PM

Quote:

Originally Posted by masterofollies

You posted comma 6 which isn't a single quote...

Correct, that was based on the suggestion that an opening quote may have been missing.
If I understand correctly, that would lead to a string of something like ...'value',6','value',...

And, yes, you are correct, the values are getting passed into the sql via php vars.

See ya
~ Mo

mOrloff · 03-31-2010, 03:53 PM

Well, I'm back, and I suspect that the problem was in the data being grabbed for the INSERT rather than the script itself.
After a change of that file, everything seems to be working.

I'll post again if I hit another brick wall.

~ Mo