mysqli::multi_query
mysqli_multi_query
Performs one or more queries on the database
Description
Object-oriented style
public bool mysqli::multi_query(string $query)
bool mysqli_multi_query(mysqli $mysql, string $query)
Warning
Security warning: SQL injection
If the query contains any variable
input then parameterized
prepared statements should be used instead. Alternatively, the
data must be properly formatted and all strings must be escaped using
the mysqli_real_escape_string
function.
Queries are sent asynchronously in a single call to the database, but the
database processes them sequentially.
mysqli_multi_query waits for the first query to
complete before returning control to PHP. The MySQL server will then process
the next query in the sequence. Once the next result is ready, MySQL will wait
for the next execution of mysqli_next_result from PHP.
It is recommended to use
do-while to process multiple
queries. The connection will be busy until all queries have completed and
their results are fetched to PHP. No other statement can be issued on the
same connection until all queries are processed.
To proceed to the next query in the sequence, use
mysqli_next_result. If the next result is not ready yet,
mysqli will wait for the response from the MySQL server.
To check if there are more results,
use mysqli_more_results.
For queries which produce a result set, such as
SELECT, SHOW, DESCRIBE or
EXPLAIN,
mysqli_use_result or mysqli_store_result
can be used to retrieve the result set. For queries which do not produce a
result set, the same functions can be used to retrieve information such as
the number of affected rows.
Tip
Executing CALL statements for stored procedures can
produce multiple result sets. If the stored procedure contains
SELECT statements, the result sets are returned in the
order that they are produced as the procedure executes. In general, the
caller cannot know how many result sets a procedure will return and must be
prepared to retrieve multiple results. The final result from the procedure
is a status result that includes no result set. The status indicates whether
the procedure succeeded or an error occurred.
Parameters
-
mysql
-
Procedural style only: A mysqli object
returned by mysqli_connect or mysqli_init
-
query
-
A string containing the queries to be executed.
Multiple queries must be separated by a semicolon.
Return Values
Returns false if the first statement failed.
To retrieve subsequent errors from other statements you have to call
mysqli_next_result first.
Errors/Exceptions
If mysqli error reporting is enabled (MYSQLI_REPORT_ERROR) and the requested operation fails,
a warning is generated. If, in addition, the mode is set to MYSQLI_REPORT_STRICT,
a mysqli_sql_exception is thrown instead.
Examples
Example #1 mysqli::multi_query example
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
$query = "SELECT CURRENT_USER();";
$query .= "SELECT Name FROM City ORDER BY ID LIMIT 20, 5";
/* execute multi query */
$mysqli->multi_query($query);
do {
/* store the result set in PHP */
if ($result = $mysqli->store_result()) {
while ($row = $result->fetch_row()) {
printf("%s\n", $row[0]);
}
}
/* print divider */
if ($mysqli->more_results()) {
printf("-----------------\n");
}
} while ($mysqli->next_result());
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$link = mysqli_connect("localhost", "my_user", "my_password", "world");
$query = "SELECT CURRENT_USER();";
$query .= "SELECT Name FROM City ORDER BY ID LIMIT 20, 5";
/* execute multi query */
mysqli_multi_query($link, $query);
do {
/* store the result set in PHP */
if ($result = mysqli_store_result($link)) {
while ($row = mysqli_fetch_row($result)) {
printf("%s\n", $row[0]);
}
}
/* print divider */
if (mysqli_more_results($link)) {
printf("-----------------\n");
}
} while (mysqli_next_result($link));
The above examples will output
something similar to:
my_user@localhost
-----------------
Amersfoort
Maastricht
Dordrecht
Leiden
Haarlemmermeer
See Also
- mysqli_query
- mysqli_use_result
- mysqli_store_result
- mysqli_next_result
- mysqli_more_results