Solution for PHP to execute batch mysql statements

  • 2020-06-01 09:16:01
  • OfStack

When there are multiple mysql statements that need to be executed, for example

$sqls= "insert table a values(1,2); insert table a values (2, 3);"

There are three methods available in php that need to be executed:




All three are fine when the sqls statement has no problems.


Problems occur when the sql statement is incorrect
Article 1 sql error: all three methods return false

Article 1 sql is correct, and article 2 sql is incorrect: mysql_query, pdo, mysqli:query also returns true. So at this point you can't tell if your sqls has that statement wrong.

There are several ways around this:

1 parse the sql statement
Each sql is split up and executed. This solves the problem by executing each statement separately. But this method has several more methods, so it is not advisable.

2 save the sqls statement as text
Use cmd to execute the command mysql... . < sqls.sql, and then capture the output. That's one way to do it, but it feels like there's a better way around the problem.

3 use the mysqli::multi_query method
This method can execute multiple sql statements, then use mysqli::next_result to set the offset of sql, and mysqli::error to get the error status of the current offset sql

Here is sample code for the third method

The code is as follows:

$sql = Config::get('sql'); 
$content = file_get_contents($sql); 
$config = Config::get('config') 
$mysqli = mysqli_connect($config['host'], $config['user'], $config['password'], $config['dbname']); 
$ret = $mysqli->multi_query($content); 
if($ret === false) { 
    echo mysqli_error($mysqli); 
while (mysqli_more_results($mysqli)) { 
    if (mysqli_next_result($mysqli) === false) { 
        echo mysqli_error($mysqli); 
        echo "\r\n"; 

In this case, if any one of the sqls statements has an error, the program will jump out of the error.

This is especially useful if you are writing scripts to initialize mysql.

Related articles: