PHP Doku:: Performs a query on the database - mysqli.multi-query.html

Verlauf / Chronik / History: (1) anzeigen

Sie sind hier:
Doku-StartseitePHP-HandbuchFunktionsreferenzDatenbankerweiterungenAnbieterspezifische DatenbankerweiterungenMySQL Improved ExtensionThe MySQLi classmysqli::multi_query -- mysqli_multi_query

Ein Service von Reinhard Neidl - Webprogrammierung.

The MySQLi class

<<mysqli::more_results -- mysqli_more_results

mysqli::next_result -- mysqli_next_result>>

mysqli::multi_query

mysqli_multi_query

(PHP 5)

mysqli::multi_query -- mysqli_multi_queryPerforms a query on the database

Beschreibung

Objektorientierter Stil

bool mysqli::multi_query ( string $query )

Prozeduraler Stil

bool mysqli_multi_query ( mysqli $link , string $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().

Parameter-Liste

link

Nur bei prozeduralem Aufruf: Ein von mysqli_connect() oder mysqli_init() zurückgegebenes Verbindungsobjekt.

query

The query, as a string.

Data inside the query should be properly escaped.

Rückgabewerte

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

Beispiele

Beispiel #1 mysqli::multi_query() example

Objektorientierter Stil

<?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->free();
        }
        
/* print divider */
        
if ($mysqli->more_results()) {
            
printf("-----------------\n");
        }
    } while (
$mysqli->next_result());
}

/* close connection */
$mysqli->close();
?>

Prozeduraler Stil

<?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);
?>

The above examples will output something similar to:

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

Siehe auch


9 BenutzerBeiträge:
- Beiträge aktualisieren...
luka8088 at owave dot net
20.09.2010 19:17
if you don't iterate through all results you get "server has gone away" error message ...

to resolve this, in php 5.2 it is enough to use

<?php
 
// ok for php 5.2
 
while ($mysqli->next_result());
?>

to drop unwanted results, but in php 5.3 using only this throws

mysqli::next_result(): There is no next result set. Please, call mysqli_more_results()/mysqli::more_results() to check whether to call this function/method

so it should be replaced with

<?php
 
// ok for php 5.3
 
while ($mysqli->more_results() && $mysqli->next_result());
?>

I also tried but failed:

<?php

 
// can create infinite look in some cases
 
while ($mysqli->more_results())
   
$mysqli->next_result();

 
// also throws error in some cases
 
if ($mysqli->more_results())
    while (
$mysqli->next_result());

?>
Shawn Pyle
15.04.2010 18:29
Be sure to not send a set of queries that are larger than max_allowed_packet size on your MySQL server. If you do, you'll get an error like:
Mysql Error (1153): Got a packet bigger than 'max_allowed_packet' bytes

To see your MySQL size limitation, run the following query: show variables like 'max_allowed_packet';

or see http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html
Miles
22.06.2009 19:47
You can use prepared statements on stored procedures.

You just need to flush all the subsequent result sets before closing the statement... so:

$mysqli_stmt = $mysqli->prepare(....);

... bind, execute, bind, fetch ...

while($mysqli->more_results())
{
    $mysqli->next_result();
    $discard = $mysqli->store_result();
}

$mysqli_stmt->close();

Hope that helps :o)
raye1010 at yahoo dot com dot hk
29.01.2009 7:56
This is my point of view:

Actually when calling $mysqli->next_result(), MySQL server will try to prepare resources for storing resultset. If you want to multi query for stored procedures, it's better to use $mysqli->use_result()->close() to close the resources. For functions, it's better to use $mysqli->store_result()->free() since there's resultset returned. Otherwise, an error of "Commands out of sync" will be rasied.

Here's the code I test:

<?php
$mysql
= new mysql('localhost', 'user', 'pw', 'db');

$query = 'show tables;';
$query.= 'show tables;';
$query.= 'select * from dummy_table;';
$query.= 'show tables;'; // this statement will be ignored as the dummy_table doesn't exist and the loop should quit

$mysqli->multi_query($query);
do {
 
$mysqli->use_result()->close();
  echo
"Okay\n";
} while (
$mysqli->next_result());

if (
$mysqli->errno) {
  echo
"Stopped while retrieving result : ".$mysqli->error;
}

?>
Returns :
Okay
Okay
Stopped while retrieving result : Table 'db.dummy_table' doesn't exist
jparedes at gmail dot com
27.08.2008 18:05
It's very important that after executing mysqli_multi_query you have first process the resultsets before sending any another statement to the server, otherwise your
socket is still blocked.

Please note that even if your multi statement doesn't contain SELECT queries, the server will send result packages containing errorcodes (or OK packet) for single statements.
undefined(AT)users(DOT)berlios(DOT)de
4.03.2008 13:13
mysqli_multi_query handles MySQL Transaction on InnoDB's :-)

<?php

$mysqli 
= mysqli_connect( "localhost", "owner", "pass", "db", 3306, "/var/lib/mysql/mysql.sock" );

$QUERY = <<<EOT
START TRANSACTION;
SELECT @lng:=IF( STRCMP(`main_lang`,'de'), 'en', 'de' )
FROM `main_data` WHERE  ( `main_activ` LIKE 1 ) ORDER BY `main_id` ASC;
SELECT `main_id`, `main_type`, `main_title`, `main_body`, `main_modified`, `main_posted`
FROM `main_data`
WHERE ( `main_type` RLIKE "news|about" AND `main_lang` LIKE @lng AND `main_activ` LIKE 1 )
ORDER BY `main_type` ASC;
COMMIT;
EOT;

$query = mysqli_multi_query( $mysqli, $QUERY ) or die( mysqli_error( $mysqli ) );

if(
$query )
{
  do {
    if(
$result = mysqli_store_result( $mysqli ) )
    {
     
$subresult = mysqli_fetch_assoc( $result );
      if( ! isset(
$subresult['main_id'] ) )
        continue;

      foreach(
$subresult AS $k => $v )
      {
       
var_dump( $k , $v );
      }
    }
  } while (
mysqli_next_result( $mysqli ) );
}

mysqli_close( $mysqli );

?>
mjmendoza at grupzero dot tk
30.11.2006 7: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
8.09.2006 2: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:

<?php
$mysqli
->next_result();
?>

Put two:

<?php
$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
8.05.2006 13: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.



PHP Powered Diese Seite bei php.net
The PHP manual text and comments are covered by the Creative Commons Attribution 3.0 License © the PHP Documentation Group - Impressum - mail("TO:Reinhard Neidl",...)