Take you to understand the query structure set in PHP in three minutes

This article will introduce you to the query structure set in PHPPHP. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to everyone.

PDO operation learning in PHP (4) Query structure set

For the last article about PDO, we will The operation of querying the result set is completed. In database operations, queries often account for a very high proportion. In daily development, most businesses are read-more-write-less businesses, so mastering query-related operations is an important part of our learning. Like mysqli, PDO's support for queries is also very convenient and fast. You can operate various query statements very conveniently and efficiently through a few functions.

When using prepared statements, after we use execute() to execute, the result set of the query will be saved in the PDOStatement object. The operations on data are transferred to PHP objects, so we need some methods of PDOStatement to obtain the contents of the result set.

fetch() method

Through the fetch() method, the next row of the query result set is obtained.

$stmt = $pdo->prepare("select * from zyblog_test_user");
$stmt->execute();

$row = $stmt->fetch();
print_r($row);
// Array
// (
//     [id] => 1
//     [0] => 1
//     [username] => aaa
//     [1] => aaa
//     [password] => aaa
//     [2] => aaa
//     [salt] => aaa
//     [3] => aaa
// )

Judging from the returned results, we did not specify the PDO::ATTR_DEFAULT_FETCH_MODE attribute for the PDO object, so it is the default PDO::FETCH_BOTH format returned, that is, the field name and subscript exist at the same time. In fact, this method can directly specify the FETCH_STYLE we need.

Result set type specification

$row = $stmt->fetch(PDO::FETCH_ASSOC);
print_r($row);
// Array
// (
//     [id] => 2
//     [username] => bbb
//     [password] => bbb
//     [salt] => 123
// )

$row = $stmt->fetch(PDO::FETCH_LAZY);
print_r($row);
// PDORow Object
// (
//     [queryString] => select * from zyblog_test_user
//     [id] => 3
//     [username] => ccc
//     [password] => bbb
//     [salt] => c3
// )

$row = $stmt->fetch(PDO::FETCH_OBJ);
print_r($row);
// stdClass Object
// (
//     [id] => 4
//     [username] => ccc
//     [password] => bbb
//     [salt] => c3
// )

is the same as specifying PDO::ATTR_DEFAULT_FETCH_MODE of the PDO object. When using the fetch() method, directly specify the required return result type parameter to the first parameter of the method, thereby realizing the specification of FETCH_STYLE. The specific supported formats are exactly the same as the PDO::ATTR_DEFAULT_FETCH_MODE attribute of the PDO object mentioned before. You can check it yourself.

Get all data

As can be seen from the code and definition, the fetch() method is to obtain the next row of data in the current data set, just like the cursor operation of the database. Therefore, we can traverse the result set by looping fetch() to obtain all the result set data.

 while($row = $stmt->fetch()){
    print_r($row);
}
// Array
// (
//     [id] => 2
//     [0] => 2
//     [username] => bbb
//     [1] => bbb
//     [password] => bbb
//     [2] => bbb
//     [salt] => 123
//     [3] => 123
// )
// ……

MySQL does not support cursors

As mentioned above, the cursor operation is supported by the PDO extension, but it should be noted that the MySQL extension does not support this operation. Therefore, the cursor-related properties we use have no effect on the MySQL library.

$stmt = $pdo->prepare("select * from zyblog_test_user", [PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL]);
$stmt->execute();

$row = $stmt->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT);
print_r($row);
// Array
// (
//     [id] => 1
//     [username] => aaa
//     [password] => aaa
//     [salt] => aaa
// )

$stmt = $pdo->prepare("select * from zyblog_test_user", [PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL]);
$stmt->execute();

$row = $stmt->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_LAST);
print_r($row);
// Array
// (
//     [id] => 1
//     [username] => aaa
//     [password] => aaa
//     [salt] => aaa
// )

If it is a database and extension that supports cursor operations, after the second parameter of fetch() in the above code is specified, the obtained results will be different. PDO::FETCH_ORI_NEXT is to get the next data of the cursor, and PDO::FETCH_ORI_LAST is to get the last data of the cursor. But in our test on MySQL, they had no effect and still got the next piece of data in the result set.

fetchAll() method

Through the fetch() method, we can get all the data in the result set, but it still requires a loop to traverse, which is still a bit troublesome. . In fact, PDO has already prepared another method for us, fetchAll(), which returns an array containing all rows in the result set.

$stmt = $pdo->prepare("select * from zyblog_test_user limit 2");
$stmt->execute();

$list = $stmt->fetchAll();
print_r($list);
// Array
// (
//     [0] => Array
//         (
//             [id] => 1
//             [0] => 1
//             [username] => aaa
//             [1] => aaa
//             [password] => aaa
//             [2] => aaa
//             [salt] => aaa
//             [3] => aaa
//         )

//     [1] => Array
//         (
//             [id] => 2
//             [0] => 2
//             [username] => bbb
//             [1] => bbb
//             [password] => bbb
//             [2] => bbb
//             [salt] => 123
//             [3] => 123
//         )

// )

fetchAll() uses fetch() internally to help us traverse the result set and assign it to an array. So if we call fetchAll() again without re-execute(), we will get empty data. Because the cursor has reached the bottom.

$list = $stmt->fetchAll();
print_r($list);
// Array
// (
// )

It also supports specifying FETCH_STYLE. Just like the fetch() method, you can directly assign the required type constant to the first parameter.

// PDO::FETCH_ASSOC
$stmt = $pdo->prepare("select * from zyblog_test_user limit 2");
$stmt->execute();

$list = $stmt->fetchAll(PDO::FETCH_ASSOC);
print_r($list);
// Array
// (
//     [0] => Array
//         (
//             [id] => 1
//             [username] => aaa
//             [password] => aaa
//             [salt] => aaa
//         )

//     [1] => Array
//         (
//             [id] => 2
//             [username] => bbb
//             [password] => bbb
//             [salt] => 123
//         )

// )

// PDO::FETCH_COLUMN
$stmt = $pdo->prepare("select * from zyblog_test_user limit 2");
$stmt->execute();

$list = $stmt->fetchAll(PDO::FETCH_COLUMN, 1);
print_r($list);
// Array
// (
//     [0] => aaa
//     [1] => bbb
// )

// PDO::FETCH_CLASS
class User{
    function __construct($a){
        echo $a, PHP_EOL;
    }
}
$stmt = $pdo->prepare("select * from zyblog_test_user limit 2");
$stmt->execute();
$list = $stmt->fetchAll(PDO::FETCH_CLASS, 'User', ['FetchAll User']);
print_r($list);
// FetchAll User
// FetchAll User
// Array
// (
//     [0] => User Object
//         (
//             [id] => 1
//             [username] => aaa
//             [password] => aaa
//             [salt] => aaa
//         )

//     [1] => User Object
//         (
//             [id] => 2
//             [username] => bbb
//             [password] => bbb
//             [salt] => 123
//         )

// )

Are you very familiar with it? I won’t go into details here. I have talked about the type specification of FETCH_STYLE many times. Its usage is the same as fetch() and the query() method in the PDO object. almost the same. However, it also supports a form of calling a method in a callback to obtain the data set.

function getValue(){
    print_r(func_get_args());
}
// Array
// (
//     [0] => 1
//     [1] => aaa
//     [2] => aaa
//     [3] => aaa
// )
// Array
// (
//     [0] => 2
//     [1] => bbb
//     [2] => bbb
//     [3] => 123
// )
$stmt = $pdo->prepare("select * from zyblog_test_user limit 2");
$stmt->execute();
$list = $stmt->fetchAll(PDO::FETCH_FUNC, 'getValue');
print_r($list);
// Array
// (
//     [0] => 
//     [1] => 
// )

In this code, we are using PDO::FETCH_FUNC, and the second parameter is a method name. In this way, each structure set will be used as a method parameter to call the specified method during traversal. We can obtain the content of these parameters through func_get_args(). In this code, the result set will not be assigned to the $list variable through the return value of the fetchAll() method. Because the data has been passed to the specified getValue() method.

fetchColumn() method

In the above test code, we have used PDO::FETCH_COLUMN to obtain a certain column of data in the result set. There is nothing wrong with writing this way, but there is a more convenient way, which is the fetchColumn() method that PDOStatment directly provides us. It is equivalent to specifying PDO::FETCH_COLUMN by default inside the method, and only requires one parameter, which is the column subscript.

It should be noted that its return is the specified column value of the next row, that is, it calls the fetch() method at the bottom. If we want to get the contents of all specified columns in the result set, we also need to traverse the result set in the same way as fetch().

// fetchColumn
$stmt = $pdo->prepare("select * from zyblog_test_user");
$stmt->execute();
$column = $stmt->fetchColumn(2);
echo $column, PHP_EOL;
// aaa

$column = $stmt->fetchColumn(3);
echo $column, PHP_EOL;
// 123

fetchObject() 方法

fetchObject() 就不用多解释了，它和 fetchColumn() 是类似的，只是返回的是下一行数据的对象格式。同样的，它也是可以传递构造参数的，这点和 PDO 对象的 query() 中指定的 PDO::FETCH_CLASS 格式的使用是一样的。我们在第一篇文章中就有讲解。

// fetchObject
$stmt = $pdo->prepare("select * from zyblog_test_user");
$stmt->execute();
$user = $stmt->fetchObject('User', ['FetchObject User']);
print_r($user);
// FetchObject User
// User Object
// (
//     [id] => 1
//     [username] => aaa
//     [password] => aaa
//     [salt] => aaa
// )

rowCount() 返回查询结果数量

要获得查询的结果集行数就需要我们的 rowCount() 方法了。数据库中不管是查询还是增、删、改操作，都会返回语句执行结果，也就是受影响的行数。这些信息都是通过 rowCount() 这个方法获得的。

查询语句返回行数

需要注意的是，在查询语句中，有些数据是可能返回此语句的行数的。但这种方式不能保证对所有数据有效，且对可移植的应用更不要依赖这种方式。我们如果需要知道当前查询结果的数量，还是通过遍历 fetch() 或者通过 count(fetchAll()) 来根据真实查询到的结果集数量确定这一次查询的真实行数。

其实它就像是 PDO 对象的 exec() 方法所返回的数据。在不使用预处理语句的情况下，直接使用 PDO 的 exec() 方法执行 SQL 语句后，返回的也是语句执行后受影响的行数。

$stmt = $pdo->prepare("select * from zyblog_test_user");
$stmt->execute();
$rowCount = $stmt->rowCount();
echo $rowCount, PHP_EOL;
// 41

增、删、改语句返回受影响的行数

$stmt = $pdo->prepare("insert into zyblog_test_user(username, password, salt) values(?, ?, ?)");
$stmt->execute(['kkk','666','k6']);
$rowCount = $stmt->rowCount();
echo $rowCount, PHP_EOL; // 1
$id = $pdo->lastInsertId();
echo $rowCount, PHP_EOL; // 1

$stmt = $pdo->prepare("update zyblog_test_user set username=? where username = ?");
$stmt->execute(['ccc','cccc']);
$rowCount = $stmt->rowCount();
echo $rowCount, PHP_EOL; // 25

$stmt = $pdo->prepare("update zyblog_test_user set username=? where username = ?");
$stmt->execute(['ccc','cccc']);
$rowCount = $stmt->rowCount();
echo $rowCount, PHP_EOL; // 0

$stmt = $pdo->prepare("delete from zyblog_test_user where username = ?");
$stmt->execute(['ddd']);
$rowCount = $stmt->rowCount();
echo $rowCount, PHP_EOL; // 11

$stmt = $pdo->prepare("delete from zyblog_test_user where username = ?");
$stmt->execute(['ddd']);
$rowCount = $stmt->rowCount();
echo $rowCount, PHP_EOL; // 0

更新和删除操作在数据不存在、没有更新、没有删除的情况下都返回的是 0 。这一点我们也在 PDO 相关的第一篇文章中就说过了，对于业务来说，这种更新或删除到底算是成功还是失败呢？还是大家根据自己的实际业务情况来确定吧！

总结

关于 PDO 和 PDOStatement 相关的内容就学习到这里了。我们完整地梳理了一遍它们两个所有的方法，也都进行了相关的测试。大家在日常使用中可能接触到的并不多，框架都已经为我们封装好了。不过对于学习来说，平常的小测试、小调试完全可以自己手写来加深记忆和理解。在深入理解了这些扩展类的使用方法后，反过来又能帮助我们更加的清楚框架是如何去封装它们的。总之，学习就是不断的从高层到底层，再从底层返回高层，循环往复，才能更加的得心应手。

测试代码：

https://github.com/zhangyue0503/dev-blog/blob/master/php/202009/source/PHP%E4%B8%AD%E7%9A%84PDO%E6%93%8D%E4%BD%9C%E5%AD%A6%E4%B9%A0%EF%BC%88%E5%9B%9B%EF%BC%89%E6%9F%A5%E8%AF%A2%E7%BB%93%E6%9E%84%E9%9B%86.php

推荐学习：php视频教程

The above is the detailed content of Take you to understand the query structure set in PHP in three minutes. For more information, please follow other related articles on the PHP Chinese website!