- Why PDO?
- Connecting. DSN
- Running queries. PDO::query()
- Prepared statements. Protection from SQL injections
- Prepared statements. Multiple execution
- Running SELECT INSERT, UPDATE, or DELETE statements
- Getting data out of statement. foreach()
- Getting data out of statement. fetch()
- Getting data out of statement. fetchColumn()
- Getting data out of statement in dozens different formats. fetchAll()
- Error handling. Exceptions
- Getting row count with PDO
- Affected rows and insert id
- Prepared statements and LIKE clause
- Prepared statements and IN clause
- Protecting table and field names
- A problem with LIMIT clause
- Calling stored procedures in PDO
- Running multiple queries with PDO
- Emulation mode. PDO::ATTR_EMULATE_PREPARES
- Mysqlnd and buffered queries. Huge datasets.
There are many tutorials on PDO already, but unfortunately, most of them fail to explain the real benefits of PDO, or even promote rather bad practices. The only two exceptions are phptherightway.com and hashphp.org, but they miss a lot of important information. As a result, half of PDO’s features remain in obscurity and are almost never used by PHP developers, who, as a result, are constantly trying to reinvent the wheel which already exists in PDO.
Unlike those, this tutorial is written by someone who has used PDO for many years, dug through it, and answered thousands of questions on Stack Overflow (the sole gold PDO badge bearer). Following the mission of this site, this article will disprove various delusions and bad practices, while showing the right way instead.
Although this tutorial is based on mysql driver, the information, in general, is applicable for any driver supported.
First things first. Why PDO at all?
PDO is a Database Access Abstraction Layer. The abstraction, however, is two-fold: one is widely known but less significant, while another is obscure but of most importance.
Everyone knows that PDO offers a unified interface to access many different databases. Although this feature is magnificent by itself, it doesn’t make a big deal for the particular application, where only one database backend is used anyway. And, despite some rumors, it is impossible to switch database backends by changing a single line in PDO config – due to different SQL flavors (to do so, one needs to use an averaged query language like DQL). Thus, for the average LAMP developer, this point is rather insignificant, and to him, PDO is just a more complicated version of familiar
mysql(i)_query() function. However, it is not; it is much, much more.
PDO abstracts not only a database API, but also basic operations that otherwise have to be repeated hundreds of times in every application, making your code extremely WET. Unlike mysql and mysqli, both of which are low level bare APIs not intended to be used directly (but only as a building material for some higher level abstraction layer),
PDO is such an abstraction already. Still incomplete though, but at least usable.
The real PDO benefits are:
- security (usable prepared statements)
- usability (many helper functions to automate routine operations)
- reusability (unified API to access multitude of databases, from SQLite to Oracle)
Note that although PDO is the best out of native db drivers, for a modern web-application consider to use an ORM with a Query Builder, or any other higher level abstraction library, with only occasional fallback to vanilla PDO. Good ORMs are Doctrine, Eloquent, RedBean, and Yii::AR. Aura.SQL is a good example of a PDO wrapper with many additional features.
Either way, it’s good to know the basic tools first. So, let’s begin:
PDO has a fancy connection method called DSN. It’s nothing complicated though – instead of one plain and simple list of options, PDO asks you to input different configuration directives in three different places:
db (schema) nameand
charset, as well as less frequently used
unix_socketgo into DSN;
passwordgo to constructor;
- all other options go into options array.
where DSN is a semicolon-delimited string, consists of
param=value pairs, that begins from the driver name and a colon:
Note that it’s important to follow the proper format – no spaces or quotes or other decorations have to be used in DSN, but only parameters, values and delimiters, as shown in the manual.
Here goes an example for mysql:
With all aforementioned variables properly set, we will have proper PDO instance in
Important notes for the late mysql extension users:
- Unlike old
mysql_*functions, which can be used anywhere in the code,
PDOinstance is stored in a regular variable, which means it can be inaccessible inside functions – so, one has to make it accessible, by means of passing it via function parameters or using more advanced techniques, such as IoC container.
- The connection has to be made only once! No connects in every function. No connects in every class constructor. Otherwise, multiple connections will be created, which will eventually kill your database server. Thus, a sole PDO instance has to be created and then used through whole script execution.
- It is very important to set charset through DSN – that’s the only proper way because it tells PDO which charset is going to be used. Therefore forget about running
SET NAMESquery manually, either via
PDO::MYSQL_ATTR_INIT_COMMAND. Only if your PHP version is unacceptably outdated (namely below 5.3.6), you have to use
SET NAMESquery and always turn emulation mode off.
More details regarding Mysql can be found in the corresponding chapter, Connecting to MySQL
Running queries. PDO::query()
There are two ways to run a query in PDO. If no variables are going to be used in the query, you can use the PDO::query() method. It will run your query and return special object of PDOStatement class which can be roughly compared to a resource, returned by
mysql_query(), especially in the way you can get actual rows out of it:
query() method allows us to use a neat method chaining for SELECT queries, which will be shown below.
Prepared statements. Protection from SQL injections
This is the main and the only important reason why you were deprived from your beloved
mysql_query() function and thrown into the harsh world of Data Objects: PDO has prepared statements support out of the box. Prepared statement is the only proper way to run a query, if any variable is going to be used in it. The reason why it is so important is explained in detail in The Hitchhiker’s Guide to SQL Injection prevention.
So, for every query you run, if at least one variable is going to be used, you have to substitute it with a placeholder, then prepare your query, and then execute it, passing variables separately.
First of all, you have to alter your query, adding placeholders in place of variables. Say, a code like this
Note that PDO supports positional (
?) and named (
Having a query with placeholders, you have to prepare it, using the
PDO::prepare() method. This function will return the same
PDOStatement object we were talking about above, but without any data attached to it.
Finally, to get the query executed, you must run
execute() method of this object, passing variables in it, in the form of array. And after that, you will be able to get the resulting data out of statement (if applicable):
As you can see, for the positional placeholders, you have to supply a regular array with values, while for the named placeholders, it has to be an associative array, where keys have to match the placeholder names in the query. You cannot mix positional and named placeholders in the same query.
Please note that positional placeholders let you write shorter code, but are sensitive to the order of arguments (which have to be exactly the same as the order of the corresponding placeholders in the query). While named placeholders make your code more verbose, they allow random binding order.
Also note that despite a widespread delusion, no “
:” in the keys is required.
After the execution you may start getting your data, using all supported methods, as described down in this article.
More examples can be found in the respective article.
Passing data into
execute() (like shown above) should be considered default and most convenient method. When this method is used, all values will be bound as strings (save for
NULL values, that will be sent to the query as is, i.e. as SQL
NULL), but most of time it’s all right and won’t cause any problem.
However, sometimes it’s better to set the data type explicitly. Possible cases are:
- LIMIT clause (or any other SQL clause that just cannot accept a string operand) if emulation mode is turned ON.
- complex queries with non-trivial query plan that can be affected by a wrong operand type
- peculiar column types, like
BOOLEANthat require an operand of exact type to be bound (note that in order to bind a BIGINT value with PDO::PARAM_INT you need a mysqlnd-based installation).
In such a case explicit binding have to be used, for which you have a choice of two functions, bindValue() and bindParam(). The former one has to be preferred, because, unlike
bindParam() it has no side effects to deal with.
Query parts you can bind
It is very important to understand which query parts you can bind using prepared statements and which you cannot. In fact, the list is overwhelmingly short: only string and numeric literals can be bound. So you can tell that as long as your data can be represented in the query as a numeric or a quoted string literal – it can be bound. For all other cases you cannot use PDO prepared statements at all: neither an identifier, or a comma-separated list, or a part of a quoted string literal or whatever else arbitrary query part cannot be bound using a prepared statement.
Workarounds for the most frequent use cases can be found in the corresponding part of the article
Prepared statements. Multiple execution
Sometimes you can use prepared statements for the multiple execution of a prepared query. It is slightly faster than performing the same query again and again, as it does query parsing only once. This feature would have been more useful if it was possible to execute a statement prepared in another PHP instance. But alas – it is not. So, you are limited to repeating the same query only within the same instance, which is seldom needed in regular PHP scripts and which is limiting the use of this feature to repeated inserts or updates:
Note that this feature is a bit overrated. Not only it is needed too seldom to talk about, but the performance gain is not that big – query parsing is real fast these times.
Note that you can get this advantage only when emulation mode is turned off.
Running SELECT INSERT, UPDATE, or DELETE statements
Come on folks. There is absolutely nothing special in these queries. To PDO they all the same. It doesn’t matter which query you are running.
Just like it was shown above, what you need is to prepare a query with placeholders, and then execute it, sending variables separately. Either for
SELECT query the process is essentially the same. The only difference is (as DML queries do not return any data), that you can use the method chaining and thus call
execute() right along with
However, if you want to get the number of affected rows, the code will have to be the same boresome three lines:
More examples can be found in the respective article.
Getting data out of statement. foreach()
The most basic and direct way to get multiple rows from a statement would be
foreach() loop. Thanks to Traversable interface,
PDOStatement can be iterated over by using
Note that this method is memory-friendly, as it doesn’t load all the resulting rows in the memory but delivers them one by one (though keep in mind this issue).
Getting data out of statement. fetch()
We have seen this function already, but let’s take a closer look. It fetches a single row from database, and moves the internal pointer in the result set, so consequent calls to this function will return all the resulting rows one by one. Which makes this method a rough analogue to
mysql_fetch_array() but it works in a slightly different way: instead of many separate functions (
mysql_fetch_row(), etc), there is only one, but its behavior can be changed by a parameter. There are many fetch modes in PDO, and we will discuss them later, but here are few for starter:
PDO::FETCH_NUMreturns enumerated array
PDO::FETCH_ASSOCreturns associative array
PDO::FETCH_BOTH– both of the above
PDO::FETCH_LAZYallows all three (numeric associative and object) methods without memory overhead.
From the above you can tell that this function have to be used in two cases:
- When only one row is expected – to get that only row. For example,
$row = $stmt->fetch(PDO::FETCH_ASSOC);
Will give you single row from the statement, in the form of associative array.
- When we need to process the returned data somehow before use. In this case it have to be run through usual while loop, like one shown above.
Another useful mode is
PDO::FETCH_CLASS, which can create an object of particular class
will produce an array filled with objects of News class, setting class properties from returned values. Note that in this mode
- properties are set before constructor call
- for all undefined properties
__setmagic method will be called
- if there is no
__setmethod in the class, then new property will be created
- private properties will be filled as well, which is a bit unexpected but quite handy
Note that default mode is
PDO::FETCH_BOTH, but you can change it using
PDO::ATTR_DEFAULT_FETCH_MODE configuration option as shown in the connection example. Thus, once set, it can be omitted most of the time.
Only when PDO is built upon mysqlnd and emulation mode is off, then PDO will return
float values with respective types. Say, if we create a table
And then query it from mysqlnd-based PDO with emulation turned off, the output will be
Otherwise the familiar
mysql_fetch_array() behavior will be followed – all values returned as strings with only
NULL returned as
If for some reason you don’t like this behavior and prefer the old style with strings and NULLs only, then you can use the following configuration option to override it:
Note that for the
DECIMAL type the string is always returned, due to nature of this type intended to retain the precise value, unlike deliberately non-precise FLOAT and DOUBLE types.
Getting data out of statement. fetchColumn()
A neat helper function that returns value of the singe field of returned row. Very handy when we are selecting only one field:
Getting data out of statement in dozens different formats. fetchAll()
That’s most interesting function, with most astonishing features. Mostly thanks to its existence one can call PDO a wrapper, as this function can automate many operations otherwise performed manually.
PDOStatement::fetchAll() returns an array that consists of all the rows returned by the query. From this fact we can make two conclusions:
- This function should not be used if many* rows have been selected. In such a case, a conventional while loop should be used to fetch rows one by one instead of getting them all as an array at once.
* “many” means more than is suitable to be shown on the average web page.
- This function is mostly useful in a modern web application that never outputs data right away during fetching, but rather passes it to template.
You’d be amazed, in how many different formats this function can return data in (and how little an average PHP user knows of them), all controlled by
PDO::FETCH_* variables. Some of them are:
Getting a plain array.
By default, this function will return just simple enumerated array consists of all the returned rows. Row formatting constants, such as
PDO::FETCH_OBJ etc can change the row format.
Getting a 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:
Getting key-value pairs.
Also extremely useful format, when we need to get the same column, but indexed not by numbers in order but by another field. Here goes
Note that you have to select only two columns for this mode, first of which have to be unique.
Getting rows indexed by unique field
Same as above, but getting not one column but full row, yet indexed by an unique field, thanks to
Note that 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 explicitly).
Getting rows grouped by some field
PDO::FETCH_GROUP will group rows into a nested array, where indexes will be unique values from the first columns, 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:
So, this is the ideal solution for such a popular demand like “group events by date” or “group goods by category”. Some real life use cases:
- How to multiple query results in order to reduce the query number?
- List records grouped by category name
Of course, there is a
PDO::FETCH_FUNC for the functional programming fans.
More modes are coming soon.
Error handling. Exceptions
Although there are several error handling modes in PDO, the only proper one is
PDO::ERRMODE_EXCEPTION. So, one ought to always set it this way, either by adding this line after creation of PDO instance,
or as a connection option, as demonstrated in the example above. And this is all you need for the basic error reporting.
Reporting PDO errors
Despite what all other tutorials say, you don’t need a
try..catch operator to report PDO errors. Catch an exception only if you have a handling scenario other than just reporting it. Otherwise just let it bubble up to a site-wide handler (note that you don’t have to write one, there is a basic built-in handler in PHP, which is quite good).
The only exception (pun not intended) is the creation of the PDO instance, which in case of error might reveal the connection credentials (that would be the part of the stack trace). In order to hide them, we can wrap the connection code into a
try..catch operator and then throw a new
ErrorException that contains only the message but not the credentials.
A long rant on the matter:
Despite a widespread delusion, you should never catch errors to report them. A module (like a database layer) should not report its errors. This function has to be delegated to an application-wide handler. All we need is to raise an error (in the form of exception) – which we already did. That’s all. Nor should you “always wrap your PDO operations in a
try/catch” like the most popular tutorial from tutsplus recommends. Quite contrary, catching an exception should be rather an exceptional case (pun intended).
In fact, there is nothing special in PDO exceptions – they are errors all the same. Thus, you have to treat them exactly the same way as other errors. If you had an error handler before, you shouldn’t create a dedicated one for PDO. If you didn’t care – it’s all right too, as PHP is good with basic error handling and will conduct PDO exceptions all right.
Exception handling is one of the problems with PDO tutorials. Being acquainted with exceptions for the first time when starting with PDO, authors consider exceptions dedicated to this library, and start diligently (but improperly) handling exceptions for PDO only. This is utter nonsense. If one paid no special attention to any exceptions before, they shouldn’t have changed their habit for PDO. If one didn’t use
try..catch before, they should keep with that, eventually learning how to use exceptions and when it is suitable to catch them.
So now you can tell that the PHP manual is wrong, stating that
If your application does not catch the exception thrown from the PDO constructor, the default action taken by the zend engine is to terminate the script and display a back trace. This back trace will likely reveal the full database connection details, including the username and password.
However, there is no such thing as “the displaying of a back trace”! What zend engine really does is just convert an uncaught exception into a fatal error. And then this fatal error is treated like any other error – so it will be displayed only if appropriate
php.ini directive is set. Thus, although you may or you may not catch an exception, it has absolutely nothing to do with displaying sensitive information, because it’s a totally different configuration setting in response to this. So, do not catch PDO exceptions to report them. Instead, configure your server properly:
On a development server just turn displaying errors on:
While on a production server turn displaying errors off while logging errors on:
- keep in mind that there are other errors that shouldn’t be revealed to the user as well.
Catching PDO exceptions
You may want to catch PDO errors only in two cases:
- If you are writing a wrapper for PDO, and you want to augment the error info with some additional data, like query string. In this case, catch the exception, gather the required information, and re-throw another Exception.
- If you have a certain scenario for handling errors in the particular part of code. Some examples are:
- if the error can be bypassed, you can use try..catch for this. However, do not make it a habit. Empty catch in every aspect works as error suppression operator, and so equally evil it is.
- if there is an action that has to be taken in case of failure, i.e. transaction rollback.
- if you are waiting for a particular error to handle. In this case, catch the exception, see if the error is one you’re looking for, and then handle this one. Otherwise just throw it again – so it will bubble up to the handler in the usual way.
However, in general, no dedicated treatment for PDO exceptions is ever needed. In short, to have PDO errors properly reported:
- Set PDO in exception mode.
- Do not use
try..catchto report errors.
- Configure PHP for proper error reporting
- on a live site set
- on a development site, you may want to set
- of course,
error_reportinghas to be set to E_ALL in both cases
- on a live site set
As a result, you will be always notified of all database errors without a single line of extra code! Further reading.
Getting row count with PDO
You don’t needed it.
Although PDO offers a function for returning the number of rows found by the query,
PDOstatement::rowCount(), you scarcely need it. Really.
If you think it over, you will see that this is a most misused function in the web. Most of time it is used not to count anything, but as a mere flag – just to see if there was any data returned. But for such a case you have the data itself! Just get your data, using either
fetchAll() – and it will serve as such a flag all right! Say, to see if there is any user with such a name, just select a row:
Exactly the same thing with getting either a single row or an array with rows:
Remember that here you don’t need the count, the actual number of rows, but rather a boolean flag. So you got it.
Not to mention that the second most popular use case for this function should never be used at all. One should never use the
rowCount() to count rows in database! Instead, one has to ask a database to count them, and return the result in a single row:
is the only proper way.
- if you need to know how many rows in the table, use
- if you need to know whether your query returned any data – check that data.
- if you still need to know how many rows has been returned by some query (though I hardly can imagine a case), then you can either use
rowCount()or simply call
count()on the array returned by
Thus you could tell that the top answer for this question on Stack Overflow is essentially pointless and harmful – a call to
rowCount() could be never substituted with
SELECT count(*) query – their purpose is essentially different, while running an extra query only to get the number of rows returned by other query makes absolutely no sense.
Affected rows and insert id
PDO is using the same function for returning both number of rows returned by SELECT statement and number of rows affected by DML queries –
PDOstatement::rowCount(). Thus, to get the number of rows affected, just call this function after performing a query.
Another frequently asked question is caused by the fact that mysql won’t update the row, if new value is the same as old one. Thus number of rows affected could differ from the number of rows matched by the WHERE clause. Sometimes it is required to know this latter number.
Although you can tell
rowCount() to return the number of rows matched instead of rows affected by setting
PDO::MYSQL_ATTR_FOUND_ROWS option to TRUE, but, as this is a connection-only option and thus you cannot change it’s behavior during runtime, you will have to stick to only one mode for the application, which could be not very convenient.
PDO::MYSQL_ATTR_FOUND_ROWS is not guaranteed to work, as it’s described in the comment below.
Unfortunately, there is no PDO counterpart for the
mysql(i)_info() function which output can be easily parsed and desired number found. This is one of minor PDO drawbacks.
An auto-generated identifier from a sequence or auto_inclement field in mysql can be obtained from the PDO::lastInsertId function. An answer to a frequently asked question, “whether this function is safe to use in concurrent environment?” is positive: yes, it is safe. Being just an interface to MySQL C API mysql_insert_id() function it’s perfectly safe.
Prepared statements and LIKE clause
Despite PDO’s overall ease of use, there are some gotchas anyway, and I am going to explain some.
One of them is using placeholders with
LIKE SQL clause. At first one would think that such a query will do:
but soon they will learn that it will produce an error. To understand its nature one has to understand that, like it was said above, a placeholder have to represent a complete data literal only – a string or a number namely. And by no means can it represent either a part of a literal or some arbitrary SQL part. So, when working with LIKE, we have to prepare our complete literal first, and then send it to the query the usual way:
Prepared statements and IN clause
Just like it was said above, it is impossible to substitute an arbitrary query part with a placeholder. Any string you bind through a placeholder will be put into query as a single string literal. For example, a string
'1,2,3' will be bound as a string, resulting in
making SQL to search for just one value.
To make it right, one needs separated values, to make a query look like
Thus, for the comma-separated values, like for
IN() SQL operator, one must create a set of
?s manually and put them into the query:
Not very convenient, but compared to mysqli it’s amazingly concise.
In case there are other placeholders in the query, you could use
array_merge() function to join all the variables into a single array, adding your other variables in the form of arrays, in the order they appear in your query:
In case you are using named placeholders, the code would be a little more complex, as you have to create a sequence of the named placeholders, e.g.
:id0,:id1,:id2. So the code would be:
Luckily, for the named placeholders we don’t have to follow the strict order, so we can merge our arrays in any order.
Protecting table and field names
On Stack Overflow I’ve seen overwhelming number of PHP users implementing the most fatal PDO code, thinking that only data values have to be protected. But of course it is not.
Unfortunately, PDO has no placeholder for identifiers (table and field names), so a developer must manually filter them out. Such a filter is often called a “white list” (where we only list allowed values) as opposed to a “black list” where we list disallowed values. Here is a brief example
the same approach should be used for the direction, although the code would be a bit simpler
having gottent these two variables this way will make them 100% safe
The same approach must be used every time a table of a field name is going to be used in the query.
A problem with LIMIT clause
Another problem is related to the SQL
LIMIT clause. When in emulation mode (which is on by default), PDO substitutes placeholders with actual data, instead of sending it separately. And with “lazy” binding (using array in
execute()), PDO treats every parameter as a string. As a result, the prepared
LIMIT ?,? query becomes
LIMIT '10', '10' which is invalid syntax that causes query to fail.
There are two solutions:
One is turning emulation off (as MySQL can sort all placeholders properly). To do so one can run this code:
And parameters can be kept in
Another way would be to bind these variables explicitly while setting the proper param type:
One peculiar thing about
PDO::PARAM_INT: for some reason it does not enforce the type casting. Thus, using it on a number that has a string type will cause the aforementioned error:
"1" in the example to
1 – and everything will go smooth.
To successfully run a transaction, you have to make sure that error mode is set to exceptions, and learn three canonical methods:
beginTransaction()to start a transaction
commit()to commit one
rollback()to cancel all the changes you made since transaction start.
Exceptions are essential for transactions because they can be caught. So in case one of the queries failed, the execution will be stopped and moved straight to the catch block, where the whole transaction will be rolled back.
So a typical example would be like
Please note the following important things:
- PDO error reporting mode should be set to
- you have catch an
PDOException, as it doesn’t matter what particular exception aborted the execution.
- you should re-throw an exception after rollback, to be notified of the problem the usual way.
- also make sure that a table engine supports transactions (i.e. for Mysql it should be InnoDB, not MyISAM)
- there are no Data definition language (DDL) statements that define or modify database schema among queries in your transaction, as such a query will cause an implicit commit
Calling stored procedures in PDO
There is one thing about stored procedures any programmer stumbles upon at first: every stored procedure always returns one extra result set: one (or many) results with actual data and one just empty. Which means if you try to call a procedure and then proceed to another query, then “Cannot execute queries while other unbuffered queries are active” error will occur, because you have to clear that extra empty result first. Thus, after calling a stored procedure that is intended to return only one result set, just call
PDOStatement::nextRowset() once (of course after fetching all the returned data from statement, or it will be discarded):
While for the stored procedures returning many result sets the behavior will be the same as with multiple queries execution:
However, as you can see here is another trick have to be used: remember that extra result set? It is so essentially empty that even an attempt to fetch from it will produce an error. So, we cannot use just
while ($stmt->nextRowset()). Instead, we have to check also for empty result. For which purpose
PDOStatement::columnCount() is just excellent.
This feature is one of essential differences between old mysql ext and modern libraries: after calling a stored procedure with
mysql_query() there was no way to continue working with the same connection, because there is no
nextResult() function for
mysql ext. One had to close the connection and then open a new one again in order to run other queries after calling a stored procedure.
Calling a stored procedure is a rare case where
bindParam() use is justified, as it’s the only way to handle
INOUT parameters. The example can be found in the corresponding manual chapter. However, for mysql it doesn’t work. You have to resort to an SQL variable and an extra call.
Note that for the different databases the syntax could be different as well. For example, to run a sored procedure against Microsoft SQL server, use the following format
where ? marks are placeholders. Note that no braces should be used in the call.
Running multiple queries with PDO
Note there are no reasons to stuff multiple queries in a single call, and generally you don’t need this functionality. Running queries one by one is equal in every way to running them in a batch. The only use case for this functionality I can think of is when you need to execute an existing SQL dump and check for the results.
When in emulation mode, PDO can run mutiple queries in the same statement, either via query() or
prepare()/execute(). To access the result of consequent queries one has to use
Within this loop you’ll be able to gather all the related information from the every query, like affected rows, auto-generated id or errors occurred.
It is important to understand that at the point of
execute() PDO will report the error for the first query only. But if error occurred at any of consequent queries, to get that error one has to iterate over results. Despite some ignorant opinions, PDO can not and should not report all the errors at once. Some people just cannot grasp the problem at whole, and don’t understand that error message is not the only outcome from the query. There could be a dataset returned, or some metadata like insert id. To get these, one has to iterate over resultsets, one by one. But to be able to throw an error immediately, PDO would have to iterate automatically, and thus discard some results. Which would be a clear nonsense.
mysqli_multi_query() PDO doesn’t make an asynchronous call, so you can’t “fire and forget” – send bulk of queries to mysql and close connection, PHP will wait until last query gets executed.
Emulation mode. PDO::ATTR_EMULATE_PREPARES
One of the most controversial PDO configuration options is
PDO::ATTR_EMULATE_PREPARES. What does it do? PDO can run your queries in two ways:
- It can use a real or native prepared statement:
When prepare() is called, your query with placeholders gets sent to mysql as is, with all the question marks you put in (in case named placeholders are used, they are substituted with ?s as well), while actual data goes later, when execute() is called.
- It can use emulated prepared statement, when your query is sent to mysql as proper SQL, with all the data in place, properly formatted. In this case only one roundtrip to database happens, with
execute()call. For some drivers (including mysql) emulation mode is turned
Both methods has their drawbacks and advantages but, and – I have to stress on it – both being equally secure, if used properly. Despite rather appealing tone of the popular article on Stack Overflow, in the end it says that if you are using supported versions of PHP and MySQL properly, you are 100% safe. All you have to do is to set encoding in the DSN, as it shown in the example above, and your emulated prepared statements will be as secure as real ones.
Note that when native mode is used, the data is never appears in the query, which is parsed by the engine as is, with all the placeholders in place. If you’re looking into Mysql query log for your prepared query, you have to understand that it’s just an artificial query that has been created solely for logging purpose, but not a real one that has been executed.
Other issues with emulation mode as follows:
When emulation mode is turned ON
one can use a handy feature of named prepared statements – a placeholder with same name could be used any number of times in the same query, while corresponding variable have to be bound only once. For some obscure reason this functionality is disabled when emulation mode is off:
Also, when emulation is
ON, PDO is able to run multiple queries in one prepared statement.
Also, as native prepared statements support only certain query types, you can run some queries with prepared statements only when emulation is
ON. The following code will return table names in emulation mode and error otherwise:
When emulation mode is turned OFF
One could bother not with parameter types, as mysql will sort all the types properly. Thus, even string can be bound to LIMIT parameters, as it was noted in the corresponding chapter.
Also, this mode will allow to use the advantage of single prepare-multiple execute feature.
It’s hard to decide which mode have to be preferred, but for usability sake I would rather turn it
OFF, to avoid a hassle with
LIMIT clause. Other issues could be considered negligible in comparison.
Mysqlnd and buffered queries. Huge datasets.
Recently all PHP extensions that work with mysql database were updated based on a low-level library called
mysqlnd, which replaced old
libmysql client. Thus some changes in the PDO behavior, mostly described above and one that follows:
There is one thing called buffered queries. Although you probably didn’t notice it, you were using them all the way. Unfortunately, here are bad news for you: unlike old PHP versions, where you were using buffered queries virtually for free, modern versions built upon mysqlnd driver won’t let you to do that anymore:
When using libmysqlclient as library PHP’s memory limit won’t count the memory used for result sets unless the data is fetched into PHP variables. With mysqlnd the memory accounted for will include the full result set.
The whole thing is about a resultset, which stands for all the data found by the query.
When your SELECT query gets executed, there are two ways to deliver the results in your script: buffered and unbuffered one. When buffered method is used, all the data returned by the query gets copied in the script’s memory at once. While in unbuffered mode a database server feeds the found rows one by one.
So you can tell that in buffered mode a resultset is always burdening up the memory on the server even if fetching weren’t started at all. Which is why it is not advisable to select huge datasets if you don’t need all the data from it.
Nonetheless, when old libmysql-based clients were used, this problem didn’t bother PHP uers too much, because the memory consumed by the resultset didn’t count in the the
But with mysqlnd things got changed, and the resultset returned by the buffered query will be count towards both
memory_limit, no matter which way you choose to get the result:
will give you
which means that with buffered query the memory is consumed even if you’re fetching rows one by one!
So, keep in mind that if you are selecting a really huge amount of data, always set
Of course, there are drawbacks. One is infamous
Cannot execute queries while other unbuffered queries are active
error message which means that until you won’t retrieve all the selected rows from the unbuffered query, it will be impossible to run any other query against hte same database connection.
And a couple minor ones,
- With unbuffered query you can’t use
rowCount()method (which is useless, as we learned above)
- Moving (seeking) the current resultset internal pointer back and forth (which is useless as well).