Page 1 of 1

Multiple Queries with mysql_query()

Posted: Sat May 13, 2006 3:42 am
by Benjamin
Why does this not work:

Code: Select all

$TESTQUERY = "SELECT * FROM testtable WHERE testdata=5; update testtable set testdata=6 where testdata=5";
echo $TESTQUERY;
$result = mysql_query($TESTQUERY) or die (mysql_error());
But when I echo the query it works fine in phpMyAdmin? How can I fix this?

Posted: Sat May 13, 2006 4:21 am
by onion2k
mysql_query can only run one query at a time. phpMyAdmin is parsing the SQL and breaking it into seperate queries before running them on the database.

However, a database connection can retain information between queries, so there's no problem simply doing multiple queries in a row if you need to retain data.. eg:

Code: Select all

$databaseLink = mysql_connect("localhost","root","password");
if (!mysql_select_db("songs", $databaseLink)) { exit; }

mysql_query("select @title := 'Track 1 Title';",$databaseLink);

$sql = "insert into `tracks` (title) values (@title);";
mysql_query($sql,$databaseLink);

mysql_query("select @trackid := LAST_INSERT_ID();",$databaseLink);

$sql = "insert into `tracks_user_xref` (trackid,userid) values (@trackid,1);";
mysql_query($sql,$databaseLink);

Re: Multiple Queries with mysql_query()

Posted: Sat May 13, 2006 5:34 am
by raghavan20
agtlewis wrote:Why does this not work:

Code: Select all

$TESTQUERY = "SELECT * FROM testtable WHERE testdata=5; update testtable set testdata=6 where testdata=5";
echo $TESTQUERY;
$result = mysql_query($TESTQUERY) or die (mysql_error());
But when I echo the query it works fine in phpMyAdmin? How can I fix this?
You should use multi_query to execute more than one query in a single go.
You have to then use mysqli_more_results(), mysqli_next_result(), mysqli_store_result() to retrieve multiple results returned after multiple SQL statements.

Posted: Sat May 13, 2006 9:13 am
by timvw
Hack the php mysql_ driver and change the code where autocommit is enabled ;)

Posted: Sat May 13, 2006 9:49 am
by raghavan20
timvw wrote:Hack the php mysql_ driver and change the code where autocommit is enabled ;)
I would like to know how autocommit is related with this. Commiting enables to make permanent changes to DB, how is it related to running multiple queries? :roll:

Posted: Sat May 13, 2006 10:22 am
by timvw
You're right that it has nothing to do with autocommit...

I should have said: add the CLIENT_MULTI_STATEMENTS flag for all the calls to mysql(_real)_connect in the mysql extension.

Posted: Sat May 13, 2006 2:12 pm
by Benjamin
Any way to craft the query so that it it's only 1 query and works?

Posted: Sat May 13, 2006 2:23 pm
by raghavan20

Code: Select all

$TESTQUERY = "SELECT * FROM testtable WHERE testdata=5; update testtable set testdata=6 where testdata=5"; 
echo $TESTQUERY; 

mysqli_multi_query($TESTQUERY);
I do not know whether mysql dll supports multiple queries but mysqli supports it. If you have not enabled mysqli, take off the ; after mysqli dll in the extensions part of the php.ini