multi_query doesnt work without using mysqli_next_result()?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

multi_query doesnt work without using mysqli_next_result()?

Post by raghavan20 »

let us say i have multiple queries to be executed.

Code: Select all

$query = "

select statement;
update statement;
insert statement;

";

$conn->multi_query( $query );
there is no error in the query as I can run it from standard mysql client and changes from mysql client but not from PHP.

but when I use this, all necessary changes happen

Code: Select all

$query = "

select statement;
update statement;
insert statement;

";

$conn->multi_query( $query );
				//loop results
				do {
					
					if ( $result = $conn->store_result() ) {
							
						while ( $row = $result->fetch_assoc() ) {
							
							echo "<br>"; print_r($row);
						}
						$result->close();
					}
					
					if ( $conn->more_results() ) {
						##printf("<br><br>-----------------<br>");
					}
				} while ( $conn->next_result() );
the queries are executed or committed only if the results are read ...why?
i have even used commit at the end of all the queries but it does not work until atleast one result is read using the command $conn->next_result()...very strange..
Last edited by raghavan20 on Fri Aug 18, 2006 7:56 am, edited 1 time in total.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

what is $conn?
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

it is a mysqli connection object
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Re: multi_query doesnt work without using mysqli_next_result

Post by feyd »

raghavan20 wrote:the queries are executed or committed only if the results are read ...why?
i have even used commit at the end of all the queries but it does not work until atleast one result is read using the command $conn->next_result()...very strange..
I would imagine MySQL doesn't really process the next statement (completely) until you request response data for it.

It makes sense to me.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

feyd, i had 10 queries in the order.

select
update
update
update
select
update
select
select
insert
set
insert

but only when i retrieve the first result, everthing else (the remaining 9 queries) is executed.
i do not know whether this behavior would be exhibited when i put all of them in one procedure.
i do understand why it works in mysql clients like putty, navicat, etc., because they break the 10 query bunch as 10 single queries and they immediately read and echo the result for each query on the screen.

why do mysql expect us to get the result? if i use select and i use the field values for subsequent queries i do not really want to parse them outside using mysqli_next_result().
ex:
select @var:=something from sometable where somefield=somevalue;
update someothertable set someotherfield=@someothervalue where someotherotherfield=@var

in the above case, i do not really want to retrieve results after the query is executed.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you don't have to retrieve anything.. just call mysqli_next_result(), or, if you don't want to do that, call the statements individually.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

I cannot reproduce that behaviour.
php 5.1.15, mysql 5.0.21-community-nt, winxp

Code: Select all

<?php
$mysqli = new mysqli("localhost", "localuser", "localpass", "test");

$query = "select * from yadda;
update yadda set fa=3;
update yadda set fa=4;
update yadda set fa=5;
select * from yadda;
update yadda set fa=5;
select * from yadda;
select * from yadda;
insert into yadda (fa,fb) values(1,'ab');
insert into yadda (fa,fb) values(2,'cd');";

$mysqli->multi_query($query);
?>
fa=5 and both inserts are executed.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

feyd wrote:you don't have to retrieve anything.. just call mysqli_next_result(), or, if you don't want to do that, call the statements individually.
i know how it works as you described but I do not understand why do I have to use that? If I do not use values from select why do I have to retrieve.

volka, thanks for trying a similar query but I was trying more like this
select @creditcardid:=creditcardId from onlineorder where id = $orderId;
update creditcard set transactioncount = transactioncount + 1 where id=@creditcardid;


so you can see that I use more mysql variables and use it in the queries following it...
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Why not use subqueries?
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

feyd wrote:Why not use subqueries?
subqueries can be used, but sometimes i have to use some value in more than one update query so I prefer storing it in mysql variable and using it in any number of queries later on.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

There are multi-table updates.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Although I agree with feyd I tried

Code: Select all

<?php
$mysqli = new mysqli("localhost", "localuser", "localpass", "test");

$orderId = 1;

$query = "select @creditcardid:=creditcardId from onlineorder where id = $orderId;
update creditcard set transactioncount = transactioncount + 1 where id=@creditcardid;
insert into onlineorder (creditcardId) values (2)";

$mysqli->multi_query($query) or die(mysqli_error());
?>
and it worked, transactioncount was incremented and the new record inserted.
Post Reply