Page 1 of 1
multi_query doesnt work without using mysqli_next_result()?
Posted: Fri Aug 18, 2006 7:07 am
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..
Posted: Fri Aug 18, 2006 7:41 am
by Jenk
what is $conn?
Posted: Fri Aug 18, 2006 7:47 am
by raghavan20
it is a mysqli connection object
Re: multi_query doesnt work without using mysqli_next_result
Posted: Fri Aug 18, 2006 9:11 am
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.
Posted: Fri Aug 18, 2006 9:24 am
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.
Posted: Fri Aug 18, 2006 9:32 am
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.
Posted: Fri Aug 18, 2006 9:35 am
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.
Posted: Fri Aug 18, 2006 9:51 am
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...
Posted: Fri Aug 18, 2006 9:53 am
by feyd
Why not use subqueries?
Posted: Fri Aug 18, 2006 10:51 am
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.
Posted: Fri Aug 18, 2006 10:54 am
by feyd
There are multi-table updates.
Posted: Fri Aug 18, 2006 11:02 am
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.