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?

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