Predefined Constants
PHP Manual

Fetch Modes

See cursor constants for the PDO::FETCH_ORI_* cursor constants.

Basic Fetch Modes

Fetch Mode Summary
PDO::FETCH_DEFAULT Special value for using the current default fetch mode.
PDO::FETCH_ASSOC Array indexed by column name only.
PDO::FETCH_BOTH (Default) Array indexed by both column number and name.
PDO::FETCH_NAMED Variant of PDO::FETCH_ASSOC that retains duplicated columns.
PDO::FETCH_NUM Array indexed by column number only.
PDO::FETCH_COLUMN A single column.
PDO::FETCH_KEY_PAIR Key-value pairs, indexed by the first column.
PDO::FETCH_FUNC Use a function to create the return value. ( PDOStatement::fetchAll only)
PDO::FETCH_OBJ Anonymous (stdClass) object.
PDO::FETCH_CLASS An object of a specified class.

PDO::FETCH_CLASS options

These modes are used to implement options when using PDO::FETCH_CLASS.

Fetch Mode Summary
PDO::FETCH_CLASSTYPE Use the first column as the class name.
PDO::FETCH_PROPS_LATE Call the constructor before setting properties.
PDO::FETCH_SERIALIZE Use PHP serialized data. Deprecated as of PHP 8.1.0.

Single Result Modes

The following modes cannot be used with PDOStatement::fetchAll.

Fetch Mode Summary
PDO::FETCH_BOUND Bind values to specified variables.
PDO::FETCH_INTO Update an existing object.
PDO::FETCH_LAZY Lazy fetch via PDORow for array- and object-like access.

Special Behavior Flags for PDOStatement::fetchAll

The following special modes for multiple results only work with PDOStatement::fetchAll and do not work with some other fetch modes. Check the full documentation for details.

Fetch Mode Summary
PDO::FETCH_GROUP Results are grouped by the first column.
PDO::FETCH_UNIQUE Results are (uniquely) indexed by the first column.

Handling of Duplicated Column Names

It's possible for results to contain multiple columns that use the same name. For example, when joining 2 tables that both contain a column with the same name.

Because PHP structures such as arrays and objects don't support multiple keys or properties that use the same name, the returned array or object will contain only 1 of the values using the same name.

Which value is returned for a given duplicated name should be considered undefined.

To avoid this issue, explicitly name columns using an alias. For example: 

SELECT table1.created_at AS t1_created_at,
       table2.created_at AS t2_created_at
FROM table1
JOIN table2 ON table1.table2id = table2.id

See also PDO::FETCH_NAMED, PDO::ATTR_FETCH_TABLE_NAMES and PDO::ATTR_FETCH_CATALOG_NAMES.

Setting the Default Fetch Mode

It is possible to set default fetch mode for all queries using PDO::ATTR_DEFAULT_FETCH_MODE with PDO::__construct or PDO::setAttribute.

The default fetch mode for a specific statement can be set using PDOStatement::setFetchMode. This affects reuse as a prepared statement and iteration (using foreach).

Caution

PDOStatement::setAttribute cannot be used to set the default fetch mode. It only accepts driver specific attributes and silently ignores attributes that are not recognized.

PDO::FETCH_DEFAULT (int)

Available as of PHP 8.0.7.

This is a special value that uses the current default fetch mode for a PDOStatement. It's specifically useful as the default value for method parameters when extending PDOStatement for use with PDO::ATTR_STATEMENT_CLASS.

This value cannot be used with PDO::ATTR_DEFAULT_FETCH_MODE.

PDO::FETCH_ASSOC (int)

PDO::FETCH_ASSOC returns an array indexed by column name only. 

<?php
$stmt = $pdo->query("SELECT userid, name, country FROM users");
$row = $stmt->fetch(\PDO::FETCH_ASSOC);
print_r($row);

The above example will output:

Array
(
    [userid] => 104
    [name] => Chris
    [country] => Ukraine
)

PDO::FETCH_BOTH (int)

This is the default fetch mode.

PDO::FETCH_BOTH returns an array indexed by both column number and name. This means that every returned value is duplicated for each result row.

The column number starts at 0 and is determined by the result column order in the query, not (for example) the order columns are defined in the table.

Note: Using the numeric column index is not recommended as this may change when the query is changed, or when the table schema is changed when using SELECT *.

Note: The number of entries indexed by name may not match the number of entries indexed by number in cases where multiple returned columns use the same name. 

<?php
$stmt = $pdo->query("SELECT userid, name, country FROM users");
$row = $stmt->fetch(\PDO::FETCH_BOTH);
print_r($row);

The above example will output:

Array
(
    [id] => 104,
    [0] => 104,
    [name] => Chris,
    [1] => Chris,
    [country] => Ukraine,
    [2] => Ukraine
)

PDO::FETCH_NAMED (int)

PDO::FETCH_NAMED returns results in the same format as PDO::FETCH_ASSOC except that where multiple columns use the same name, all values are returned as a list.

For more information on handling of duplicated column names and alternatives, see the handling of duplicated names section above.

The order in which duplicated values are returned should be considered undefined. There's no way to tell where each value came from. 

<?php
$stmt = $pdo->query(
    "SELECT users.*, referrer.name
     FROM users
     LEFT JOIN users AS referrer ON users.referred_by = referrer.userid
     WHERE userid = 109"
);
$row = $stmt->fetch(\PDO::FETCH_NUM);
print_r($row);

The above example will output:

Array
(
    [userid] => 109
    [name] => Array
        (
            [0] => Toni
            [1] => Chris
        )
    [country] => Germany
    [referred_by] = 104
)

PDO::FETCH_NUM (int)

PDO::FETCH_NUM returns an array indexed by column number only. The column number starts at 0 and is determined by the result column order in the query, not (for example) the order columns are defined in the table.

Note: Using the numeric column index is not recommended as this may change when the query is changed, or when the table schema is changed when using SELECT *. 

<?php
$stmt = $pdo->query("SELECT userid, name, country FROM users");
$row = $stmt->fetch(\PDO::FETCH_NUM);
print_r($row);

The above example will output:

Array
(
    [0] => 104
    [1] => Chris
    [2] => Ukraine
)

PDO::FETCH_COLUMN (int)

PDO::FETCH_COLUMN returns values from a single column. Use the second argument for PDOStatement::setFetchMode or PDOStatement::fetchAll to specify which column is returned.

If the specified column does not exist a ValueError will be thrown. 

<?php
$stmt = $pdo->query("SELECT name, country FROM users LIMIT 3");
$row = $stmt->fetchAll(\PDO::FETCH_COLUMN);
print_r($row);

$stmt = $pdo->query("SELECT name, country FROM users LIMIT 3");
$row = $stmt->fetchAll(\PDO::FETCH_COLUMN, 1);
print_r($row);

The above example will output:

Array
(
    [0] => Chris
    [1] => Jamie
    [2] => Robin
)

Array
(
    [0] => Ukraine
    [1] => England
    [2] => Germany
)

PDO::FETCH_KEY_PAIR (int)

PDO::FETCH_KEY_PAIR returns pairs of values, indexed by the first column. The results must contain only 2 columns. This fetch mode only makes sense with PDOStatement::fetchAll.

Note: If the first column is not unique, values will be lost. Which value(s) are lost should be considered undefined. 

<?php
$stmt = $pdo->query("SELECT name, country FROM users LIMIT 3");
$row = $stmt->fetchAll(\PDO::FETCH_KEY_PAIR);
print_r($row);

The above example will output:

Array
(
    [Chris] => Ukraine
    [Jamie] => England
    [Robin] => Germany
)

PDO::FETCH_FUNC (int)

Specify a function to create the returned value. This mode can only be used with PDOStatement::fetchAll.

The function receives the values as parameters. There's no way to retrieve the column name a given value was associated with. It is crucial to ensure that the column order in the query matches the order of parameters of the function.

Note: The effects of PDO::FETCH_GROUP and PDO::FETCH_UNIQUE are applied to results before the function is called. 

<?php
function valueCreator($col1, $col2, $col3)
{
    return [
        'col1' => $col1,
        'col2' => strtoupper($col2),
        'col3' => $col3,
        'customKey' => 'customValue',
    ];
}

$stmt = $pdo->query("SELECT userid, name, country FROM users LIMIT 3");
$row = $stmt->fetchAll(\PDO::FETCH_FUNC, valueCreator(...));
print_r($row);

The above example will output:

Array
(
    [0] => Array
        (
            [col1] => 104
            [col2] => SAM
            [col3] => Ukraine
            [customKey] => customValue
        )

    [1] => Array
        (
            [col1] => 105
            [col2] => JAMIE
            [col3] => England
            [customKey] => customValue
        )

    [2] => Array
        (
            [col1] => 107
            [col2] => ROBIN
            [col3] => Germany
            [customKey] => customValue
        )

)

PDO::FETCH_OBJ (int)

PDO::FETCH_OBJ returns a stdClass object.

See also PDOStatement::fetchObject and PDO::FETCH_CLASS. 

<?php
$stmt = $pdo->query("SELECT userid, name, country FROM users");
$row = $stmt->fetch(\PDO::FETCH_OBJ);
print_r($row);

The above example will output:

stdClass Object
(
    [userid] => 104
    [name] => Chris
    [country] => Ukraine
)

PDO::FETCH_CLASS (int)

Returns an object of a specified class. For additional behaviors see the option flags.

If a property does not exist with the name of a returned column, it will be dynamically declared. This behavior is deprecated and will cause an error from PHP 9.0.

See also PDOStatement::fetchObject. 

<?php
class TestEntity
{
    public $userid;

    public $name;

    public $country;

    public $referred_by_userid;

    public function __construct()
    {
        print "Constructor called with ". count(func_get_args()) ." args\n";
        print "Properties set when constructor called? "
            . (isset($this->name) ? 'Yes' : 'No') . "\n";
    }
}

$stmt = $db->query(
    "SELECT userid, name, country, referred_by_userid FROM users"
);
$stmt->setFetchMode(PDO::FETCH_CLASS, TestEntity::class);
$result = $stmt->fetch();
var_dump($result);

The above example will output something similar to:

Constructor called with 0 args
Properties set when constructor called? Yes
object(TestEntity)#3 (4) {
  ["userid"]=>
  int(104)
  ["name"]=>
  string(5) "Chris"
  ["country"]=>
  string(7) "Ukraine"
  ["referred_by_userid"]=>
  NULL
}

PDO::FETCH_CLASSTYPE (int)

This fetch mode can only be used combined with PDO::FETCH_CLASS (and its other options).

When this fetch mode is used, PDO will use the first returned column as the name of the class to return.

If the specified class cannot be found, a stdClass object will be returned, without warning or error. 

<?php
class TestEntity
{
    public $userid;

    public $name;

    public $country;

    public $referred_by_userid;

    public function __construct()
    {
        print "Constructor called with ". count(func_get_args()) ." args\n";
        print "Properties set when constructor called? "
            . (isset($this->name) ? 'Yes' : 'No') . "\n";
    }
}

$stmt = $db->query(
    "SELECT 'TestEntity', userid, name, country, referred_by_userid FROM users"
);
$stmt->setFetchMode(PDO::FETCH_CLASS | PDO::FETCH_CLASSTYPE);
$result = $stmt->fetch();
var_dump($result);

The above example will output something similar to:

Constructor called with 0 args
Properties set when constructor called? Yes
object(TestEntity)#3 (4) {
  ["userid"]=>
  int(104)
  ["name"]=>
  string(5) "Chris"
  ["country"]=>
  string(7) "Ukraine"
  ["referred_by_userid"]=>
  NULL
}

PDO::FETCH_PROPS_LATE (int)

This fetch mode can only be used combined with PDO::FETCH_CLASS (and its other options).

When this fetch mode is used, the constructor will be called before the properties are set. 

<?php
class TestEntity
{
    public $userid;

    public $name;

    public $country;

    public $referred_by_userid;

    public function __construct()
    {
        print "Constructor called with ". count(func_get_args()) ." args\n";
        print "Properties set when constructor called? "
            . (isset($this->name) ? 'Yes' : 'No') . "\n";
    }
}

$stmt = $db->query(
    "SELECT userid, name, country, referred_by_userid FROM users"
);
$stmt->setFetchMode(PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE, TestEntity::class);
$result = $stmt->fetch();
var_dump($result);

The above example will output something similar to:

Constructor called with 0 args
Properties set when constructor called? No
object(TestEntity)#3 (4) {
  ["userid"]=>
  int(104)
  ["name"]=>
  string(5) "Chris"
  ["country"]=>
  string(7) "Ukraine"
  ["referred_by_userid"]=>
  NULL
}

PDO::FETCH_SERIALIZE (int)

Warning

This feature has been DEPRECATED as of PHP 8.1.0. Relying on this feature is highly discouraged.

This fetch mode can only be used combined with PDO::FETCH_CLASS (and its other options).

When this fetch mode is used, the specified class must be Serializable.

Caution

This feature does not support a string that contains a complete serialized object (with serialize).

Caution

This fetch mode does not call the constructor. 

<?php
class TestEntity implements Serializable
{
    public $userid;

    public $name;

    public $country;

    public $referred_by_userid;

    public function __construct()
    {
        print "Constructor called with " . count(func_get_args()) . " args\n";
        print "Properties set when constructor called? "
            . (isset($this->name) ? 'Yes' : 'No') . "\n";
    }

    public function serialize() {
        return join(
            "|",
            [$this->userid, $this->name, $this->country, $this->referred_by_userid]
        );
    }

    public function unserialize(string $data) {
        $parts = explode("|", $data);
        $this->userid = (int) $parts[0];
        $this->name = $parts[1];
        $this->country = $parts[2];

        $refId = $parts[3];
        $this->referred_by_userid = ($refId === "" ? null : (int) $refId);
    }
}

print "Set up record (constructor called manually):\n";
$db->exec(
    "CREATE TABLE serialize (
        sdata TEXT
    )"
);

$origObj = new TestEntity();
$origObj->userid = 200;
$origObj->name = 'Seri';
$origObj->country = 'Syria';
$origObj->referred_by_userid = null;

$insert = $db->prepare("INSERT INTO serialize (sdata) VALUES (:sdata)");
$insert->execute(['sdata' => $origObj->serialize()]);

print "\nRetrieve result:\n"
$query = "SELECT sdata FROM serialize";
$stmt = $db->query($query);
// NOTE: Constructor is never called!
$stmt->setFetchMode(PDO::FETCH_CLASS | PDO::FETCH_SERIALIZE, TestEntity::class);
$result = $stmt->fetch();
var_dump($result);

The above example will output something similar to:

Deprecated: TestEntity implements the Serializable interface, which is deprecated. Implement __serialize() and __unserialize() instead (or in addition, if support for old PHP versions is necessary) in Standard input code on line 2
Set up record (constructor called manually):
Constructor called with 0 args
Properties set when constructor called? No

Retrieve result:
Deprecated: PDOStatement::setFetchMode(): The PDO::FETCH_SERIALIZE mode is deprecated in Standard input code on line 58

Deprecated: PDOStatement::fetch(): The PDO::FETCH_SERIALIZE mode is deprecated in Standard input code on line 59
object(TestEntity)#5 (4) {
  ["userid"]=>
  int(200)
  ["name"]=>
  string(4) "Seri"
  ["country"]=>
  string(5) "Syria"
  ["referred_by_userid"]=>
  NULL
}

PDO::FETCH_BOUND (int)

This fetch mode cannot be used with PDOStatement::fetchAll.

This fetch mode does not directly return a result, but binds values to variables specified with PDOStatement::bindColumn. The called fetch method returns true.

Note: When using prepared statements, to work correctly, variables must be bound after the query is executed. 

<?php
$query = "SELECT users.userid, users.name, users.country, referrer.name
    FROM users
    LEFT JOIN users AS referrer ON users.referred_by_userid = referrer.userid";
$stmt = $db->prepare($query);
$stmt->execute();

$stmt->bindColumn('userid', $userId);
$stmt->bindColumn('name', $name);
$stmt->bindColumn('country', $country);
// Bind by column position to resolve duplicated column name
// To avoid this breaking if the query is changed, use an SQL alias instead
// For example: referrer.name AS referrer_name
$stmt->bindColumn(4, $referrerName);

print "\nfetch:\n";
while ($stmt->fetch(\PDO::FETCH_BOUND)) {
    print join("\t", [$userId, $name, $country, ($referrerName ?? 'NULL')]) . "\n";
}

The above example will output:

104	Chris	Ukraine	NULL
105	Jamie	England	NULL
107	Robin	Germany	Chris
108	Sean	Ukraine	NULL
109	Toni	Germany	NULL
110	Toni	Germany	NULL

PDO::FETCH_INTO (int)

This fetch mode cannot be used with PDOStatement::fetchAll.

This fetch mode updates properties in the specified object. The object is returned on success.

If a property does not exist with the name of a returned column, it will be dynamically declared. This behavior is deprecated and will cause an error from PHP 9.0.

Properties must be public and cannot be readonly.

Caution

There's no way to change the object to be updated without using PDOStatement::setFetchMode between retrieving each record. 

<?php
class TestEntity
{
    public $userid;

    public $name;

    public $country;

    public $referred_by_userid;
}

$obj = new TestEntity();
$stmt->setFetchMode(\PDO::FETCH_INTO, $obj);

$stmt = $db->query("SELECT userid, name, country, referred_by_userid FROM users");
$result = $stmt->fetch();
var_dump($result);

The above example will output something similar to:

object(TestEntity)#3 (4) {
  ["userid"]=>
  int(104)
  ["name"]=>
  string(5) "Chris"
  ["country"]=>
  string(7) "Ukraine"
  ["referred_by_userid"]=>
  NULL
}

PDO::FETCH_LAZY (int)

This fetch mode cannot be used with PDOStatement::fetchAll.

This fetch mode returns a PDORow object which provides both array- and object-like access to values (i.e. combines the behavior of PDO::FETCH_BOTH and PDO::FETCH_OBJ), retrieved in a lazy manner.

This can provide memory efficient access (on the PHP side) to unbuffered results on the database server. Whether PDO uses client-side buffering for results depends on the database-specific driver used (and its configuration).

Caution

PDORow will return NULL without any error or warning when accessing properties or keys that are not defined. This can make errors such as typos or queries not returning expected data harder to spot and debug.

Caution

The returned PDORow object is updated each time a result is retrieved. 

<?php
$stmt = $db->query("SELECT userid, name, country, referred_by_userid FROM users");
$result = $stmt->fetch(\PDO::FETCH_LAZY);

print "ID: ". $result[0] ."\n";
print "Name: {$result->name}\n";
print "Country: " . $result['country'] ."\n";
// Returns NULL. No warning or error is raised.
print "Does not exist: " . var_export($result->does_not_exist, true) . "\n";

$differentResult = $stmt->fetch(\PDO::FETCH_LAZY);
// The previously retrieved PDORow now points to the newly retrieved result
print "ID: ". $result[0] ."\n";

The above example will output:

ID: 104
Name: Chris
Country: Ukraine
Does not exist: NULL
ID: 105

PDO::FETCH_GROUP (int)

PDO::FETCH_GROUP returns lists of associative arrays, indexed by a (non-unique) column. This fetch mode only works with PDOStatement::fetchAll.

When combined with PDO::FETCH_UNIQUE, both modes will use the same column, rendering the combination of these modes useless.

This fetch should be combined with one of PDO::FETCH_ASSOC, PDO::FETCH_BOTH, PDO::FETCH_NAMED, PDO::FETCH_NUM, PDO::FETCH_COLUMN or PDO::FETCH_FUNC.

If no fetch mode from the above list is given, the current default fetch mode for the PDOStatement will be used. 

<?php
$stmt = $pdo->query("SELECT country, userid, name FROM users");
$row = $stmt->fetchAll(\PDO::FETCH_GROUP | \PDO::FETCH_ASSOC);
print_r($row);

The above example will output:

Array
(
    [Ukraine] => Array
        (
            [0] => Array
                (
                    [userid] => 104
                    [name] => Chris
                )

            [1] => Array
                (
                    [userid] => 108
                    [name] => Sean
                )

        )
    [England] => Array
        (
            [0] => Array
                (
                    [userid] => 105
                    [name] => Jamie
                )

        )

    [Germany] => Array
        (
            [0] => Array
                (
                    [userid] => 107
                    [name] => Robin
                )

            [1] => Array
                (
                    [userid] => 109
                    [name] => Toni
                )
        )
)

In the above example one should note that the first column is omitted from the array for each row, only available as the key. It can be included by repeating the column, as in the following example: 

<?php
$stmt = $pdo->query("SELECT country, userid, name, country FROM users");
$row = $stmt->fetchAll(\PDO::FETCH_GROUP | \PDO::FETCH_ASSOC);
print_r($row);

The above example will output:

Array
(
    [Ukraine] => Array
        (
            [0] => Array
                (
                    [userid] => 104
                    [name] => Chris
                    [country] => Ukraine
                )

            [1] => Array
                (
                    [userid] => 108
                    [name] => Sean
                    [country] => Ukraine
                )

        )
    [England] => Array
        (
            [0] => Array
                (
                    [userid] => 105
                    [name] => Jamie
                    [country] => England
                )

        )

    [Germany] => Array
        (
            [0] => Array
                (
                    [userid] => 107
                    [name] => Robin
                    [country] => Germany
                )

            [1] => Array
                (
                    [userid] => 109
                    [name] => Toni
                    [country] => Germany
                )
        )
)

PDO::FETCH_UNIQUE (int)

PDO::FETCH_UNIQUE uses the first column to index records, returning 1 record per index value. This fetch mode only works with PDOStatement::fetchAll.

When combined with PDO::FETCH_GROUP, both modes will use the same column, rendering the combination of these modes useless.

This fetch should be combined with one of PDO::FETCH_ASSOC, PDO::FETCH_BOTH, PDO::FETCH_NAMED, PDO::FETCH_NUM, PDO::FETCH_COLUMN or PDO::FETCH_FUNC.

If no fetch mode from the above list is given, the current default fetch mode for the PDOStatement will be used.

When used with a column that is known to be unique (such as record ID), this mode provides the ability to quickly return results indexed by that value.

Note: If the first column is not unique, values will be lost. Which value(s) are lost should be considered undefined.

Caution

Filtering records should be done in SQL where possible. The database will use indexes to optimize this process and return only the required records. Selecting more records than required from the database may significantly increase memory usage and query time for larger result sets. 

<?php
$stmt = $pdo->query("SELECT userid, name, country FROM users LIMIT 3");
$row = $stmt->fetchAll(\PDO::FETCH_UNIQUE | \PDO::FETCH_ASSOC);
print_r($row);

The above example will output:

Array
(
    [104] => Array
        (
            [name] => Chris
            [country] => Ukraine
        )

    [105] => Array
        (
            [name] => Jamie
            [country] => England
        )

    [107] => Array
        (
            [name] => Robin
            [country] => Germany
        )

)

In the above example one should note that the first column is omitted from the array for each row, only available as the key. It can be included by repeating the column, as in the following example: 

<?php
$stmt = $pdo->query("SELECT userid, userid, name, country FROM users LIMIT 3");
$row = $stmt->fetchAll(\PDO::FETCH_UNIQUE | \PDO::FETCH_ASSOC);
print_r($row);

The above example will output:

Array
(
    [104] => Array
        (
            [userid] => 104
            [name] => Chris
            [country] => Ukraine
        )

    [105] => Array
        (
            [userid] => 105
            [name] => Jamie
            [country] => England
        )

    [107] => Array
        (
            [userid] => 107
            [name] => Robin
            [country] => Germany
        )

)