$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?
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:
$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.
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?
$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