Community Learn Tools Library Leisure
search
English
Home > Backend Development > PHP Problem > body text

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

醉折花枝作酒筹
Release： 2023-03-10 18:10:01
forward
1617 people have browsed it

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.

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

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
// )
Copy after login

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
// )
Copy after login

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
// )
// ……
Copy after login

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
// )
Copy after login

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
//         )

// )
Copy after login

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
// (
// )
Copy after login

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
//         )

// )
Copy after login

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] => 
// )
Copy after login

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
Copy after login

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
// )
Copy after login

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
Copy after login

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

$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
Copy after login

更新和删除操作在数据不存在、没有更新、没有删除的情况下都返回的是 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
Copy after login

推荐学习：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!

Related labels：
php
source：segmentfault.com
Previous article：How to use session in session control in PHP? Next article：How to use Phar code packaging tool in php
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Latest Issues
PHP arrays obtained from URL parameters do not behave as expected I have a URL parameter that contains the category ID and I want to treat it as an array li...
From 2024-04-06 22:09:02
0
1
1428
Where should I place CustomLog directive in apache I'm using php:7.2-apachedocker. I need to disable health check url login access log. Based...
From 2024-04-06 22:03:59
0
1
990
What is the format of the variables in the return value? I am a new learner of php. I found a piece of code: if($x<time()){return[false,'error']...
From 2024-04-06 21:55:20
0
1
778
Problems encountered when using opentbs to generate odt files: values ​​of the same key are displayed in the same row instead of separate columns. I'm using a library called OpenTbs to create odt using PHP, I'm using it because columns a...
From 2024-04-06 20:18:18
0
1
483
Group MySQL results by ID for looping over I have a table with flight data in mysql. I'm writing a php code that will group and displ...
From 2024-04-06 17:27:56
0
1
406
Related Topics
More>
Popular Recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template