How to use PDO to obtain query results in PHP database learning?

In the previous article, I brought you "PHP database learning: How to use PDO to execute SQL statements? ", which introduces in detail the knowledge about how to use PDO to execute SQL statements. In this article, we will continue to look at how PHP uses PDO to obtain query results. Hope it helps everyone!

In the previous article we have learned how to use PDO to execute SQL statements, then let’s take a look at the data acquisition method of PDO, the data acquisition method of PDO It is very similar to other database extensions.

As long as the SELECT query is executed successfully, a result set object will be generated, whether using the qurey() method in the PDO object or using prepare() Preprocessing statements combined with execute() and other methods will get the result set object PDOStatement when executing a SELECT query.

You can obtain the query results of the SELECT statement through the methods in the PDOStatement class. Next, let's take a look at several common methods of obtaining result set data in the PDOStatement class.

<strong><span style="font-size: 20px;">fetch()</span></strong> Method

fetch () The method can obtain the contents of the current row from the result set of a PDOStatement object, and move the result set pointer to the next row. When it reaches the end of the result set, it returns FALSE. The syntax format of this method is as follows:

PDOStatement::fetch([int $fetch_style[, int $cursor_orientation = PDO::FETCH_ORI_NEXT[, int $cursor_offset = 0]]])

What needs to be noted is:

$fetch_style represents an optional parameter, used to control how the next line is returned to the caller. The value of this parameter must be one of the PDO::FETCH_* series of constants, as shown below:

• PDO::FETCH_ASSOC Indicates returning an associative array;

• PDO::FETCH_BOTH (default) Indicates returning an array mixed with an index array plus an associative array

• PDO::FETCH_BOUND means returning TRUE and assigning the value in the result set to the PHP variable bound by the PDOStatement::bindColumn() method

• PDO::FETCH_OBJ means returning an anonymous object whose attribute name corresponds to the column name of the result set.

• PDO::FETCH_CLASS: Returns a new instance of the requested class, mapping the column names in the result set to the corresponding attribute names in the class.

If fetch_style contains PDO::FETCH_CLASSTYPE For example: PDO::FETCH_CLASS | PDO::FETCH_CLASSTYPE, the class name is listed in the first column Determined by the value;

$cursor orientation represents an optional parameter, used to determine which row should be obtained when the object is a scrollable cursor. This value must be one of the PDO::FETCH_ORI_* series of constants, defaulting to PDO::FETCH_ORI_NEXT.

$offset represents an optional parameter. When the parameter $cursor_orientation is set to PDO::FETCH_ORI_ABS, this value specifies the absolute row number of the row in the result set that is to be obtained. ;When parameter $cursor_orientation is set to PDO::FETCH_ORI_REL, this value specifies the position of the row you want to fetch relative to the cursor before calling PDOStatement::fetch().

Next, let’s look at using the fetch() method through an example to obtain the query results of the SELECT statement. The example is as follows:

<?php
    $dsn  = &#39;mysql:host=127.0.0.1;dbname=test&#39;;
    $user = &#39;root&#39;;
    $pwd  = &#39;root&#39;;
    try{
        $pdo = new PDO($dsn,$user,$pwd);
        $sql = &#39;SELECT name,age,sex FROM user WHERE age = \&#39;12\&#39;&#39;;
        $res = $pdo -> query($sql);
        echo &#39;<pre class="brush:php;toolbar:false">&#39;;
        while ($row = $res -> fetch(PDO::FETCH_ASSOC)){
            print_r($row);
        }
    }catch(PDOException $e){
        echo &#39;数据库连接失败：&#39;.$e -> getMessage();
    }
?>

Output result:

From this we complete the acquisition from the result set of a PDOStatement object through the fetch() method The contents of the current line. $offset represents an optional parameter. When the parameter $cursor_orientation is set to PDO::FETCH_ORI_ABS , this value specifies the absolute row number of the row in the result set that is to be obtained. ;When the parameter $cursor_orientation is set to PDO::FETCH_ORI_REL , this value specifies the row you want to fetch relative to the cursor before calling PDOStatement::fetch() Location.

Next let’s take a look at the application of the fetchAll() method.

<strong><span style="max-width:90%">fetchAll()</span></strong> Method

##fetchAll The () method is similar to the fetch() method introduced above, but this method only needs to be called once to get all the rows in the result set and assign them to the returned array. The syntax format of this method is as follows:

PDOStatement::fetchAll([int $fetch_style[, mixed $fetch_argument[, array $ctor_args = array()]]])

What needs to be noted is:

$fetch_style represents optional parameters, which are used to control the content of the returned array. The default value is PDO::FETCH_BOTH. The value of this parameter is the same as that of the fetch() method

$fetch_argument This parameter has different meanings depending on the value of the $fetch_style parameter:

• PDO::FETCH_COLUMN: Returns the specified column indexed starting from 0;

• PDO::FETCH_CLASS：返回指定类的实例，映射每行的列到类中对应的属性名；

• PDO::FETCH_FUNC：将每行的列作为参数传递给指定的函数，并返回调用函数后的结果。

$ctor_args 表示当 $fetch_style 参数为 PDO::FETCH_CLASS 时，自定义类的构造函数的参数。

接下来我们通过示例来看一下fetchAll() 方法的实际应用，示例如下：

<?php
    $dsn  = &#39;mysql:host=127.0.0.1;dbname=test&#39;;
    $user = &#39;root&#39;;
    $pwd  = &#39;root&#39;;
    try{
        $pdo  = new PDO($dsn,$user,$pwd);
        $sql  = &#39;SELECT name,age,sex FROM user WHERE sex = \&#39;男\&#39;&#39;;
        $res  = $pdo -> query($sql);
        $data = $res -> fetchAll(PDO::FETCH_ASSOC);
        echo &#39;<pre class="brush:php;toolbar:false">&#39;;
        print_r($data);
    }catch(PDOException $e){
        echo &#39;数据库连接失败：&#39;.$e -> getMessage();
    }
?>

输出结果：

由此我们便通过使用 fetchAll() 方法，获取 SELECT 语句的查询结果。$ctor_args 表示当 $fetch_style 参数为 PDO::FETCH_CLASS 时，自定义类的构造函数的参数。

接下来我们看一下fetchColumn() 方法的使用。

<strong><span style="max-width:90%">fetchColumn() </span></strong>方法

fetchColumn() 方法可以获取结果集中当前行指定字段的值，其语法格式如下：

PDOStatement::fetchColumn([int $column_number = 0])

其中需要注意的是：

参数 $column_number表示的是想从行里取回的列的索引数字。

如果该参数没有取值，也就是如果没有提供值，那么则会从第一列开始获取。

接下来我们通过示例来看一下fetchColumn() 方法的使用，示例如下：

<?php
    $dsn  = &#39;mysql:host=127.0.0.1;dbname=test&#39;;
    $user = &#39;root&#39;;
    $pwd  = &#39;root&#39;;
    try{
        $pdo = new PDO($dsn,$user,$pwd);
        $sql = &#39;SELECT name,age,sex FROM user&#39;;
        $res = $pdo -> query($sql);
        echo &#39;当前行第一列的值为：&#39;.$res -> fetchColumn().&#39;<br>&#39;;
        echo &#39;当前行第三列的值为：&#39;.$res -> fetchColumn(2).&#39;<br>&#39;;
        echo &#39;当前行第二列的值为：&#39;.$res -> fetchColumn(1).&#39;<br>&#39;;
    }catch(PDOException $e){
        echo &#39;数据库连接失败：&#39;.$e -> getMessage();
    }
?>

输出结果：

由此我们便通过使用 fetchColumn() 方法，获取指定字段的值。

大家如果感兴趣的话，可以点击《PHP视频教程》进行更多关于PHP知识的学习。

The above is the detailed content of How to use PDO to obtain query results in PHP database learning?. For more information, please follow other related articles on the PHP Chinese website!