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:
mysql_query
pdo
mysqli
All three are fine when the sqls statement has no problems.
but
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";
break;
}
}
$mysqli->close();
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.