mySQL Query won't work from PHP but works in PHPMyAdmin

Question

When I run an SQL query from PHP MyAdmin, it inserts the line correctly; when I run it from PHP, it does not.

I am going to provide you with the text in each; they are identical but given I might have missed something, I don't want to assume there are no differences;

IN PHP:

$orderSend = "SET @typeSQL := (SELECT `typeID` FROM `ArbuckleType` WHERE `typeName` = 'A la Carte');

        SET @groupSQL:= (SELECT `groupID` FROM `ArbuckleGroup` WHERE (`groupName` = 'Nigiri' AND `typeID` = @typeSQL));

        SET @itemSQL:=(SELECT `itemID` FROM `ArbuckleItem` WHERE (`itemName` = 'Salmon' AND `groupID` = @groupSQL));

        INSERT INTO `ArbuckleOrderDetails` VALUES('', '$orderID', @typeSQL,@groupSQL, @itemSQL, '$quantity', '$spicy')";
mysql_query($orderSend);

Now in PHP MyAdmin:

SET @type := (SELECT `typeID` FROM `ArbuckleType` WHERE `typeName` = 'A la Carte');# MySQL returned an empty result set (i.e. zero rows).  

        SET @group:= (SELECT `groupID` FROM `ArbuckleGroup` WHERE (`groupName` = 'Nigiri' AND `typeID` = @type));# MySQL returned an empty result set (i.e. zero rows).

        SET @item:=(SELECT `itemID` FROM `ArbuckleItem` WHERE (`itemName` = 'Salmon' AND `groupID` = @group));# MySQL returned an empty result set (i.e. zero rows).

INSERT INTO `ArbuckleOrderDetails` VALUES('', '$orderID', @type,@group, @item, '$quantity', '$spicy')

NOTE: I ran the PHP query WITHOUT the SET calls (the 3 lines setting the variables) and it worked; but I can't figure out where there is wrong syntax in those lines. Also ran mysql_error and it states that there is a syntax error - then asks me to look at the manual. Very helpful, mysql.

I wouldn't be using the mysql_* functions. They are deprecated and for good reasons. Read more at: php.net/manual/en/mysqlinfo.api.choosing.php — Ben Rowe
– Ben Rowe, Commented Sep 7, 2012 at 6:56

Māris Kiseļovs · Accepted Answer · 2012-09-07 07:03:54Z

7

mysql_query() cannot execute multiple queries at once.

Two solutions:

Use multiple mysql_query lines
use mysqli with multi_query()

Your code, rewriten to multiple mysql_query lines:

mysql_query("SET @typeSQL := (SELECT `typeID` FROM `ArbuckleType` WHERE `typeName` = 'A la Carte')");
mysql_query("SET @groupSQL:= (SELECT `groupID` FROM `ArbuckleGroup` WHERE (`groupName` = 'Nigiri' AND `typeID` = @typeSQL))");
mysql_query("SET @itemSQL:=(SELECT `itemID` FROM `ArbuckleItem` WHERE (`itemName` = 'Salmon' AND `groupID` = @groupSQL))");
mysql_query("INSERT INTO `ArbuckleOrderDetails` VALUES('', '$orderID', @typeSQL,@groupSQL, @itemSQL, '$quantity', '$spicy')");

edited Sep 7, 2012 at 7:03

answered Sep 7, 2012 at 6:52

Māris Kiseļovs

17.3k5 gold badges43 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Laurent Over a year ago

Hi Maris, I was initially going to do multiple queries but then thought: doesn't doing multiple queries slow down the process? I will have a user waiting for a confirmation the order has gone through (as you can see the script involves a menu) and so I thought running everything in a single Mysql query would be quicker; am I misunderstanding how MySQL works?

Māris Kiseļovs Over a year ago

Of course, single group of queries will be a bit faster. You still tried to run many queries but in one request. I would rewrite your queries in to one single query. Check dev.mysql.com/doc/refman/5.0/en/insert-select.html

Cyclonecode · Accepted Answer · 2012-09-07 06:57:54Z

0

You have following options:

Use multiple queries to retrieve values and and make final query.
Use mysqli_query
Make a stored procedure and call it. (Recommended, then you need not worry about escape characters)

edited Sep 7, 2012 at 6:57

Cyclonecode

30.4k11 gold badges77 silver badges97 bronze badges

answered Sep 7, 2012 at 6:55

harshit

3,8664 gold badges33 silver badges54 bronze badges

Collectives™ on Stack Overflow

mySQL Query won't work from PHP but works in PHPMyAdmin

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related