PDO Fetch Modes

  1. Classic Modes
  2. PDO::FETCH_LAZY
  3. Most useful modes
  4. OOP
  5. Miscellaneous

Like it is said in the main article, PDO fetch modes (along with usable prepared statements) is a thing that makes PDO a wrapper, not yet another (though universal) database API. These modes could dramatically reduce the amount of code for routine operations, as they let you to get the data in the proper format right out of the query.

Although constants in question are partially described on the manual pages for fetch() and fetchAll() methods, the full list can be found only on the page with all PDO constants, where they are just scattered among other constants with descriptions that hardly exceed a few words. It doesn't seem too convenient and, most likely is the reason, why some interesting modes escaped attention of a general PHP audience. I decided to take the fetch modes out of the global list and split them into several categories for convenience, as total number of PDO fetch modes amounts to a decent figure of 23!

Moreover, some modes can be combined together, increasing the number of possible return formats even mere. However, one have to keep in mind that not all the constants' values are powers of 2, and thus some constants cannot be combined. For example, PDO::FETCH_FUNC has a bit unexpected value of 10 (which is equal to PDO::FETCH_CLASS|PDO::FETCH_ASSOC combination - though quite useless by itself).

Classic Modes

For starter let's list the modes that resemble the behavior of old good mysql functions.

PDO::FETCH_BOTH

A counterpart for mysql_fetch_array(). The row is returned in the form of array, where data is duplicated, to be accessed via both numeric and associative indexes. This mode is set by default.

$user $pdo->query("SELECT * from users LIMIT 1")->fetch(PDO::FETCH_BOTH);
/*
array (
  'id' => '104',
  0 => '104',
  'name' => 'John',
  1 => 'John',
  'sex' => 'male',
  2 => 'male',
  'car' => 'Toyota',
  3 => 'Toyota',
)*/

PDO::FETCH_NUM

An old pal again, a mysql_fetch_row() counterpart, numeric indices only:

$user $pdo->query("SELECT * from users LIMIT 1")->fetch(PDO::FETCH_NUM);
/*
array (
  0 => '104',
  1 => 'John',
  2 => 'male',
  3 => 'Toyota',
)*/

PDO::FETCH_ASSOC

Same thing, but for mysql_fetch_assoc() - associative indices only. See also PDO::FETCH_NAMED

$user $pdo->query("SELECT * from users LIMIT 1")->fetch(PDO::FETCH_ASSOC);

/*
array (
  'id' => '104',
  'name' => 'John',
  'sex' => 'male',
  'car' => 'Toyota',
)*/

PDO::FETCH_OBJ

like mysql_fetch_object() but without class name provided. with no class name provided. Returns stdClass instance.

$user $pdo->query("SELECT * from users LIMIT 1")->fetch(PDO::FETCH_OBJ);
/*
stdClass::__set_state(array(
   'id' => '104',
   'name' => 'John',
   'sex' => 'male',
   'car' => 'Toyota',
))*/

Note that PDO is not that silly with returning objects. A whole dedicated section on object manipulation follows below.

PDO::FETCH_LAZY

This constant is so remarkable that I decided to dedicate an entire section to it. For starter, the returned value being an instance of a special class PDORow, and this object bears a whole lot of peculiar features:

Let's do some tests. We'll try to request a considerable amount of data and watch the memory consumption, verifying a couple things along the way:

$stmt $pdo->query("SELECT *, REPEAT(' ', 1024 * 1024) big FROM users");
echo 
'start       'round(memory_get_usage() / 1024), PHP_EOL;
$lazy $stmt->fetch(PDO::FETCH_LAZY);
echo 
'lazy fetch  'round(memory_get_usage() / 1024), PHP_EOL;
$big  $lazy[3];
echo 
'lazy assign 'round(memory_get_usage() / 1024), PHP_EOL;
echo 
'lazy name   '$lazy[0], PHP_EOL;
echo 
'lazy undef  'var_dump($lazy['undef']);

echo 
'------------'PHP_EOL;

$num $stmt->fetch(PDO::FETCH_NUM);
echo 
'num fetch   'round(memory_get_usage() / 1024), PHP_EOL;
$big2 $num[3];
echo 
'num assign  'round(memory_get_usage() / 1024), PHP_EOL;
$big2 .= ''// to invoke a copy-on-write
echo 'num assign2 'round(memory_get_usage() / 1024), PHP_EOL;
echo 
'lazy name   '$lazy[0], PHP_EOL;
echo 
'num undef  'var_dump($num['undef']);

Output:

start       228
lazy fetch  228
lazy assign 1252
lazy name   John
lazy undef  NULL
------------
num fetch   2277
num assign  2277
num assign2 3301
lazy name   Mike
num undef  
Notice
:  Undefined indexundef in pdo.php on line 48
NULL

As you can see, this code is fetching all records from the users table, adding one column of the size 1Mb. First fetch is done with PDO::FETCH_LAZY and it can be seen that it doesn't change the amount of memory consumed (note that this behavior can be changed depends on the buffering mode used). After that we are fetching another row, using one of conventional methods and watching the immediate memory consumption increase. So the memory save is evident. Also we observed the "Undefined variable" error absence, and the status change after a consequent fetch() call.

From this we can conclude that PDORow object is just a link to the resultset. Having no own state, it is just reading the data from the current cursor position. Given all that, it is quite surprising to see this mode so rarely used.

Most useful modes

Here I decided to list the most useful modes, that return the data in most demanded formats. All these modes make sense only with fetchAll().

Note that you can always combine several fetch modes to alter the result format. All the examples below are given with PDO::FETCH_ASSOC set as a default fetch format.

PDO::FETCH_COLUMN

It is often very handy to get plain one-dimensional array right out of the query, if only one column out of many rows being fetched. Here you go:

$data $pdo->query('SELECT name FROM users')->fetchAll(PDO::FETCH_COLUMN);

/* array (
  0 => 'John',
  1 => 'Mike',
  2 => 'Mary',
  3 => 'Kathy',
) */

PDO::FETCH_KEY_PAIR

Also extremely useful format, when we need to get the same column as above, but indexed not by numbers in order but by an unique field:

$data $pdo->query('SELECT name, car FROM users')->fetchAll(PDO::FETCH_KEY_PAIR);

/* array (
  'John' => 'Toyota',
  'Mike' => 'Ford',
  'Mary' => 'Mazda',
  'Kathy' => 'Mazda',
)

The returned key-value format is excellent for the dictionary like data or simply for indexed values, like below

$data $pdo->query('SELECT sex, count(*) FROM users GROUP BY sex')->fetchAll(PDO::FETCH_KEY_PAIR);

/* array (
  'male' => 2,
  'female' => 2,
)

Note that you have to select only two columns for this mode, first of which have to be unique.

PDO::FETCH_UNIQUE

Same as above, but getting not one column but full row, yet indexed by an unique field

$data $pdo->query('SELECT * FROM users')->fetchAll(PDO::FETCH_UNIQUE);
/* array (
  104 => array (
    'name' => 'John',
    'car' => 'Toyota',
  ),
  110 => array (
    'name' => 'Mike',
    'car' => 'Ford',
  ),
  120 => array (
    'name' => 'Mary',
    'car' => 'Mazda',
  ),
  121 => array (
    'name' => 'Kathy',
    'car' => 'Mazda',
  ),
)*/

here you get the data array indexed by id (Note that the first column selected have to be unique. In this query it is assumed that first column is id, but to be sure better list it up explicitly). Or you can use any other unique field as well:

$data $pdo->query('SELECT name, users.* FROM users')->fetchAll(PDO::FETCH_UNIQUE);

/* array (
  'John' => array (
    'sex' => 'male',
    'car' => 'Toyota',
  ),
  'Mike' => array (
    'sex' => 'male',
    'car' => 'Ford',
  ),
  'Mary' => array (
    'sex' => 'female',
    'car' => 'Mazda',
  ),
  'Kathy' => array (
    'sex' => 'female',
    'car' => 'Mazda',
  ),
) */

PDO::FETCH_GROUP

This mode groups the returned rows into a nested array, where indexes will be unique values from the first column, and values will be arrays similar to ones returned by regular fetchAll(). The following code, for example, will separate boys from girls and put them into different arrays:

$data $pdo->query('SELECT sex, name, car FROM users')->fetchAll(PDO::FETCH_GROUP);

/* array (
  'male' => array ( 0 => 
    array (
      'name' => 'John',
      'car' => 'Toyota',
    ),
    1 => array (
      'name' => 'Mike',
      'car' => 'Ford',
    ),
  ),
  'female' => array (
    0 => array (
      'name' => 'Mary',
      'car' => 'Mazda',
    ),
    1 => array (
      'name' => 'Kathy',
      'car' => 'Mazda',
    ),
  ),
) */

So, this is the ideal solution for such a popular demand like "group events by date" or "group goods by category".

This mode could be combined with PDO::FETCH_COLUMN:

$sql "SELECT sex, name FROM users";
$data $pdo->query($sql)->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_COLUMN);
/*
array (
  'male' => 
  array (
    0 => 'John',
    1 => 'Mike',
  ),
  'female' => 
  array (
    0 => 'Mary',
    1 => 'Kathy',
  ),
)*/

A hint: in case you need to select all the fields, but group by not the first one, the first idea that sporings in the mind won't work:

SELECT sex, * FROM users

will return an error. To avoid it, just perpend the asterisk with the table name:

SELECT sexusers.* FROM users

now it works like a charm!

OOP

Of course, PDO's ability to manipulate objects is not limited to returning a silly stdObject instance. In the following section we will learn a whole bunch of object-oriented fetch modes.

PDO::FETCH_CLASS

The cornerstone of object manipulation in PDO. Creates an instance of a class with a given name, mapping returned columns to the class' properties. This mode can be used to get either a single row or an array of rows from database. With fetchAll() the approach is quite familiar:

class User {};
$users $pdo->query('SELECT name FROM users')->fetchAll(PDO::FETCH_CLASS'User');

will give you an array consists of objects of a User class, with properties filled from returned data:

array(2) {
  [
0]=> object(User)#3 (1) {
    
["name"] => string(4"John"
  
}
  [
1]=> object(User)#4 (1) {
    
["name"]=> string(4"Mike"
  
}
}

While to get a single row you have two options. However, although you could use the the familiar fetch() method, like shown below:

class User {};
$stmt $pdo->query('SELECT name FROM users LIMIT 1');
$stmt->setFetchMode(PDO::FETCH_CLASS'User');
$user $stmt->fetch();

it is recommended to use a dedicated fetchObject() method:

class User {};
$user $pdo->query('SELECT name FROM users LIMIT 1')->fetchObject('User');

as there are several issues with using fetch() to get an object:

No matter which method you choose, all the returned columns will be assigned to the corresponding class' properties according to the following rules:

For example, this code

class User
{
    public 
$name;
}
$user $pdo->query('SELECT * FROM users LIMIT 1')->fetchObject('User');

will give you an object with all the properties automatically assigned, no matter were they ixist in the class or not:

object(User)#3 (4) {
  
["id"]   => string(3"104"
  
["name"] => string(4"John"
  
["sex"]  => string(4"male"
  
["car"]  => string(6"Toyota"
}

From this you can tell that to avoid an automated property creation you need to use the magic __set() method to filter the properties out. The simplest filtering technique would be just adding an empty __set() method to your object. With it, only existing properties will be set:

class User
{
    private 
$name;
    public function 
__set($name$value) {}
}
$user $pdo->query('SELECT * FROM users LIMIT 1')->fetchObject('User');

array(
1) {
  [
0]=> object(User)#3 (1) {
    
["name":"User":private]=> string(4"John"
  
}
}

As you can see, in this mode PDO can assign values to private properties as well. Which is a bit unexpected but extremely useful.

Of course, for the newly created classes we may want to supply constructor parameters. So, let's add them to the examples above:

class User {
    public function 
__construct($car) {
        
$this->car $car;
    }
}
$users $pdo->query('SELECT name FROM users LIMIT 1')
            ->
fetchAll(PDO::FETCH_CLASS'User', ['Caterpillar']);

// or using fetch()
$stmt $pdo->query('SELECT name FROM users LIMIT 1');
$stmt->setFetchMode(PDO::FETCH_CLASS'User', ['Caterpillar']);
$user $stmt->fetch();

will give you

/*
object(User)#3 (2) {
    ["name"] => string(4) "John"
    ["car"]  => string(11) "Caterpillar"
} */

As you can see, in this mode PDO assign class properties before calling a constructor. To amend this behavior, use the following flag:

PDO::FETCH_PROPS_LATE

Not a fetch mode but a modifier flag. As it was noted above, by default PDO assigns class properties before calling a constructor. This behavior can be changed using this flag:

class User {
    public function 
__construct($car) {
        
$this->car $car;
    }
}
$stmt $pdo->query('SELECT name, car FROM users LIMIT 1');
$stmt->setFetchMode(PDO::FETCH_CLASS|PDO::FETCH_PROPS_LATE'User', ['Caterpillar']);
$user $stmt->fetch();
/*
object(User)#3 (2) {
  ["car"]  => string(6) "Toyota"
  ["name"] => string(4) "John"
} */

As you can see, a value assigned in the constructor has been overwritten.

PDO::FETCH_CLASSTYPE

One more modifier flag which tells PDO to get the class name from the first column's value. With this flag one can avoid using setFetchMode() with fetch():

$data $pdo->query("SELECT 'User', name FROM users")
            ->
fetch(PDO::FETCH_CLASS PDO::FETCH_CLASSTYPE);
/ *
object(User)#3 (1) {
  
["name"]=> string(4"John"
} */

Besides, as it was noted in the comments to the main article, this mode can be useful if objects of different classes can be created from the same query

$stmt $pdo->query('SELECT sex, name FROM users');
$users $stmt->fetchAll(PDO::FETCH_CLASS PDO::FETCH_CLASSTYPE);
/*
array(6) {
  [0]=> object(Male)#3 (1) {
    ["name"]=> string(4) "John"
  }
  [1]=> object(Male)#4 (1) {
    ["name"]=> string(4) "Mike"
  }
  [2]=> object(Female)#5 (1) {
    ["name"]=> string(4) "Mary"
  }
  [3]=> object(Female)#6 (1) {
    ["name"]=> string(5) "Kathy"
  }
}*/

PDO::FETCH_INTO

Unlike PDO::FETCH_CLASS, doesn't create a new object but update the existing one. Works with setFetchMode() only, which takes the existing variable as a parameter. Obviously, useless with fetchAll().

class User
{
    public 
$name;
    public 
$state;

    public function 
__construct()
    {
        
$this->name NULL;
    }
}
$user = new User;
$user->state "up'n'running";
var_dump($user);

$stmt $pdo->query('SELECT name FROM users LIMIT 1');
$stmt->setFetchMode(PDO::FETCH_INTO$user);
$data $stmt->fetch();
var_dump($data$user);
/*
object(Foo)#2 (2) {
  ["name"]  => NULL
  ["state"] => string(12) "up'n'running"
}
object(Foo)#2 (2) {
  ["name"]  => string(4) "John"
  ["state"] => string(12) "up'n'running"
}
object(Foo)#2 (2) {
  ["name"]  => string(4) "John"
  ["state"] => string(12) "up'n'running"
} */    

As you can see, fetch() call is returning the same object, which seems redundant to me. Also note that unlike PDO::FETCH_CLASS, this mode doesn't assign private properties.

PDO::FETCH_SERIALIZE

One more flag for PDO::FETCH_CLASS. Allegedly should return an object that was serialized and stored in a database. At the moment doesn't work.

Intended to do something like

class foo {}
$foo = new foo;
$foo->status="up'n'running";
$sFoo serialize($foo);
// storing $sFoo in a database
// and then something like
$stmt $pdo->query('SELECT sFoo FROM table');
$stmt->setFetchMode(PDO::FETCH_CLASS|PDO::FETCH_SERIALIZE'foo');
$foo $stmt->fetch();

But the object returned from a database is not the same as it was before serialization! And thus unserialize fails. After toying around this mode with a friend, we submitted a bug, but nothing seems changed since.

Either way, this flag looks a rather useless, as one could always unserialize manually:

class User {
    protected 
$name "John";
}
$serialized serialize(new User);
var_dump($serialized);
$stmt $pdo->prepare('INSERT INTO sertest VALUES(?)')->execute([$serialized]);
$user unserialize($pdo->query('SELECT * FROM sertest')->fetchColumn());
var_dump($user);
/*
string(40) "O:4:"User":1:{s:7:"*name";s:4:"John";}"
object(User)#2 (1) {
  ["name":protected]=>string(4) "John"
} */

Miscellaneous

PDO::FETCH_FUNC

For the closure lovers. Works with fetchAll() only. Not very convenient as you should list parameters for the every returned column manually. For example, a PDO::FETCH_COLUMN emulator:

$data $pdo
    
->query('SELECT name FROM users')
    ->
fetchAll(PDO::FETCH_FUNC, function($first) {return $first;});

PDO::FETCH_NAMED

Almost texactly the same as PDO::FETCH_ASSOC, but with one little difference. Many times I've seen a question, whether it's possible to distinguish different fields with the same names that returned by same query. With the only answer is using aliases in SQL or numeric indices instead of associative. However, PDO offers another way. If this mode is used, returned values are assigned the same way as with PDO::FETCH_ASSOC, but if there are several columns with the same name in the resultset, all values are stored in the nested array. For example, let's try to select from users and companies, while both tables has a column name. Using PDO::FETCH_ASSOC, we'll lose one of the names:

$data $pdo->query("SELECT * FROM users, companies WHERE users.name=username")->fetch();
/*
array(3) {
  ["name"]     => string(10) "ACME, Inc."
  ["sex"]      => string(4) "male"
  ["username"] => string(4) "John"
}*/

While if PDO::FETCH_NAMED is used instead, everything will be kept intact:

$data $pdo->query("SELECT * FROM users, companies WHERE users.name=username")
            ->
fetch(PDO::FETCH_NAMED);
/*
array(3) {
  ["name"]=> array(2) {
    [0]=> string(4) "John"
    [1]=> string(10) "ACME, Inc."
  }
  ["sex"]      => string(4) "male"
  ["username"] => string(4) "John"
}

I doubt whether this feature is useful at all, but at least it gives you a choice. .

PDO::FETCH_BOUND

An interesting mode, essentially different from others. It doesn't return any data by itself, but makes PDO assign values to variables that has been previously bound using bindColumn() - the behavior that can be familiar to mysqli users. And example can be seen in the manual page

PDO::FETCH_ORI_NEXT at al

6 modes to operate the cursor. An example from the manual


Related articles: