PHP PDO Fetch (With Detailed Explanation)

A PHP PDO Fetch is a method of the (PDOStatement class) which allows you to fetch results from a set of associated row with an object (PDOStatement).

In this article, we will learn what is PDO Fetch and why it is important to learn and understand. This article is a continuation of the previous topic, MySQL Create Tables In PHP with MySQLi and PDO.

What is PHP PDO fetch?

The PDO fetch is a method that returns an array indexed with the same column name and a 0-indexed number column which returns a set of results.

In addition, the PDO fetch also return true and assigns a set of values in the columns in the result set and set to assign variables that are bound with a method name PDOStatement::bindColumn().

What does fetch () do in PHP?

The mysql_fetch_row() allows to fetches one result set of associated row data with a specified identifier result. This row will return an array and each result column will store into an array offset which starts at (offset 0).

How does PHP PDO fetch data?

In PHP, to fetch data from a set of results you will just need to call the following (fetch) methods.

  • The PDOStatement::fetch method allows you to return a (single) row from a result set as an object or array.
  • The PDOStatement::fetchAll method allows you to fetchall PDO fetch which returns all the rows from a result set as an object or array.

PHP PDO fetch parameters

ModeDescription
PDO::FETCH_ASSOCreturns an array indexed by column name as returned in your result set.
PDO::FETCH_BOTH (default)returns an array indexed by both column name and 0-indexed column number as returned in your result set.
PDO::FETCH_BOUNDreturns true and assigns the values of the columns in your result set to the PHP variables to which they were bound with the PDOStatement::bindColumn() method.
PDO::FETCH_CLASSreturns a new instance of the requested class, mapping the columns of the result set to named properties in the class, and calling the constructor afterward, unless PDO::FETCH_PROPS_LATE is also given. If mode includes PDO::FETCH_CLASSTYPE (e.g. PDO::FETCH_CLASS | PDO::FETCH_CLASSTYPE) then the name of the class is determined from a value of the PDO fetch column.
PDO::FETCH_INTOupdates an existing instance of the requested class, mapping the columns of the result set to named properties in the class.
PDO::FETCH_LAZYcombines PDO::FETCH_BOTH and PDO::FETCH_OBJ, creating the object variable names as they are accessed.
PDO::FETCH_NAMEDreturns an array with the same form as PDO::FETCH_ASSOC, except that if there are multiple columns with the same name, the value referred to by that key will be an array of all the values in the row that had that column name.
PDO::FETCH_NUMreturns an array indexed by column number as returned in your result set, starting at column 0.
PDO::FETCH_OBJreturns an anonymous object with property names that correspond to the column names returned in your result set.
PDO::FETCH_PROPS_LATEwhen used with PDO::FETCH_CLASS, the constructor of the class is called before the properties are assigned from the respective column values.

What is PDO query in PHP?

A PDO::query() is used to execute and prepare an (SQL statement) into a one call function, which returns a statement as an object PDOStatement.

Is PDO better than MySQLi?

The PDO is better than MySQLi in terms of database support. A PDO supports at least (12) different types of databases, just like MySQLi which only supports MySQL database only.

There are some instances when you have to switch your project to another database. The PDO or prepared statements will make the entire process simpler.

Example program for fetching rows using different fetch styles

<?php
$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();

/* Exercise PDOStatement::fetch styles for PDO fetch class returns*/
print("PDO::FETCH_ASSOC: ");
print("Return next row as an array indexed by column name\n");
$result = $sth->fetch(PDO::FETCH_ASSOC);
print_r($result);
print("\n");

/* pdo fetch ori abs and pdo fetch class returns*/
print("PDO::FETCH_BOTH: ");
print("Return next row as an array indexed by both column name and number\n");
$result = $sth->fetch(PDO::FETCH_BOTH);
print_r($result);
print("\n");

/*pdo attr default fetch mode and pdo fetch column*/
print("PDO::FETCH_LAZY: ");
print("Return next row as an anonymous object with column names as properties\n");
$result = $sth->fetch(PDO::FETCH_LAZY);
print_r($result);
print("\n");

print("PDO::FETCH_OBJ: ");
print("Return next row as an anonymous object with column names as properties\n");
$result = $sth->fetch(PDO::FETCH_OBJ);
print $result->name;
print("\n");
?>

Output:

PDO::FETCH_ASSOC: Return next row as an array indexed by column name
Array
(
    [name] => mulberry
    [colour] => red
)

PDO::FETCH_BOTH: Return next row as an array indexed by both column name and number
Array
(
    [name] => watermelon
    [0] => watermelon
    [colour] => red
    [1] => red
)

PDO::FETCH_LAZY: Return next row as an anonymous object with column names as properties
PDORow Object
(
    [name] => apple
    [colour] => red
)

PDO::FETCH_OBJ: Return next row as an anonymous object with column names as properties
kiwi

Example program for fetching rows with a scrollable cursor

<?php
function readDataForwards($dbh) {
    $sql = 'SELECT hand, won, bet FROM mynumbers ORDER BY BET';
    $stmt = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
    $stmt->execute();
    while ($row = $stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_NEXT)) {
        $data = $row[0] . "\t" . $row[1] . "\t" . $row[2] . "\n";
        print $data;
    }
}
function readDataBackwards($dbh) {
    $sql = 'SELECT hand, won, bet FROM mynumbers ORDER BY bet';
    $stmt = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
    $stmt->execute();
    $row = $stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_LAST);
    do {
        $data = $row[0] . "\t" . $row[1] . "\t" . $row[2] . "\n";
        print $data;
    } while ($row = $stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_PRIOR));
}

print "Reading forwards:\n";
readDataForwards($conn);

print "Reading backwards:\n";
readDataBackwards($conn);
?>

Output:

Reading forwards:
21    10    5
16    0     5
19    20    10

Reading backwards:
19    20    10
16    0     5
21    10    5

Summary

This article discussed the PHP PDO Fetch. It also tackles what does fetch () do, how does PHP PDO fetch data, what is PDO query, whether Is PDO better than MySQLi, fetches rows using different fetch styles, and fetches rows with a scrollable cursor.

I hope this lesson has helped you learn a lot. Check out my previous and latest articles for more life-changing tutorials which could help you a lot.

Leave a Comment