PHP: mysqli_multi_query - Manual

downloads | documentation | faq | getting help | mailing lists | reporting bugs | php.net sites | links | conferences | my php.net

search for in the

mysqli_next_result mysqli_more_results

Last updated: Sat, 27 Jan 2007

view this page in

# mysqli_multi_query

(PHP 5)

mysqli_multi_query

(no version information, might be only in CVS)

mysqli->multi_query -- Performs a query on the database

## Description

Procedural style:

bool mysqli_multi_query ( mysqli link, string query )

Object oriented style (method):

class mysqli {
bool multi_query ( string query )
}

The mysqli_multi_query() executes one or multiple queries which are concatenated by a semicolon.

To retrieve the resultset from the first query you can use mysqli_use_result() or mysqli_store_result(). All subsequent query results can be processed using mysqli_more_results() and mysqli_next_result().

## Возвращаемые значения

mysqli_multi_query() only returns `FALSE` if the first statement failed. To retrieve subsequent errors from other statements you have to call mysqli_next_result() first.

## Смотрите также

mysqli_use_result(), mysqli_store_result(), mysqli_next_result() и mysqli_more_results().

## Примеры

Пример 1. Object oriented style

`<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$query = "SELECT CURRENT_USER();";
$query .= "SELECT Name FROM City ORDER BY ID LIMIT 20, 5";
/* execute multi query */
if ($mysqli->multi_query($query)) {
do {
/* store first result set */
if ($result = $mysqli->store_result()) {
while ($row = $result->fetch_row()) {
printf("%s\n", $row[0]);
}
$result->close();
}
/* print divider */
if ($mysqli->more_results()) {
printf("-----------------\n");
}
} while ($mysqli->next_result());
}
/* close connection */
$mysqli->close();
?>`

Пример 2. Procedural style

`<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$query = "SELECT CURRENT_USER();";
$query .= "SELECT Name FROM City ORDER BY ID LIMIT 20, 5";
/* execute multi query */
if (mysqli_multi_query($link, $query)) {
do {
/* store first result set */
if ($result = mysqli_store_result($link)) {
while ($row = mysqli_fetch_row($result)) {
printf("%s\n", $row[0]);
}
mysqli_free_result($result);
}
/* print divider */
if (mysqli_more_results($link)) {
printf("-----------------\n");
}
} while (mysqli_next_result($link));
}
/* close connection */
mysqli_close($link);
?>`

Результат выполнения данного примера:

    my_user@localhost
    -----------------
    Amersfoort
    Maastricht
    Dordrecht
    Leiden
    Haarlemmermeer

add a note User Contributed Notes
mysqli_multi_query
mjmendoza at grupzero dot tk
29-Nov-2006 10:47
`I was developing my own CMS and I was having problem with attaching the database' sql file. I thought mysqli_multi_query got bugs where it crashes my MySQL server. I tried to report the bug but it showed that it has duplicate bug reports of other developers. To my surprise, mysqli_multi_query needs to bother with result even if there's none.
I finally got it working when I copied the sample and removed somethings. Here is what it looked liked
<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$query = "CREATE TABLE....;...;... blah blah blah;...";
/* execute multi query */
if (mysqli_multi_query($link, $query)) {
do {
/* store first result set */
if ($result = mysqli_store_result($link)) {
//do nothing since there's nothing to handle
mysqli_free_result($result);
}
/* print divider */
if (mysqli_more_results($link)) {
//I just kept this since it seems useful
//try removing and see for yourself
}
} while (mysqli_next_result($link));
}
/* close connection */
mysqli_close($link);
?>
bottom-line: I think mysql_multi_query should only be used for attaching a database. it's hard to handle results from 'SELECT' statements inside a single while loop.`
sly underscore mcfly at hotmail dot com
07-Sep-2006 05:32
`Ive just had exactly the same problem as below trying to execute multiple stored procedures. I thought i might as well add how to do it the object oriented way.
Instead of putting the one statement:
$mysqli->next_result();
Put two:
$mysqli->next_result();
$mysqli->next_result();
The first statement points (possibly using the term incorrectly) you to the return value. The second one will point you to the result of the next query.
I hope this makes sense.`
info at ff dot net
08-May-2006 04:02
`Note that you need to use this function to call Stored Procedures!
If you experience "lost connection to MySQL server" errors with your Stored Procedure calls then you did not fetch the 'OK' (or 'ERR') message, which is a second result-set from a Stored Procedure call. You have to fetch that result to have no problems with subsequent queries.
Bad example, will FAIL now and then on subsequent calls:
<?php
$sQuery='CALL exampleSP('param')';
if(!mysqli_multi_query($this->sqlLink,$sQuery))
$this->queryError();
$this->sqlResult=mysqli_store_result($this->sqlLink);
?>
Working example:
<?php
$sQuery='CALL exampleSP('param')';
if(!mysqli_multi_query($this->sqlLink,$sQuery))
$this->queryError();
$this->sqlResult=mysqli_store_result($this->sqlLink);
if(mysqli_more_results($this->sqlLink))
while(mysqli_next_result($this->sqlLink));
?>
Of course you can do more with the multiple results then just throwing them away, but for most this will suffice. You could for example make an "sp" function which will kill the 2nd 'ok' result.
This nasty 'OK'-message made me spend hours trying to figure out why MySQL server was logging warnings with 'bad packets from client' and PHP mysql_error() with 'Connection lost'. It's a shame the mysqli library does catch this by just doing it for you.`
Nick
30-Jan-2006 09:34
`I have to amend my previous post, I was misusing microtime(). multi_query is faster even for 2 queries on an internal network.
For the record, microtime(TRUE) is what I should have used instead of microtime().`
Nick
27-Jan-2006 08:11
`I assume this is here for performance, to avoid having to establish a communication link over the network repeatedly. My tests reveal some serious overhead though.
Communicating on an internal network at 100Mbps I had to combine over 300 queries to see a speed increase, and under that it was faster to do them individually.
I wouldn't recommend using this to combine 3 or 4 queries if all you're after is speed. It may be more worthwhile if your database calls have to go out over the internet.
Here's the benchmark I used:
<?php
$reps = 100;
$db = new mysqli("yourserver.com", "username", "password", "dbname");
$query = "SELECT column FROM table WHERE id=1";
$start = microtime();
for ($i = 0; $i < $reps; $i++) {
$result = $db->query($query);
$result->close;
}
$end = microtime();
echo ($end - $start) . "<br>\n";
$start = microtime();
for ($i = 0; $i < $reps; $i++) {
$myquery .= $query . "; ";
}
$db->multi_query($myquery);
do {
$result = $db->store_result();
$result->close;
} while ($db->next_result());
$end = microtime();
echo ($end - $start) . "<br>\n";
?>`
add a note
mysqli_next_result mysqli_more_results

Last updated: Sat, 27 Jan 2007
show source | credits | stats | sitemap | contact | advertising | mirror sites
Copyright © 2001-2007 The PHP Group
All rights reserved.
This mirror generously provided by: Yahoo! Inc.
Last updated: Sat Mar 10 10:36:09 2007 PST