Multiple Queries with mysql_query()

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
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Multiple Queries with mysql_query()

Post 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?
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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);
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Re: Multiple Queries with mysql_query()

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Hack the php mysql_ driver and change the code where autocommit is enabled ;)
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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:
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Any way to craft the query so that it it's only 1 query and works?
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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
Post Reply