What are the basic operations of mysqli in PHP

The extension of MySQLi is more functional than PDO, so we will still intersperse various interesting methods and functions in MySQLi during the learning process. However, today's protagonist is how to execute SQL statements and the execution of multiple SQL statements in MySQLi.

Connecting and selecting the database

First of all, a small learning sharing, we still connect to the database, but this time we use Another way to connect.

$mysqli = new mysqli();
$mysqli->real_connect("localhost", "root", "", "blog_test");

var_dump($mysqli); 
// ["thread_id"]=>
// int(163)

$mysqli->real_connect("localhost", "root2", "123", "blog_test");
var_dump($mysqli);
// ["thread_id"]=>
// int(164)

First, we instantiate a mysqli object. During the instantiation process, we did not pass any parameters to the mysqli constructor, but used the real_connect() method to pass the database server information and establish the connection.

I believe many friends can see from the code that we can use real_connect() to switch different database connections in a mysqli instance. By printing the contents of the mysqli object, we can see that the thread IDs of the two connections are different, that is to say, they are two different connections, but they use the mysqli object initialized at the top.

The connection can be switched, so what about the database we want to connect to? Of course, it can also be switched easily.

$mysqli->select_db('mysql');

It is such a simple select_db() method that can help us dynamically modify the connected database during code execution.

Execute SQL statements

For PDO, if it is a query statement, we need to use the query() method. If it is add, delete, modify, etc. statement, we need to use exec() to execute different SQL statements through these two methods. But in MySQLi, we can only use the query() method.

$mysqli->query("insert into zyblog_test_user(username, password, salt) values('3a', '3a', '3a')");
var_dump($mysqli->affected_rows);
var_dump($mysqli->insert_id);

$mysqli->query("update zyblog_test_user set password='3aa' where username='3a'");
var_dump($mysqli->affected_rows);

$mysqli->query("delete from zyblog_test_user where id = 60");
var_dump($mysqli->affected_rows);

$res = $mysqli->query("select * from zyblog_test_user where username='3a'");
print_r($res);
// mysqli_result Object
// (
//     [current_field] => 0
//     [field_count] => 4
//     [lengths] =>
//     [num_rows] => 3
//     [type] => 0
// )

print_r($res->fetch_assoc());
// Array
// (
//     [id] => 61
//     [username] => 3a
//     [password] => 3aa
//     [salt] => 3a
// )

while ($row = $res->fetch_assoc()) {
    print_r($row);
}
// Array
// (
//     [id] => 62
//     [username] => 3a
//     [password] => 3aa
//     [salt] => 3a
// )
// Array
// (
//     [id] => 63
//     [username] => 3a
//     [password] => 3aa
//     [salt] => 3a
// )
// ……

For statements such as add, delete, and modify, the query() method will only return a Boolean value, that is, whether the statement was executed successfully. Remember, it does not return the number of rows affected, which is important to note. If we need to get the number of affected rows, we need to use the MySQLi attribute affected_rows. For insert statements, the insert_id attribute is used to obtain the latest inserted data ID.

If a SELECT statement is executed, query() returns a mysqli_result object, which represents the result set obtained from a database query. We will explain the content of this object in detail in a later article.

Execute multiple SQL statements

The ability to execute multiple SQL statements is not possible with PDO, but it is said that PDO supports it and the statements can work normally Executed, but we cannot get the complete return result.

$sql = "insert into zyblog_test_user(username, password, salt) values('3bb', '3bb', '3bb');"
        . "update zyblog_test_user set password='3aa' where username='3a';"
        . "select * from zyblog_test_user where username='3b';"
        . "select now()";

$pdo = new PDO("mysql:dns=locahost;dbname=blog_test", 'root', '', [PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION]);
$res = $pdo->exec($sql);
var_dump($res); // int(1)
$stmt = $pdo->query($sql);
foreach ($stmt as $row) { //PHP Fatal error:  Uncaught PDOException: SQLSTATE[HY000]: General error in
    var_dump($row);
}

As can be seen from the code, if the exec() method is used, the result of the INSERT statement is returned. If you use the query() method, although the PDOStatement object is returned, it cannot be traversed.

Next let’s take a look at how MySQLi executes this SQL statement that consists of multiple statements spliced ​​together.

$mysqli->multi_query($sql);
$i = 1;
do{
    echo '第' . $i . '条：', PHP_EOL;
    $i++;
    $result = $mysqli->use_result();
    var_dump($result);
    var_dump($mysqli->affected_rows);
    if(is_object($result)){
        var_dump($result->fetch_assoc());
    }
    var_dump($mysqli->next_result());
    echo '========', PHP_EOL;
} while($mysqli->more_results() );
// 第1条：
// bool(false)
// int(1)
// ========
// 第2条：
// bool(false)
// int(0)
// ========
// 第3条：
// object(mysqli_result)#2 (5) {
//   ["current_field"]=>
//   int(0)
//   ["field_count"]=>
//   int(4)
//   ["lengths"]=>
//   NULL
//   ["num_rows"]=>
//   int(0)
//   ["type"]=>
//   int(1)
// }
// int(-1)
// array(4) {
//   ["id"]=>
//   string(2) "67"
//   ["username"]=>
//   string(2) "3b"
//   ["password"]=>
//   string(2) "3b"
//   ["salt"]=>
//   string(2) "3b"
// }
// ========
// 第4条：
// bool(false)
// int(0)
// ========

multi_query() is the method provided by MySQLi to execute multiple SQL statements. After it is executed, the result returned is a Boolean value. If there is a problem with the first statement, then it returns FALSE. If the subsequent statement is wrong, we need to call next_result() to obtain the error information of the subsequent statement.

In fact, this also leads to the role of our next_result() method. It is equivalent to preparing for the operation of obtaining the next result, and can also be seen as moving the cursor to the next SQL statement. The more_results() method is to determine whether there are more statements that have not been executed.

use_result

In the above test code, the use_result() method is used to obtain the execution result of each statement. Its function is to initiate retrieval of the result set. In other words, when mutli_query() is called, these statements are not executed immediately, but the current statement is executed when use_result() is called. We noticed that the results returned by INSERT and UPDATE statements are FALSE. And num_rows in the SELECT statement is also 0. This is its characteristic. It does not directly store the result set information in the program's memory. Therefore, the biggest advantage of the use_result() method is that it takes up little memory and is suitable for traversing a large number of queries. The disadvantage is that it has to go to the database to query every time, which is slow.

store_result

In addition to use_result(), there is also a store_result() method that can help us obtain the result set of the query. It is the opposite of the use_result() method, that is, it executes directly and saves the result set in memory.

$mysqli = new mysqli("localhost", "root", "", "blog_test");

$mysqli->multi_query($sql);
$i = 1;
do{
    echo '第' . $i . '条：', PHP_EOL;
    $i++;
    $result = $mysqli->store_result();
    var_dump($result);
    var_dump($mysqli->affected_rows);
    if(is_object($result)){
        var_dump($result->fetch_assoc());
    }
    var_dump($mysqli->next_result());
    echo '========', PHP_EOL;
}
while($mysqli->more_results() );
// 第1条：
// bool(false)
// int(1)
// ========
// 第2条：
// bool(false)
// int(0)
// ========
// 第3条：
// object(mysqli_result)#1 (5) {
//   ["current_field"]=>
//   int(0)
//   ["field_count"]=>
//   int(4)
//   ["lengths"]=>
//   NULL
//   ["num_rows"]=>
//   int(7)
//   ["type"]=>
//   int(0)
// }
// int(7)
// array(4) {
//   ["id"]=>
//   string(2) "67"
//   ["username"]=>
//   string(2) "3b"
//   ["password"]=>
//   string(2) "3b"
//   ["salt"]=>
//   string(2) "3b"
// }
// ========
// 第4条：
// object(mysqli_result)#3 (5) {
//   ["current_field"]=>
//   int(0)
//   ["field_count"]=>
//   int(1)
//   ["lengths"]=>
//   NULL
//   ["num_rows"]=>
//   int(1)
//   ["type"]=>
//   int(0)
// }
// int(1)
// array(1) {
//   ["now()"]=>
//   string(19) "2020-09-14 10:31:37"
// }

Not only does num_rows in the query result have data, but the last SELECT now(); statement is also returned successfully. It is similar to the result of our daily use of query().

Another thing to note is that you can take a look at how we write the loop conditions for executing these two methods of obtaining results. more_results() and next_result() have different ways of obtaining these two result sets. You can test it yourself.

总结

光说不练假把式，虽说多语句执行看似很美好，但即使在这简单的测试代码中，也会出现各种问题，大家一定要自己多尝试一下。在日常的开发过程中，最好还是一条一条的语句来执行，避免出现各种无法查明的问题而影响我们正常的业务执行。至于到底要不要使用这个能力，还是大家仁者见仁智者见智了。

测试代码：

https://github.com/zhangyue0503/dev-blog/blob/master/php/202009/source/6.PHP中的MySQLi扩展学习（三）mysqli的基本操作.php

推荐学习：php视频教程

The above is the detailed content of What are the basic operations of mysqli in PHP. For more information, please follow other related articles on the PHP Chinese website!