PDO Fetch Modes

Classic Modes

PDO::FETCH_BOTH
PDO::FETCH_NUM
PDO::FETCH_ASSOC
PDO::FETCH_OBJ

PDO::FETCH_LAZY
Most useful modes

PDO::FETCH_COLUMN
PDO::FETCH_KEY_PAIR
PDO::FETCH_UNIQUE
PDO::FETCH_GROUP

PDO::FETCH_CLASS
PDO::FETCH_PROPS_LATE
PDO::FETCH_CLASSTYPE
PDO::FETCH_INTO
PDO::FETCH_SERIALIZE

Miscellaneous

PDO::FETCH_FUNC
PDO::FETCH_NAMED
PDO::FETCH_BOUND
PDO::FETCH_ORI_NEXT at al
Comments (13)

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:

First, this variable doesn't contain any row data, but just return it on demand (so the name suggests)

Instead, it contains a single mysterious variable called queryString to which the SQL query is assigned. (Which reminds us a PDOStatement class):

$lazy = $pdo->query("SELECT name FROM users")->fetch(PDO::FETCH_LAZY);
/*
object(PDORow)#3 (2) {
  ["queryString"] => string(22) "SELECT name FROM users"
  ["name"]        => string(4) "John"
}*/

For the curious - yes, you can overwrite it :)

Third, you cannot store this object in a session (or, in other words, this object is not serializable)
Fourth, you can get the data in any way - by using a numeric index, an associative index or via the object property.
Fifth, calling an undefined index/property doesn't raise an "Undefined property / index" error. NULL value is silently returned.
Sixth, this variable is changing its state after consequent fetch() calls.
Seventh, this constant cannot be used with fetchAll(), but only with fetch().

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 index: undef 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 sex, users.* 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:

you cannot pass constructor parameters to a newly created object
if no class defined with such a name, an array will be silently returned, while with fetchObject() an error will be thrown
obviously, the dedicated method takes less code to write.

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

if there is a class property, which name is the same as a column name, the column value will be assigned to this property.
if there is no such property, then a magic __set() method will be called.
if __set() method is not defined for the class, then a public property will be created and a column value assigned to it.

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 exactly 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 result set, 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

13.11.24 13:35
eeeeeeeeeet for How to run an UPDATE query using Mysqli:
rft
read more

04.11.24 00:49
PHP programmer for How to check if email exists in the database?:
If you only want to know whether a row exists or not, but don't care about how many rows, always...
read more

02.11.24 12:39
Rehanamol for How to check if email exists in the database?:
If l put my email ID showing that 'your email id not in our data base
read more

31.10.24 13:08
Domenico for Basic principles of web programming:
Could you give your opinion about correct database connection pooling management? Is it possible...
read more

08.10.24 11:39
Nitesh Vishwakarma for How to check if email exists in the database?:
I wnat to make a dynamic multiforms stepper using php and mysql Email id -...
read more

Add a comment

Please refrain from sending spam or advertising of any sort.
Messages with hyperlinks will be pending for moderator's review.

Markdown is now supported:

> before and an empty line after for a quote
to mark a block of code add four spaces before each line and empty lines before and after

Comments:

Hayley Watson, 25.11.23 14:00
@Tim Dawson This is one of those questions that feels like an XY problem, but....
```
$row_assoc = $stmt->fetch(PDO::FETCH_ASSOC);
$row_num = array_values($row_assoc);
```
Tim Dawson, 20.11.23 02:52

That should have been 'FETCH_BOTH' !
Tim Dawson, 20.11.23 02:40

I want to get both NUM and ASSOC information out of a single row, but as two separate arrays. I find that if I apply: $row_assoc = $stmt->fetch(PDO::FETCH_ASSOC); $row_num = $stmt->fetch(PDO::FETCH_NUM); Then I get the data in whichever FETCH is applied first, but the second is empty. It seems the first FETCH destroys the information? No doubt I could 'FETCH_ALL' and then manipulate the resulting array, but is there a simpler way? Regards, Tim Dawson.
Me, 13.08.22 12:29
Hello! I have few question about pivot tables. For example I wanna make users and followers
1. I have table "users" (id, name, created_at, updated_at), id is autoincrement primery key (ai PK) Now im going to create "followers" (id, from_user_id, to_user_id, created_at) id is ai PK, unique key on from, to and foreign keys from, to.
I add id to followers so I have order of followers when querying and writes are in sequence (somewhere I've read that's it good to have ai PK id in every table). But I came across claims that this is bullsh1t and pivot tables should not have id.

So who's right assuming I wanna query followers in desc order of created.

I have a lot of pivot tables some of them need order others don't. I would like to know hard truth. P.s. I use mysql 8
Josh H, 04.04.20 10:12

This is an excellent resource. Thank you for making it! I didn't know they could be combined. After playing around I've found the combination of PDO::FETCH_UNIQUE|PDO::FETCH_ASSOC to be very useful for what I'm working on. Is there any reason to avoid this? If not, others might also find it useful as well if you wanted to give it a mention above. Thanks again!
David, 06.03.20 12:17

FETCH_NAMED just saved my life, or at least a lot of awkward coding. It's very useful when doing JOINs on somewhat indeterminately-structured data.
Arnold D?zsa, 14.11.19 20:37

Dear!

I read your article about FETCH_INTO. I would like to help so you wrote that it seems redundant for you, but it's not. The $user and the $data points to the same object instance. Try the === comparison operator which will tell that i am right. var_dump($data === $user)
Bruno T, 08.03.19 19:09

Is it possible to group by table name?

I know that if I use the PDO :: ATTR_FETCH_TABLE_NAMES attribute it returns results like this array ('Product.name' => 'Test')

But I would like this array ('Product' => array ('name' => 'Test'))

Reply:
Hello Bruno!

As far as I know, there is no way to group like this.
Tiago A., 27.12.18 16:12

FETCH_NAMED isn't useful, but helped me. Thanks a lot.
Marcel, 27.11.18 22:54

I got a hint for PDO::FETCH_SERIALIZE. If the class implements the Serializable interface and the methods this interface dictates, you can unserialize the data with User::unserialize();
Ken, 30.07.18 23:26

Thank you! I've never seen other parts of PDO::FETCH_xxxx. Very useful.
Gonzalo, 24.04.18 00:39

PDO::FETCH_GROUP|PDO::FETCH_COLUMN, just saved my life, thx u so much!!!
Jonathan L, 08.04.18 00:44

This is nice but you have a lot of examples with LIMIT 1 or fetch() but not fetchAll()... I want to see how the result are returned when there is more than one -- is it horizontally or vertically composed?

Reply:
Hello Jonathan!

fetchAll() always returns the results horizontally - just the way rows are stored in the database.