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!
$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.
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.
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.
<?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);
?>
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...
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.