How to use MySQLI_result object operation in MySQLi with PHP

We have already come into contact with MYSQLI_result related content before. It is actually the result set of a query. However, in PDO, results are generally returned after querying via query() or PDOStatement object. But in MySQLi, the query results are also put into an object, which is the MySQLI_result object.

MySQLI_result object properties

First, we need to obtain a MySQLI_result object through a query.

$stmt = $mysqli->prepare("select * from zyblog_test_user where username = 'kkk'");

$stmt->execute(); // 执行语句
$result = $stmt->get_result();
var_dump($result);
// object(mysqli_result)#3 (5) {
//     ["current_field"]=>
//     int(0)
//     ["field_count"]=>
//     int(4)
//     ["lengths"]=>
//     NULL
//     ["num_rows"]=>
//     int(7)
//     ["type"]=>
//     int(0)
//   }

If you use MYSQLI_STMT, you can obtain a MySQLI_result object through the get_result() method directly after the execute() method executes the query statement.

In this object, we can see the current_field current field, field_count field number, lengths field length, num_rows row number, type and other attributes. Many students will find that current_field and lengths do not seem to have any actual content. In fact, these two attributes need to display content under specific operations. For example, lengths will not have information until after the fetch() result set. .

$result->fetch_array();
var_dump($result);
// ……
// ……
// ["lengths"]=>
//   array(4) {
//     [0]=>
//     int(0)
//     [1]=>
//     int(3)
//     [2]=>
//     int(3)
//     [3]=>
//     int(2)
//   }
// ……
// ……

The content of the current_field attribute will be displayed when we traverse and view the field information below.

Query result set acquisition

Among the visible attributes of the MySQLI_result object, we can only see the above information. It is not very useful for our business development. In addition to num_rows, which can be used to determine whether the query has results based on the number of rows, more importantly, we need to obtain the data information in the result set. In this case, we need to use other functions. Let’s get the data.

Get all result sets

var_dump($result->fetch_all());
// array(7) {
//     [0]=>
//     array(4) {
//       [0]=>
//       int(42)
//       [1]=>
//       string(3) "kkk"
//       [2]=>
//       string(3) "666"
//       [3]=>
//       string(2) "k6"
//     }
//     ……
//     ……



$result->data_seek(0);
var_dump($result->fetch_all(MYSQLI_ASSOC));
// array(7) {
//     [0]=>
//     array(4) {
//       ["id"]=>
//       int(42)
//       ["username"]=>
//       string(3) "kkk"
//       ["password"]=>
//       string(3) "666"
//       ["salt"]=>
//       string(2) "k6"
//     }
//     ……
//     ……

fetch_all() method is used to get all the data in the data set and return it in the form of an array. It can specify the return format. By default It is in the form of array subscript MYSQLI_NUM. It is similar to PDO. We can directly specify it as MySQLI_ASSOC to return the data content in the form of key name.

The data_seek() method moves the subscript of the result set. When we obtain or use the method to be introduced later to loop through the result set once, if we traverse it again, its cursor is already at the last position, so we cannot obtain the data. In the above code, we return the cursor twice to the 0 subscript position, which is the initial position, so that we can repeatedly operate this result set.

Get ordinary result set

If you want to obtain row-by-row data, we can use various forms of result set data acquisition methods.

var_dump($result->fetch_array());
// array(8) {
//     [0]=>
//     int(42)
//     ["id"]=>
//     int(42)
//     [1]=>
//     string(3) "kkk"
//     ["username"]=>
//     string(3) "kkk"
//     [2]=>
//     string(3) "666"
//     ["password"]=>
//     string(3) "666"
//     [3]=>
//     string(2) "k6"
//     ["salt"]=>
//     string(2) "k6"
//   }

var_dump($result->fetch_array(MYSQLI_ASSOC));
// array(4) {
//     ["id"]=>
//     int(43)
//     ["username"]=>
//     string(3) "kkk"
//     ["password"]=>
//     string(3) "666"
//     ["salt"]=>
//     string(2) "k6"
//   }

Using fetch_array() is to get the result data of the next row and return it in the form of an array. It can also specify the format of the returned result set. It is similar to fetch_all(), except that it only fetches The next row instead of the entire data set, and its parameters default to the returned MYSQLI_BOTH, that is, the numerical subscript and key name subscript return the results at the same time.

There is also a fetch_assoc() method, which directly returns data in MYSQLI_ASSOC format. This method does not require any parameters. It can be regarded as an encapsulation of the usage of fetch_array(MYSQLI_ASSOC).

var_dump($result->fetch_assoc());
// array(4) {
//     ["id"]=>
//     int(42)
//     ["username"]=>
//     string(3) "kkk"
//     ["password"]=>
//     string(3) "666"
//     ["salt"]=>
//     string(2) "k6"
//   }

The other method, fetch_row(), can be regarded as a method similar to fetch_array(MYSQLI_NUM). It is actually the structure return method specified as MySQLI_NUM by default.

var_dump($result->fetch_row());
// array(4) {
//     [0]=>
//     int(43)
//     [1]=>
//     string(3) "kkk"
//     [2]=>
//     string(3) "666"
//     [3]=>
//     string(2) "k6"
//   }

Getting the object result set

Getting the object result set is actually similar to the related functions in PDO. It puts the result directly into a class and instantiates it to return an object.

ar_dump($result->fetch_object());
// object(stdClass)#4 (4) {
//     ["id"]=>
//     int(42)
//     ["username"]=>
//     string(3) "kkk"
//     ["password"]=>
//     string(3) "666"
//     ["salt"]=>
//     string(2) "k6"
//   }

Here we do not specify a class, so it uses stdClass to return the object structure. We can also specify a class and pass parameters to the constructor of this class, which is the same as the related functions in PDO.

class User
{
    public function __construct()
    {
        print_r(func_get_args());
    }
}
var_dump($result->fetch_object('User', [1, 2, 3]));
// Array
// (
//     [0] => 1
//     [1] => 2
//     [2] => 3
// )
// object(User)#4 (4) {
//     ["id"]=>
//     int(42)
//     ["username"]=>
//     string(3) "kkk"
//     ["password"]=>
//     string(3) "666"
//     ["salt"]=>
//     string(2) "k6"
//   }

Query result set field information acquisition

Next let’s look at obtaining field-related information in the MySQLI_result object. We can directly obtain all field information in the result set of the current query.

while ($finfo = $result->fetch_field()) {
    var_dump($result->current_field);
    var_dump($finfo);
}
// int(1)
// object(stdClass)#4 (13) {
//     ["name"]=>
//     string(2) "id"
//     ["orgname"]=>
//     string(2) "id"
//     ["table"]=>
//     string(16) "zyblog_test_user"
//     ["orgtable"]=>
//     string(16) "zyblog_test_user"
//     ["def"]=>
//     string(0) ""
//     ["db"]=>
//     string(9) "blog_test"
//     ["catalog"]=>
//     string(3) "def"
//     ["max_length"]=>
//     int(0)
//     ["length"]=>
//     int(11)
//     ["charsetnr"]=>
//     int(63)
//     ["flags"]=>
//     int(49667)
//     ["type"]=>
//     int(3)
//     ["decimals"]=>
//     int(0)
//   }
// int(2)
//   object(stdClass)#5 (13) {
//     ["name"]=>
//     string(8) "username"
//     ["orgname"]=>
//     string(8) "username"
//     ……
//     ……

In this code, we checked the current_field attribute information of the MySQLI_result object. It can be seen that it points out the subscript of which field it is currently located. The information in the

field is very detailed, and the key names of these attributes are also very intuitive, so I will not give a detailed explanation here.

$result->field_seek(1);
while ($finfo = $result->fetch_field()) {
    var_dump($finfo);
}
// object(stdClass)#5 (13) {
//     ["name"]=>
//     string(8) "username"
//     ["orgname"]=>
//     string(8) "username"

We can also move the field traversal cursor through the field_seek() method. Here we move the cursor to 1 and we will start traversing from the second username field.

var_dump($result->fetch_fields());
// array(4) {
//     [0]=>
//     object(stdClass)#5 (13) {
//       ["name"]=>
//       string(2) "id"
//       ["orgname"]=>
//       string(2) "id"
//       ["table"]=>
//       string(16) "zyblog_test_user"
//       ["orgtable"]=>
//       string(16) "zyblog_test_user"
//       ["def"]=>
//       string(0) ""
//       ["db"]=>
//       string(9) "blog_test"
//       ["catalog"]=>
//       string(3) "def"
//       ["max_length"]=>
//       int(0)
//       ["length"]=>
//       int(11)
//       ["charsetnr"]=>
//       int(63)
//       ["flags"]=>
//       int(49667)
//       ["type"]=>
//       int(3)
//       ["decimals"]=>
//       int(0)
//     }
//     [1]=>
//     object(stdClass)#4 (13) {
//       ["name"]=>
//       string(8) "username"

var_dump($result->fetch_field_direct(2));
// object(stdClass)#7 (13) {
//     ["name"]=>
//     string(8) "password"
//     ["orgname"]=>
//     string(8) "password"
//     ["table"]=>
//     string(16) "zyblog_test_user"
//     ["orgtable"]=>
//     string(16) "zyblog_test_user"
//     ["def"]=>
//     string(0) ""
//     ["db"]=>
//     string(9) "blog_test"
//     ["catalog"]=>
//     string(3) "def"
//     ["max_length"]=>
//     int(3)
//     ["length"]=>
//     int(765)
//     ["charsetnr"]=>
//     int(33)
//     ["flags"]=>
//     int(0)
//     ["type"]=>
//     int(253)
//     ["decimals"]=>
//     int(0)
//   }

The fetch_fields() method is similar to fetch_all(), it gets all the field information. And fetch_field_direct() obtains the field information of the specified subscript based on the parameters.

总结

至此，MySQLi 相关扩展的学习我们也就告一段落了，其它的一些类和函数比如 MySQLI_Driver 、 MySQLI_Exception 之类的内容大家可以自行查阅相关的文档，内容都不是很多。MySQLI_Driver 对象可以帮助我们指定当前驱动的报错形式，之前的文章中我们也已经接触过。

总体来说，整个 PHP 中和 MySQL 打交道的官方扩展我们就已经全部学习完了，PDO 和 MYSQLi 这两个扩展大家更主要的还是要掌握它们的区别和联系。在实际的业务开发中 PDO 还是会使用得更多，但 MySQLi 也绝不是能够完全忽略的，多多动手尝试学习吧。

测试代码：

https://github.com/zhangyue0503/dev-blog/blob/master/php/202009/source/9.PHP中的MySQLi扩展学习（六）MySQLI_result对象操作.php

推荐学习：php视频教程

The above is the detailed content of How to use MySQLI_result object operation in MySQLi with PHP. For more information, please follow other related articles on the PHP Chinese website!