insert and select, not select into

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Illusionist
Forum Regular
Posts: 903
Joined: Mon Jan 12, 2004 9:32 pm

insert and select, not select into

Post by Illusionist »

here is my current sql:

Code: Select all

"INSERT INTO user_items VALUES('{$_SESSIONї'id']}', '(SELECT itemID FROM item WHERE itemName=$name)')"
Its inside a function that takes $name as a parameter. But for some reason, the SELECT keeps returning 0.
I'm pretty sure that means what im doing is wrong, so waht is the correct way?
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

What's the full code look like?
Are you doing stuff like the following to debug?

Code: Select all

$sql = "INSERT INTO user_items VALUES('{$_SESSION['id']}', '(SELECT itemID FROM item WHERE itemName=$name)')";
echo $sql;
mysql_query($sql) or die(mysql_error());
Illusionist
Forum Regular
Posts: 903
Joined: Mon Jan 12, 2004 9:32 pm

Post by Illusionist »

already tried it keep getting:
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 'SELECT itemID FROM item WHERE itemName='Pillow'))
Illusionist
Forum Regular
Posts: 903
Joined: Mon Jan 12, 2004 9:32 pm

Post by Illusionist »

Code: Select all

INSERT INTO user_items (userID,itemID) VALUES('24', (SELECT itemID FROM item WHERE itemName='Pillow'))

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 'SELECT itemID FROM item WHERE itemName='Pillow'))'
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

What version of mysql?
Illusionist
Forum Regular
Posts: 903
Joined: Mon Jan 12, 2004 9:32 pm

Post by Illusionist »

4.0.20
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

You need 4.1 or above for subselects.
Illusionist
Forum Regular
Posts: 903
Joined: Mon Jan 12, 2004 9:32 pm

Post by Illusionist »

dangit. Ok, so ill have to do 2 seperate queries? that sucks! lol
Post Reply