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.
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
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.
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.
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);
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.
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);
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);
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.
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.
<?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
}
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
}
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
}
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
}
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);
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
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 = $db->query("SELECT userid, name, country, referred_by_userid FROM users");
$stmt->setFetchMode(\PDO::FETCH_INTO, $obj);
$result = $stmt->fetch();
var_dump($result);
The above example will output
something similar to:
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
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);
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);
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);
Predefined Constants
Predefined Constants