What's wrong with this MySQL syntax?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

What's wrong with this MySQL syntax?

Post 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
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

do you get an error?

Mark
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

Post 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
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post by magicrobotmonkey »

try losing the parenthesis (sp?)
User avatar
patrikG
DevNet Master
Posts: 4235
Joined: Thu Aug 15, 2002 5:53 am
Location: Sussex, UK

Re: What's wrong with this MySQL syntax?

Post 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.'"');
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

Post 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
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

Post 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
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Unipus
Forum Contributor
Posts: 409
Joined: Tue Aug 26, 2003 2:06 pm
Location: Los Angeles, CA

Post 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'
Post Reply