Page 1 of 1
What's wrong with this MySQL syntax?
Posted: Tue Mar 30, 2004 9:58 am
by mjseaden
Dear All
Can you tell me what is wrong with this?
Code: Select all
$result = mysql_query('UPDATE Items SET
ItemType='.$_SESSION['item_type'].',
ItemDev='.$_SESSION['item_dev'].',
ItemTitle='''.$_SESSION['item_title'].''',
PictureURL='''.$_SESSION['item_ID'].''',
Price='.$_SESSION['item_price'].',
DescriptionSmall='''.$_SESSION['smalldesc'].''',
DescriptionBig='''.$_SESSION['bigdesc'].''',
CountryID='.$_SESSION['CountryID'].',
LocationID='.$_SESSION['location_id'].'
WHERE (ItemID='.$itemid.')');
Thank you very much
Mark
Posted: Tue Mar 30, 2004 10:02 am
by JayBird
do you get an error?
Mark
Posted: Tue Mar 30, 2004 10:05 am
by mjseaden
Error: Could not perform MySQL 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 ' LocationID=0 WHERE (ItemID=4)' at line 11
Posted: Tue Mar 30, 2004 10:11 am
by magicrobotmonkey
try losing the parenthesis (sp?)
Re: What's wrong with this MySQL syntax?
Posted: Tue Mar 30, 2004 10:12 am
by patrikG
Try
Code: Select all
$result = mysql_query('UPDATE Items SET
ItemType='.$_SESSION['item_type'].',
ItemDev='.$_SESSION['item_dev'].',
ItemTitle='''.$_SESSION['item_title'].''',
PictureURL='''.$_SESSION['item_ID'].''',
Price='.$_SESSION['item_price'].',
DescriptionSmall='''.$_SESSION['smalldesc'].''',
DescriptionBig='''.$_SESSION['bigdesc'].''',
CountryID='.$_SESSION['CountryID'].',
LocationID='.$_SESSION['location_id'].'
WHERE ItemID="'.$itemid.'"');
Posted: Tue Mar 30, 2004 10:19 am
by pickle
For queries like that, it's always better to put the query in heredocs, as there's no escaping into and out of a string.
Code: Select all
$query = <<<SQL
UPDATE Items
SET
ItemType='$_SESSION['item_type']',
ItemDev='$_SESSION['item_dev']',
ItemTitle='$_SESSION['item_title']',
PictureURL='$_SESSION['item_ID']',
Price='$_SESSION['item_price']',
DescriptionSmall='$_SESSION['smalldesc']',
DescriptionBig='$_SESSION['bigdesc']',
CountryID='$_SESSION['CountryID']',
LocationID='$_SESSION['location_id']'
WHERE
ItemID=$itemid
SQL;
$result = mysql_query($query);
At first glance, I notice that you don't need the parentheses around the where clause. Also for some entries you don't have the escaped ', where in others you do. That's probably generating the error, and that's why I use heredocs.
Posted: Tue Mar 30, 2004 10:30 am
by mjseaden
Hi Patrik
I'm now getting this error:
Error: Could not perform MySQL 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 ' LocationID=0 WHERE ItemID=3' at line 11
I've removed the "" from the ItemID, I initially tried it with that but it didn't work, and the ItemID is an int on the database.
Thanks
Mark
Posted: Tue Mar 30, 2004 10:36 am
by mjseaden
Pickle
I have pasted your extract into the program, it's generating the following error:
Parse error: parse error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in \\nas09ent\domains\s\XXXX\user\htdocs\run2.php on line 4090
Posted: Tue Mar 30, 2004 11:21 am
by pickle
That's probably because you've got whitespace before or after the "SQL;" line. The closing of the heredocs has to be the ONLY thing on the line - no whitespace.
Posted: Tue Mar 30, 2004 11:31 am
by twigletmac
In heredoc you need to use curly brackets around array elements:
Code: Select all
$query = <<<SQL
UPDATE Items
SET
ItemType='{$_SESSION['item_type']}',
ItemDev='{$_SESSION['item_dev']}',
ItemTitle='{$_SESSION['item_title']}',
PictureURL='{$_SESSION['item_ID']}',
Price='{$_SESSION['item_price']}',
DescriptionSmall='{$_SESSION['smalldesc']}',
DescriptionBig='{$_SESSION['bigdesc']}',
CountryID='{$_SESSION['CountryID']}',
LocationID='{$_SESSION['location_id']}'
WHERE
ItemID=$itemid
SQL;
Mac
Posted: Tue Mar 30, 2004 11:38 am
by pickle
twigletmac wrote:In heredoc you need to use curly brackets around array elements:
In my experience, you don't need to do that around arrays one level deep. By that I mean $my_array['element1'] will parse out, but $my_array['element1']['subemelent1'] wont.
Posted: Tue Mar 30, 2004 11:46 am
by Unipus
Error: Could not perform MySQL 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 ' LocationID=0 WHERE ItemID=3' at line 11
I believe your values should be quoted? So instead of LocationID=0 WHERE ItemID=3, it should be LocationID='0' WHERE ItemID='3'