(The only proper) PDO tutorial
- 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()
- Getting a plain array.
- Getting a column.
- Getting key-value pairs.
- Getting rows indexed by unique field
- Getting rows grouped by some field
- 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
- Transactions
- Calling stored procedures in PDO
- Running multiple queries with PDO
- Emulation mode. PDO::ATTR_EMULATE_PREPARES
- Mysqlnd and buffered queries. Huge datasets.
- Comments (333)
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 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.
Why PDO?
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 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 them, PDO is just a more complicated version of a familiar mysql(i)_query() function. However, it is not; PDO is 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.
The real benefits of PDO 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 using 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:
Connecting. DSN
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 (all examples given for Mysql, for other drivers consult their respective sections in the PHP manual):
database driver,host,db (schema) nameandcharset, as well as less frequently usedportandunix_socketgo into DSN;usernameandpasswordgo 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:
mysql:host=localhost;dbname=test;port=3306;charset=utf8mb4
driver^ ^ colon ^param=value pair ^semicolon
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:
$host = '127.0.0.1';
$db = 'test';
$user = 'root';
$pass = '';
$charset = 'utf8mb4';
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
];
$pdo = new PDO($dsn, $user, $pass, $options);
With all aforementioned variables properly set, we will have proper PDO instance in the $pdo variable.
Important notes:
- 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 strictly once! There must be no connections inside each function or class constructor. In this case 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 the 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 viaquery()orPDO::MYSQL_ATTR_INIT_COMMAND. Only if your PHP version is unacceptably outdated (namely below 5.3.6), you have to useSET 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 a 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 from of it:
$stmt = $pdo->query('SELECT name FROM users');
while ($row = $stmt->fetch())
{
echo $row['name'] . "\n";
}
Also, the 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 (also called a parameterized query) 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.
Long story short, it is not as hard as it seems. In most cases, you need only two functions - prepare() and execute().
First of all, you have to alter your query, adding placeholders in place of variables. Say, a code like this
$sql = "SELECT * FROM users WHERE email = '$email' AND status='$status'";
will become
$sql = 'SELECT * FROM users WHERE email = ? AND status=?';
or
$sql = 'SELECT * FROM users WHERE email = :email AND status=:status';
Note that PDO supports positional (?) and named (:email) placeholders, the latter always begins from a colon and can be written using letters, digits and underscores only.
Also note that no quotes have to be ever used around placeholders.
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):
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = ? AND status=?');
$stmt->execute([$email, $status]);
$user = $stmt->fetch();
// or
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email AND status=:status');
$stmt->execute(['email' => $email, 'status' => $status]);
$user = $stmt->fetch();
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.
Binding methods
Passing data into execute() (like shown above) should be considered the 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
BIGINTorBOOLEANthat 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:
$data = [
1 => 1000,
5 => 300,
9 => 200,
];
$stmt = $pdo->prepare('UPDATE users SET bonus = bonus + ? WHERE id = ?');
foreach ($data as $id => $bonus)
{
$stmt->execute([$bonus, $id]);
}
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
Running these queries is not different from SELECT.
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 DELETE and 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 prepare():
$sql = "UPDATE users SET name = ? WHERE id = ?";
$pdo->prepare($sql)->execute([$name, $id]);
However, if you want to get the number of affected rows, the code will have to be the same boresome three lines:
$stmt = $pdo->prepare("DELETE FROM goods WHERE category = ?");
$stmt->execute([$cat]);
$deleted = $stmt->rowCount();
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 foreach() operator:
$stmt = $pdo->query('SELECT name FROM users');
foreach ($stmt as $row)
{
echo $row['name'] . "\n";
}
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_assoc(), 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 arrayPDO::FETCH_ASSOCreturns associative arrayPDO::FETCH_BOTH- both of the abovePDO::FETCH_OBJreturns objectPDO::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
$news = $pdo->query('SELECT * FROM news')->fetchAll(PDO::FETCH_CLASS, 'News');
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.
Return types.
Starting from PHP 8.3, it is safe to assume that PDO would return int and float values with respective types all the time. Say, if we create a table
create table typetest (string varchar(255), `int` int, `float` float, `null` int);
insert into typetest values('foo',1,1.1,NULL);
And then query it using PDO, the output will be
array(4) {
["string"] => string(3) "foo"
["int"] => int(1)
["float"] => float(1.1)
["null"] => NULL
}
Before that, it only happened when emulation mode was turned off (and mysqlnd used, which is now a standard). Otherwise the familiar mysql_fetch_array() behavior was followed - all values returned as strings with only NULL returned as NULL.
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:
$pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, true);
Note that for the DECIMAL type, 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 single field of returned row. Very handy when we are selecting only one field:
// Getting the name based on id
$stmt = $pdo->prepare("SELECT name FROM table WHERE id=?");
$stmt->execute([$id]);
$name = $stmt->fetchColumn();
// getting number of rows in the table utilizing method chaining
$count = $pdo->query("SELECT count(*) FROM table")->fetchColumn();
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_NUM, PDO::FETCH_ASSOC, PDO::FETCH_OBJ etc can change the row format.
$data = $pdo->query('SELECT name FROM users')->fetchAll(PDO::FETCH_ASSOC);
var_export($data);
/*
array (
0 => array('John'),
1 => array('Mike'),
2 => array('Mary'),
3 => array('Kathy'),
)*/
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:
$data = $pdo->query('SELECT name FROM users')->fetchAll(PDO::FETCH_COLUMN);
/* array (
0 => 'John',
1 => 'Mike',
2 => 'Mary',
3 => 'Kathy',
)*/
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 PDO::FETCH_KEY_PAIR constant:
$data = $pdo->query('SELECT id, name FROM users')->fetchAll(PDO::FETCH_KEY_PAIR);
/* array (
104 => 'John',
110 => 'Mike',
120 => 'Mary',
121 => 'Kathy',
)*/
Note that you have to select only two columns for this mode, first of which has 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
PDO::FETCH_UNIQUE constant:
$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',
),
)*/
Note that first column selected has 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 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". Some real life use cases:
- How to multiple query results in order to reduce the query number?
- List records grouped by category name
Other modes
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,
$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
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
TL;DR:
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:
ini_set('display_errors', 1);
While on a production server turn displaying errors off while logging errors on:
ini_set('display_errors', 0);
ini_set('log_errors', 1);
- 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.
E.g.:
try {
$pdo->prepare("INSERT INTO users VALUES (NULL,?,?,?,?)")->execute($data);
} catch (PDOException $e) {
$existingkey = "Integrity constraint violation: 1062 Duplicate entry";
if (strpos($e->getMessage(), $existingkey) !== FALSE) {
// Take some action if there is a key constraint violation, i.e. duplicate name
} else {
throw $e;
}
}
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
display_errors=offandlog_errors=on - on a development site, you may want to set
display_errors=on - 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 fetch() or 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:
$stmt = $pdo->prepare("SELECT 1 FROM users WHERE name=?");
$stmt->execute([$name]);
$userExists = $stmt->fetchColumn();
Exactly the same thing with getting either a single row or an array with rows:
$data = $pdo->query("SELECT * FROM table")->fetchAll();
if ($data) {
// You have the data! No need for the rowCount() ever!
}
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:
$count = $pdo->query("SELECT count(1) FROM t")->fetchColumn();
is the only proper way.
In essence:
- if you need to know how many rows in the table, use
SELECT COUNT(*)query. - 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 callcount()on the array returned byfetchAll()(if applicable).
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.
Note that 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:
$stmt = $pdo->prepare("SELECT * FROM table WHERE name LIKE '%?%'");
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 has to represent a complete data literal only - a string or a number namely. And by no means it can represent either a part of a literal or some arbitrary SQL. So, when working with LIKE, we have to prepare our complete literal first, and then send it to the query the usual way:
$search = "%$search%";
$stmt = $pdo->prepare("SELECT * FROM table WHERE name LIKE ?");
$stmt->execute([$search]);
$data = $stmt->fetchAll();
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 literal string. For example, a string '1,2,3' will be bound as a is, resulting in
SELECT * FROM table WHERE column IN ('1,2,3')
making SQL to search for just one value.
To make it right, one needs separated values, to make a query look like
SELECT * FROM table WHERE column IN ('1','2','3')
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:
$arr = [1,2,3];
$in = str_repeat('?,', count($arr) - 1) . '?';
$sql = "SELECT * FROM table WHERE column IN ($in)";
$stm = $db->prepare($sql);
$stm->execute($arr);
$data = $stm->fetchAll();
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:
$arr = [1,2,3];
$in = str_repeat('?,', count($arr) - 1) . '?';
$sql = "SELECT * FROM table WHERE foo=? AND column IN ($in) AND bar=? AND baz=?";
$stm = $db->prepare($sql);
$params = array_merge([$foo], $arr, [$bar, $baz]);
$stm->execute($params);
$data = $stm->fetchAll();
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:
// other parameters that are going into query
$params = ["foo" => "foo", "bar" => "bar"];
$ids = [1,2,3];
$in = "";
$i = 0;// we are using an external counter
// because the actual array key could be dangerous
foreach ($ids as $item)
{
$key = ":id".$i++;
$in .= "$key,";
$in_params[$key] = $item; // collecting values into key-value array
}
$in = rtrim($in,","); // :id0,:id1,:id2
$sql = "SELECT * FROM table WHERE foo=:foo AND id IN ($in) AND bar=:bar";
$stm = $db->prepare($sql);
$stm->execute(array_merge($params,$in_params)); // just merge two arrays
$data = $stm->fetchAll();
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
$orders = ["name","price","qty"]; // the white list of allowed fierld names
$key = array_search($_GET['sort'], $orders); // see if we have such a name
$orderby = $orders[$key]; //if not, first one will be set automatically. smart enuf :)
the same approach should be used for the direction, although the code would be a bit simpler
$direction = _GET['direction'] == 'DESC' ? 'DESC' : 'ASC';
having gottent these two variables this way will make them 100% safe
$query = "SELECT * FROM `table` ORDER BY $orderby $direction"; // sound and 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:
$conn->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );
And parameters can be kept in execute():
$conn->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );
$stmt = $pdo->prepare('SELECT * FROM table LIMIT ?, ?');
$stmt->execute([$offset, $limit]);
$data = $stmt->fetchAll();
Another way would be to bind these variables explicitly while setting the proper param type:
$stmt = $pdo->prepare('SELECT * FROM table LIMIT ?, ?');
$stmt->bindParam(1, $offset,PDO::PARAM_INT);
$stmt->bindParam(2, $limit,PDO::PARAM_INT);
$stmt->execute();
$data = $stmt->fetchAll();
In the past, there was one peculiar thing about PDO::PARAM_INT: it didn't enforce the type casting. Thus, using it on a number that has a string type was causing the aforementioned error:
$stmt = $pdo->prepare("SELECT 1 LIMIT ?");
$stmt->bindValue(1, "1", PDO::PARAM_INT);
$stmt->execute();
But it was fixed in current PHP versions.
Transactions
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 transactioncommit()to commit onerollback()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
try {
$pdo->beginTransaction();
$stmt = $pdo->prepare("INSERT INTO users (name) VALUES (?)");
foreach (['Joe','Ben'] as $name)
{
$stmt->execute([$name]);
}
$pdo->commit();
}catch (\Throwable $e){
$pdo->rollback();
throw $e;
}
Please note the following important things:
- PDO error reporting mode should be set to
PDO::ERRMODE_EXCEPTION - you have catch an
Exception, notPDOException, 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):
$stmt = $pdo->query("CALL bar()");
$data = $stmt->fetchAll();
$stmt->nextRowset();
While for the stored procedures returning many result sets the behavior will be the same as with multiple queries execution:
$stmt = $pdo->prepare("CALL foo()");
$stmt->execute();
do {
$data = $stmt->fetchAll();
var_dump($data);
} while ($stmt->nextRowset() && $stmt->columnCount());
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 OUT and 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
$stmt = $pdo->prepare("EXEC stored_procedure ? ?");
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 PDOStatement::nextRowset():
$stmt = $pdo->prepare("SELECT ?;SELECT ?");
$stmt->execute([1,2]);
do {
$data = $stmt->fetchAll();
var_dump($data);
} while ($stmt->nextRowset());
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.
Unlike 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 turnedONby default.
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:
$stmt = $pdo->prepare("SELECT * FROM t WHERE foo LIKE :search OR bar LIKE :search");
$stmt->execute(['search'] => "%$search%");`
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:
$stmt = $pdo->prepare("SHOW TABLES LIKE ?");
$stmt->execute(["%$name%"]);
var_dump($stmt->fetchAll());
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 memory_get_usage() and memory_limit.
But with mysqlnd things got changed, and the resultset returned by the buffered query will be count towards both memory_get_usage() and memory_limit, no matter which way you choose to get the result:
$pdo->query("create temporary table pdo_memtest (i int)");
$pdo->query("insert into pdo_memtest values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)");
$sql = "SELECT REPEAT(' ', 1024 * 1024) as big FROM pdo_memtest";
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, FALSE);
$mem = memory_get_usage();
$stmt = $pdo->query($sql);
while($row = $stmt->fetch());
echo "Memory used: ".round((memory_get_usage() - $mem) / 1024 / 1024, 2)."M\n";
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, TRUE);
$mem = memory_get_usage();
$stmt = $pdo->query($sql);
while($row = $stmt->fetch());
echo "Memory used: ".round((memory_get_usage() - $mem) / 1024 / 1024, 2)."M\n";
will give you
Memory used: 1M
Memory used: 20.04M
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 PDO::MYSQL_ATTR_USE_BUFFERED_QUERY to FALSE.
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).
Related articles:
- PDO Fetch Modes
- Fetching objects with PDO
- Simple yet efficient PDO wrapper
- An SQL injection against which prepared statements won't help
- A fair comparison of mysqli vs. PDO
- Authenticating a user using PDO and password_verify()
- Your first database wrapper's childhood diseases
- MCVE or How to debug database interactions with PDO
- A cargo cult prepared statement
- Whitelisting helper function
Comments:
If you work mostly with strings, I've found setting PDO::ATTR_STRINGIFY_FETCHES to true handy. This, in combination with the badly named PDO::ATTR_ORACLE_NULLS, will make everything a string, including empty values.
Fantastic articles, thank you very much for putting all this effort into them.
You only have silver on SQL-INJECTION. No one has gold.
Reply:
Hello Myles
I had it but was stripped of it when the topic was moved to "community wiki". Quite a dick move and SO stopped doing such things since then but what's done is done. Yet it's a hard-earned badge is mine.
In your wonderful phpdelusions.net/pdo, you state that the types of results will always be string. As of PHP 8.3, I see that, even with emulation mode turned on, I can get int values back from a query. (E.g. is_int() says that the result is REALLY an int.)
Reply:
Hello Charles!
Thank you for pointing this out! Indeed I missed that change. Now I made it up to date.
Can you get someone that speaks english natively to proof read this and fix the errors?
I'm just interested in the opinion of a specialist that it is possible to write secure code without pdo
Thank you, I have done that and of course it works. I am always wary of disabling any feature in a program in case there's a downside. But in this case I can't see one. FWIW I am now using LIMIT in preference to having WHERE select between two dates 'x' days apart because STRTOTIME has been deprecated (for deletion in PHP9) and adding 'x' days to a date is more difficult than it was. Great tutorial, I'm a repeating visitor. Thank you.
I have runi into the string/integer problem with the LIMIT clause. If I resort to explicitly binding the params It looks as if I have to explicitly bind them all, not just the LIMIT integer. That's OK if there are only a few (I've got five), but is there any solution (other then turning emulation mode off) when there are many more? It could become really tedious, all for the one parameter.
Reply:
Hello Tim!
Just disable emulation mode as it shown in this article and you can have LIMIT parameters in execute!
This guy should write a book. The content is so good!
Thank you !!!!!!
I agree with you. I'm just wondering why it died instead of returning an empty result set. I expected an empty result in this case.
Thanks for your help. This site is one of the most useful I've found about any technical topic.
My processing loop can handle an empty result set. I was expecting the die() to cover an actual error, not an empty result set. I use the message parameter to die as an indication of where the error occurred, if one should happen. I did not expect it to die on an empty result set. Is my understanding about the die function incorrect?
I have a code sequence below where $rqry is a valid query:
When the result set is empty, this statement dies. Should it not just return an empty result set? The PHP error_log does not have any entries for this event, so I do not think there was any actual error - just an empty result set.
Reply:
Hello Tom!
This statement shouldn't die. It should just return an empty result set. So it should be just this:
I have this code that works perfectly in Sequel Pro for Mac.
SET @a = 202401;
UPDATE invoice SET InvoiceNumber = @a:=@a+1 WHERE id > 798;
But, I don't understand how to make it working with php and pdo
Reply:
JUST write your second statement as is
I can't seem to make AND work. See my statement below:
The result keeps getting all of them instead of either one of the enclosed parameters. I want both conditions inside the parentheses be satisfied before it gets the data but it gets the data anyway even without satisfying the AND required. How do you go about this.
A client has an Azure RHEL server and an Azure Database for MySQL service for our application. They use Active Directory for user authentication. They would like to have the MySQL user be authenticated against AD rather than be a 'normal' mysql user/password connection. We want to have our PHP application code that uses PDO do the required authentication. Is this supported? I would like to have a Drupal application that uses PDO to connect to MySQL
Oh thank you, thank you, thank you. How silly of me. I've spent hours looking at this there you are, Fresh guru eyes you., Thank you heaps. I'll implement the changes you suggested.
I thought I was doing it right but maybe I'm not. I'm really not a gun programmer so would really appreciate your advice. This is what I'm now doing:
but I'm getting that previously mentioned error
Reply:
Well, now you put me in a fix. Definitely there must be an error...
But wait, I got it. There is NO error in the query, just in the code:
See the difference? :)
In order to let PHP help you in this situation, set error_reporting to E_ALL. Had it been set this, PHP would have told you that $stmtU variable is not set, directing your attention at the variable name.
Right now your PHP is configured to hide some errors from you which is a very bad practice. Always configure PHP to report all errors.
On a side note, you should remove that try - catch stuff from here, leaving only
All this stuff is explained in the article I wrote especially for "not a gun programmers", https://phpdelusions.net/basic_principles_of_web_programming
Consider checking it out!
In the section about transactions, you say that you should write
As I understand, that means that you only can catch exceptions and not errors. To catch both, you write
instead. So isn't it better to catch Throwable instead of Exception?
Reply:
Hello!
Good point, thank you! Fixed right away
Thanks for the reply! I was hoping you would know if what I'm attempting is even possible! :) I'll be sure to share if I ever get it working!
Hi, I just discovered this site today, so first and foremost, thank you for all your work here!
I have a project at work where I've been tasked to upgrade the company's Intranet, which, among other things, connects to an application created in Visual FoxPro 20+ years ago, which was around the last time the Intranet was updated. Additionally, this application is on another network server.
The webserver is still on PHP 4.3. I've been asked to move it all to a Windows 11 machine to be used as the new webserver, as well as to update PHP, Apache, etc., to more current versions.
To say I've been struggling with this would be an understatement! I do have copies of the now-extinct VFPODBC driver (Microsoft's website runs you around in circles looking for it). I've tried PDO, but to be honest, that was before I found this site, so I most likely did it very wrong. I've also been trying ADODB, which is what the current PHP 4-fueled site uses, but I continually get errors insisting that I'm trying to use a 64-bit DSN to connect to a 32-bit application (the FoxPro app), even though I created the DSN in 32-bit ODBC Administrator. I don't expect you to solve all of this for me, but I was hoping you could point me in the right direction!
Sadly, I do not have the option of replacing the FoxPro application or any other part of this other than the webserver software.
Reply:
Hello Jim!
Sadly, I have absolutely no experience with either FoxPro or ODBC, so I have no idea what to suggest.
Hey, I'm interested to know what are your thoughts regarding FETCH_CLASS and deprecation of dynamic properties in PHP 8.2? I often join two tables then create one object inside the other, for example:
But in PHP 8.2 this gives deprecation notices unless you also add
public $a_id;and so on to the Book class. I don't see an easy way around it, besides using Laravel's inefficient system of separate queries for authors.Reply:
Hello Scott!
To be honest, after some trial and error, I don't find this part of PDO functionality any useful anymore. It's too opposite to the regular approach. So I just gave up the idea and settled with a regular loop that uses a regular fetch like this
So it doesn't make me change the Book class only to make it usable with PDO.
Regarding your question, if it works for you - why not? Though it seems rather a dirty approach, with all Author's properties filled into the book. So in your place I would create a distinct mapper method with a loop from above, and make it fill both classes the regular way.
You can also call PDO::ATTR_FETCH_TABLE_NAMES for help:
so it will return columns of each table in a separate array.
Hello, I have a query regarding PDO and MySQLi. I am creating a project in PHP and I have used MySQLi for now. I would like to use PDO after reading its advantage over MySQLi. What would you recommend? Is there a way to convert to PDO easily?
Reply:
Hello Anjana!
No, there is no way to convert to PDO easily
However, nowadays mysqli is all right. Either use it with PHP 8.2 where you can send variables into execute just like with PDO, or use the mysqli helper function from this site
Just a sincer thank you for all of these unvaluable explanantion !
starting from this line in the pdo tutorial all the text below is blurred and impossible to read, is this on purpose or is just me having this problem?
I've recently found myself having to create an API layer to query DB2 for IBM iSeries without any kind of ORM (terrible I know). This guide has been invaluable.
Hmmm have to admit this is the best PDO drilldown I've seen. However with the new updates, especially with PHP 8.2's changes to MySQLi's prepare and bind -- would be neat if were as detailed.
With the mysqlnd native driver installed; MySQLi can handle aysync prepared statements. With latest PHP constantly running service processes and connection pools are vastly simpler, complimenting such features. Hopefully PDO will catch up -- or at this rate it may eventually get merged.
Can you please email me this is to the administrator or owner of the site
I do not consent to any data exporting or exploitation of my life or any media content whatsoever please stop this bullshit
Hello would you mind stating which blog platform you're using? I'm looking to start my own blog soon but I'm having a difficult time choosing between BlogEngine/Wordpress/B2evolution and Drupal. The reason I ask is because your design seems different then most blogs and I'm looking for something completely unique. P.S Apologies for getting off-topic but I had to ask!
Reply:
Hello.
This site is not a blog, and there is no platform. Unfortunately, I am not well versed in blog platforms and cannot suggest anything.
Usually I do not learn article on blogs, however I wish to say that this write-up very pressured me to try and do it! Your writing style has been amazed me. Thank you, very great post.
So I have been self-teaching myself MySQL back in the day of 2011. I should have moved with the flow to MySQLi, but I didn't and stuck with the outdated stuff. I was scared to advance myself. Now I want to get back into coding for making a website for a GTA 5 RP server with a CAD and my old knowledge of MySQL won't work. So I am trying to learn PDO some of it comes easy, but other stuff I feel lost and no one really tries to dumb it down so I can understand what I am doing wrong.
So I am trying to make a rank structure for a government / Military for my server, I am trying to do an INNER JOIN with one table in my database with another. Where instead of it saying rank 15 it will join with my rank table and say Captain. Since on my members side it rank 15, but on my rank table 15 is Captain or whatever name I put there. Would really appreciate for any help and making it simpler to understand.
can anyone convert this part of the article to caveman terms?
Everyone knows that PDO offers 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.
does this mean that LAMP developers are better off sticking with mysqli? thanks!
How are people making such nice text sections of code and plain text..Mine look terrible and I feel bad providing such crap.
Reply:
To make the code properly formatted have at least four spaces from the left and a blank line before and after the code block
I'm trying to emulate your blog using stored procedures. Two tables...comments & reply...parent child.
This all prints out in the html page, but not in the correct iterative manner. I'm missing some hidden understanding using stored procedures and looping.
Reply:
Hello James.
Just get rid of that for() loop. Instead, simply do $commentId = $row['commentId'];
I love your site, just begining to read. Wanna suggest to encode your files with utf-8, so the (c) sign is displayed correctly instead of the � I see.
sincerely, greetings from berlin
How do I bind an array to a mysql column of type SET. The use case is a simple column holding roles for users like SET(user,admin). In PHP this is an array. Whenever I try to bind such a param and execute, it returns Database error: [01000] ["01000",1265,"Data truncated for column 'roles' at row 1"].
Hello. Given the incredible resources you have provided in this resource area as a relates to PDO and Mysqli, I would be curious to hear your opinion of PHP cURL. Is this better in any way? Thank you.
Reply:
Hello Neil.
Curl has absolutely irrelevant to PDO, mysqli or databases at all. It's an HTML client, kind of a "browser" that can make requests to HTTP resources. It's a standard library, which is used almost everywhere. So it's not "better" than PDO or mysqli as it's incomparable matters, but it's a default HTTP client for PHP.
Typos: A neat helper function that returns value of the singe field of returned row. singe -> single
Note that first column selected have to be unique have -> has
indexes will be unique values from the first columns columns -> column
For this section of your article: $stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email AND status=:status'); $stmt->execute(['email' => $email, 'status' => $status]);
should perhaps there be colons in your execute statement? $stmt->execute([':email' => $email, ':status' => $status]);
The issue is that I have <?php echo $data['table1']; ?> and have tried <?php echo $data1['table2']; ?> on the same page. This just gave me errors - something like $data1 not defined. So, how do I have two different tables on the same page?
Reply:
It's hard to tell what your issue is.
As a rule, as long as you have a line
you nave a $data1 variable at your disposal.
Hence your problem is not PDO related but it's the composition of the page. Make sure you don't have any issue related to variable scope.
I have a web page with a working menu and links that are used for the Contact page etc. There are no issues with the display of these pages. However, I have a footer menu and want to have the same thing happen as the top menu. The pages will be in a different table so I need to know how to get the data from the second data table on the same page as the top menu.
The top menu code:
If it helps I use pdo = new pdo( etc).
I read something about joining, but it looked complicated, so if you provide help with joining, please make it basic. As an alternative, can I use two separate SELECT statements? One of which will be <?php ... ?> within the footer.
Reply:
Hello Stephen!
I am not sure what your problem is. It's hard to understand without seeing actual tables what is this "second table", how it's related to the first table and even why there is a second table at all.
But in general, given you have another table, why don't you just query this table, the same way you did for the first one?
By the way, as it's shown in the article, this code could be written in just two lines
Thanks to you I could graduate from university haha It would be great if you add some author information so we can give you credits in researches. Thank you so much for the tutorial :'3
Hi,
There is lot of PDO on this page, where i can find PDO such file to download into to workflow to get started?
Reply:
Hello!
The good thing, you don't need to download PDO, it's already a part of PHP. In case it is not available, you need to tick some checkbox in your server panel, or uncomment PDO in php.ini if you installed PHP manually
The SELECT keyword is the operating keyword in any search engine script. Robust searches are often done using the Match() Against () Construct. PHP PDO execute() method requires an array for parameters. But, the Match() Against() requires a string expression ! If a search string is multi-worded, how do you explode them into an array and still satisfy the String requirement in the Against($string, IN QUERY EXPANSION MODE). Even moreso, when sometimes the search string "proper PDO" for example should find exact matches?
Reply:
Hello Adolf!
That's a very good question which, however, has very simple to answer.
A MATCH AGAINST query is no different from any other query in regard of prepared statements. Simply replace $string with a placeholder and then run your query the usual way
while all keywords are going into $string. You can find some examples in this Stack Overflow question, https://stackoverflow.com/a/41796320/285587
Excellent tutorial! The best i've ever seen.
Hello. Fantastic tutorial. Brilliant suggestions (including code examples). Question(s):
Reply:
Hello Neil!
Thank you for the excellent questions.
Hope it made it clearer for you!
.
Reply:
Using password in the WHERE clause will do you no good.
Actually there is a ready made example in the corresponding section, https://phpdelusions.net/pdo_examples/password_hash
Hello, thank you for your answer. I realized that and changed the code accordingly. But still for some reason the password_verify() function gives false. I this because of the way i have inserted the hashed password into MySql? I just copied it manually inside the database. The query works with a plain password but not with a hashed password. Kr.
Hi there i came accross this issue of empty Array () when using PDO statement. The error message says $result does not exist and the array is empty. Any idea of the problem. Thanks a lot.
Reply:
Hello Steph
The problem here is that you are verifying the password the wrong way.
to verify the password you need to fetch it and then check against password_verify()
Hi im checking for a post update when an image is changed or not
Gives me an erro message
Any idea whats going on? Thanks
Reply:
It is said pretty straightforward: the number of parameters (:something) things in your query is no the same as the number of bindValue calls below. there is one extra bindValue
If a user submits, say...
and either the username or the email (or both) are taken, what would be a correct method of handling this while conforming to the best practices described above - such that we get a notification like 'Email address is already in use', or 'Please choose another username', or 'Both the email address and username are already in use', etc.??
Why did you edit my comment removing my reply to Mike?
Hi The code $options = [PDO::ATTR... ]; fails to parse on my development server. I used $options = array(PDO::ATTR...); instead.
Reply:
Hi Aaron!
Square bracket arrays appeared in PHP 5.4 which happened nearly TEN years ago. And the current version is PHP 8. And the least supported version is PHP 7.3. May be it's about time to upgrade your development server?
A thought on using PDO with LIKE - I have hit this problem myself and mitigated it by using concatenation to join the wildcards to the incoming string. Like (for SQL Server):
or (for MySQL/MariaDB):
I'm sure there's an argument against this and I am open to receive it.
Reply:
Hi Aaron!
Nope, it's just another way to do the same.
Hi The code $options = [PDO::ATTR... ]; fails to parse on my development server. I used $options = array(PDO::ATTR...); instead.
Thoughts on using a generator function for efficient retrieval of large number of rows?
Reply:
Hello Nick!
Yes it's possible. But given PDOStatement is already iterable, this code can be made much simpler:
Is it possible to combine transactions with the exapmple of PDO Wrapper?
Reply:
Hello Carlos!
Transactions are orthogonal to a wrapper. You just use them, that's all. A wrapper just helps to execute query in one go, but the rest is just vanilla PDO, use it as usual.
I have found your PDO tutorial so good. Is it available in book / PDF form?
Thank you so much for your hard work.
Reply:
Hello David!
No, there is no PDF. I should think of making it on learnpub...
I’d like to know if you can post a sample PHP Script that explains how to use the LIKE With PDO to search in a database for keywords. The example I’ve seen on your site explains a lot but doesn’t explain how to retrieve the $data for viewing.
Hi. Best treatment ever! A note: PDO::ATTR_EMULATE_PREPARES => true, //if set to false cannot SELECT from views!
Please update your website especially PDO parts because I cannot understand what you are trying to mention. Terrible grammar. No fency, I wanted to say it because the information you stated are very valuable.
Thanks.
Hello, I've been reading the PDO article with interest. Since in the mission of this site article states that grammatical corrections are welcome I would like to report a small one I noticed. In the "Running SELECT INSERT, UPDATE, or DELETE statements" section of the PDO article the third sentence "To PDO they all the same" should be "To PDO they're all the same". I look forward to continuing my reading experience with glee.
Hello, i got till moment when i do not know if it is secure!
If SQL injection is inserted into myTableTwo via safe PDO query will it make my query below SQL injection open? Is the query below safe?
$mysfield returns
SHOW COLUMNS FROM myTablemyTable columns match myTableTwo columns
Hi, I think I found a way which suits my particular scenario: I collect 3 php variables: $class, $studentnum and $weeknr.
I don't know what an sql injection attack looks like, but it must be some text like:
I set
As I see it, any injection attack would not reach execution. I check $studentnum to see if it is a 10 digit number and $weeknr to see if it is in the array: $weeks = array(Week1, .... , Week18)
My webpage only gets a couple of hundred visitors, students, a week, but I still wouldn't like to get "injected". Do you think this will stop it??
Hello!
Nice article!
I have an error with the stored procedures that returns more than a resultset. The first query on the sp is ok but the second uses an unknown field (a typo error). The PDO::nextRowset() returns false as expected. But I don't have any way to know which is the error that makes the second resultset is empty since PDO::errorInfo() returns "00000" because the first resultset is ok.
How can I catch the error on the second resultset?
This is the code:
And the connection params:
Thank you!
Reply:
Hello Federico
According to my experience, PDO should throw an exception when you're calling PDO::nextRowset() on the query with error.
I have a html form with options to search a mysql database.
This then goes to a .php page with a query like this
What I would like to do is run it in pdo like this
How do I set it up so column in the $tmmt line in equal to $column in the $sql line.
Thanks
Reply:
Hello Rob. It is shown in the article above, in ht section related to LIKE operator
Thanks for your advice! I read somewhere that PDO could handle native and non-native variables, so I thought maybe prepare can take the php $variables, like this:
Is it really that simple? What if $class was actually some nasty sql injection code??
I did this, it seems to work, no errors and I get the result I expect.
Reply:
Hello Pedro.
No, it is not that simple. Beside calling prepare() you have to replace all data variables in the query with placeholders, while variables themselves should be sent to execute.
By the way, I don't understand what "native and non-native variables" are. Never heard of them
As an amateur, I find PHP and SQL difficult. Here, $class is the name of a class of students, like 19BE1, $weeknr is the school week, like Week1 and $studentnum is the student's student number, like 1234567890.
If I define a query like this (this does what I want):
Do I need a prepare statement to avoid sql injection before:
Does query() need to be 'prepared'?
Thanks for any tips or advice!
Pedro
Reply:
Hello Pedro!
Yes, your query needs to be prepared and data variables should be replaced by placeholders, like it is shown in the article.
Also, you should really really change the database design:
So variables $class and $weeknr should be in the WHERE part and should be replaced with a placeholder, as well as the $studentnum.
converting my mapping site to PDO and it works great until I try and retrieve 10,000 records.
Not sure how to write a for loop for pdo. Found this example and tried it, but I'm missing something and was wondering if you had any recommendations.
Reply:
Hello Virgil!
Technically it should work.
If it doesn't then it would be a good idea to provide some description of the result you get.
Besides, you are using PDO prepared statement entirely wrong. Please read the article above.
Thank you for the tutorial. It helps a lot!
I want to comment on the section on the rowCount() method: You are writing that one scarcely needs it because normally you only need to know if there is 0 or more lines. But I use it a lot, especially when I need to know if I have to use plural or singular in a text. E.g. "You have 1 new message" or "2 new messages". For that it is not sufficient to just know if there is date or not. You present an alternative with getting it directly from the database and state that this is a better way to do it. Could you please tell why? For performance reasons?
Reply:
Hello Kerstin!
Thank you for a good question. Yes, exactly, it's for the performance reasons. As a rule of thumb, one should always select the exact information they need from a database:
Please write a book called PHP Idiots. I will buy two copies. Thank you!
Is there a way to get your blogs as one "book"/pdf. I am more comfortable reading on paper than on line.
Thanks Ken
Reply:
Hello Ken!
No, there is no PDF version but as far as I know there are many services that convert a web page to PDF. I never used it myself though but I hope the result would be satisfactory
I am building a small framework of my own and I am not creating an ORM, But I have a DAO per entity/table which manages the relations superficially,
I read about multi queries being exactly the same as one by one in every way,
So what is the best way to do multiple insert, update,delete in one hit? If I did all these in one single query, would it not be faster?
If not, then maybe its easier to just do one query separately?
Reply:
Hello!
No, you have read it wrong. Doing a multi query is NOT the same as doing one.
And no, it is not faster either.
The proper way to run multiple DML queries is to use a transaction and a prepared statement:
You can get an example here: https://phpdelusions.net/pdo_examples/insert#multiple
Hello! I have a simple table with a massive number of records, and this is causing memory and timeout issues when I use fetchAll(). I think using a cursor would solve them, but I never used them. May you please give me an advice or point me towards a good source of examples? Thanks in advance Ivano
Reply:
Hello Ivano!
You can find the example here: https://phpdelusions.net/pdo_examples/select#multiple
Also, consider using unbuffered query, as explained here: https://phpdelusions.net/pdo#mysqlnd
However, in the first place you should consider to process lesser amounts of data. You can use WHERE or LIMIT clauses to limit the amount of rows selected. Also, when applicable, you can ask a database to perform all calculations and return the result in a single row.
i have a db field that i want to use to track intervals. i want to push completed intervals onto the db field when they are completed. to wit:
the values will never decrement (and order doesn't really matter, if that's a factor), so i'm only looking for a way to
UPDATEthe field, but i have no idea how to do that becauseUPDATE tbl SET ...just overwrites the existing contents. i looked intoCONCAT, but that works with variables the user provides, not with existing data AND additional user data. if i were to write some PSEUDO code, it might look like this:so. how would i structure my query for PDO? would there be any issues with validation? can you help me out here? thanks in advance for your time.
Reply:
Hello Ian!
Although CONCAT certainly works with variables the user provides, I would strongly suggest to normalize your table structure instead.
To normalize in the database world means to eliminate questions like this. Intervals should be really really a separate table where you add intervals on the distinct rows.
You can then even get the same string in your SELECT query using a GROUP_CONCAT() function, id needed.
How does inserting file into mysql table work from .csv file. I have a pdo db. Thanks.
@Ingus, did you run same query in mysql console by hands?
Hello there! Could you help me understand why this query is not showing any row?
Hi - Thank you for the excellent site and invaluable information! I‘m following your advice in using prepared statements to lessen any risk on a site I’m building. I would like to use a prepared PDO statement for an INSERT...ON DUPLICATE KEY and an example would be very much appreciated. Thank you.
I see not examples of an UPDATE stored procedure in PDO. I have tried to make this work, but have failed. I have not problem with UPDATE PDO, but not calling a sproc for an update. Is this possible or is it nonsensical to try and use?
How can I create two forms on one page that submits form data in two different scripts; Let's say a login page and a registration page that submits data on two different scripts, one for login and another for registration
Hey, great article, well done. Im not sure if i missed it but how do you close the connection and statement? In mysql you close the database connection and statement, is that something you do with PDO or did i miss something.
ok, what about this:
WRT:
if $arr is empty, you still get a '?', I use:
where if $arr is empty, $in is also empty
Reply:
If array is empty then empty in() clause will cause an SQL error, so it has to be checked before the query execution.
I like to use:
Reply:
Hello Sergio!
With this code you've got two problems:
I've got an article that covers both problems, please check it out: https://phpdelusions.net/pdo_examples/dynamical_update
Hi Master, for the purpose of security, on a machine that connects to the dbs only locally, does it make sense to read the users dbs with a connection other than the one used to read other dbs ?? now therefore I make 2 types of different connections ( $conn1 & $conn2 ) through 2 db.inc.php that I carry inside my functions through : global $conn1, $conn2... now I made some changes and managing 2 different types of connections became really problematic, and I think to understand the bad pratice of call the connections with global... so I was wondering if it made sense to keep them separate or go MUCH simpler keeping one for all without having problem recalling one connection only in my functions with global $conn... suggestions ? always thanks Gio
Reply:
Hello Gio!
I don't see any reason to have an extra connections. As a rule, every website is using just a single connection. I have a feeling that anyone who recommends an extra connection for the extra security never used it in the real life.
I'm trying to make my PDO code portable. With mysqli you can have you database, host, username/password in a db.inc or db.php then include it when you start your code. I am not finding that I'm able to do that. For example:
db.inc/db.php
On the php file to do the action:
Then call your variables and run the mysqli connection then the query. If I try that in PDO I get errors. Is this just not done? Or there's a proper way to have 1 file with my DSN, username, password and database name?
Thank You in advance,
Reply:
Hello John!
Of course it can be done.
The error is not some imminent but probably some mistake in your implementation. A few problems I can spot right away:
If you still encounter some errors, try to google them - usually there are many answers on Stack Overflow explainig the problem.
Ok, first let me just say, I am not asking you to explain to me how to do this, just asking if it is possible. I asked if anyone could point me in the right direction on how to do it on Stack Exchange and was told that the answer to that question could fill books lol. So basically I just want to know if it is possible to do, and if it is, I will roll up my sleeves and do a lot of reading.
Basically we currently have a customer invoicing program made in Access, forms built in Access and using Access database. There are 2 main parts, the customer info and the invoice itself. It looks like there is a customer table and an invoice table. When an invoice is made it links to the customer by the incremental ID of the invoice. Basically I would like to make a web form that has all the information on one page. Top has customer info, and if it is a new customer the info can put inputted and submitted to database, if it is a return customer then the customer can be looked up by customer ID, license number, or lastname, and the rest of the info autofilled from the record. Then the invoice can be filled out and submitted, linking the invoice to the customer. In the end an invoice can be saved, emailed, or printed. Does that sound like something that is possible to make? If I did not do a good job explaining I can send you screenshots of what we are currently using and of what I would like the new one to look like. Thank you in advance for any help you can offer.
Reply:
Hello Charles!
Well, everything is doable, the question is the amount of effort you are willing to spend.
The easiest way would be to ditch everything you have now and rebuild the system using PHP HTML and SQLite. For this stack the task is of low to moderate complexity, can be done in a month if you have no prior experience.
If you still need the connection to your existing Access database, it would be tricky to achieve and clumsy to maintain. And I've got no solution from top off my head right now.
Replaying to your last reply (Ingus, 10.01.20 11:16): Thanks for fetch() fix!
But what if i had like this:
How to get same result with PDO?
Reply:
I can't even think where you would need such a code but you can fetch all rows into array using fetchAll() and then count them
But wait. If you need only count, but not the data itself then select only count.
I m having issue converting mysqli to PDO
My code:
I had for mysqli:
The question is how to make same effect with count returned lines with PDO?
Reply:
Hello Ingus!
I am not sure what are you doing here but at least I can tell that fetchColumn() is not suitable for such a query, because you are selecting two columns, not one.
Change the last line to
and then you can use this $row variable instead of mysqli_num_rows() because not empty $row means your query returned something:
Here you can use status, $row['date'] and $row['status']. If $date above is the date from $row then just write $row['date'] instead of $date.
Hello, first of all thanks for such a good tutorial! It teached me allot!
I m about to make new script and i m going to use PDO! Clients that will use this script have different wishes about database type. .. One want SQL other want MySQL so i have question! Can i make script that allows both database structures and if database type would be needed to change all i had to do would be change connection string?
Thanks in advance!
Reply:
Hello Ingus,
As a rule, it is not possible because of different SQL flavors. For example in SQL Server there is no LIMIT operator, but there is TOP instead and so on.
It means you have to test all your queries for all servers.
For a a true database-agnostic application look for some DBAL, such as Doctrine or Eloquent.
Just wanted to say THANK YOU for this! Still working through it, but the explanations for everything is so much better than YouTube videos and other tuts that just give code for one way that works. I feel like I'm actually learning this stuff (and the RIGHT way), and not just copying code.
Thanks again!
Reply:
Hello Steve!
Thank you for your feedback. Such a response is a thing that keeps this site going.
Please keep in mind that best gratitude is questions. Do not hesitate to ask for clarification or an example. It will help me to make this site better.
I'm having some issues with a particular file that I'm updating to pdo and I'm suspicious that it's related to the fact that some of the DB columns are bigint. Could you please elaborate on the bigint idiosyncrasies you mentioned in your main article and how to deal with them. I'm also looking for a place to get help with my pdo issues but I've gotten bad response from asking questions on SO and I'm not sure where else might be a good source for help. Any suggestions would be greatly appreciated!
How does this work with fulltext searches? I'm using mysql and PHP, and I've been trying to create prepared statements, but cannot get it to work:
Thanks! Todd
I'm extremely confused.
What the heck do you mean by "here is an example for mysql"? Do I need to use this code if I'm using a msql database? What does most of this code mean?
Reply:
Hello Ted!
Sorry for the late answer.
I am not sure what do you mean with "msql". As far as I know, PDO doesn't support such a database. May be it's mysql or MS SQL server? For the latter you will need different connection code, see https://www.php.net/manual/en/ref.pdo-dblib.php
And what does this code mean is fully explained in another article, https://phpdelusions.net/pdo_examples/connect_to_mysql
Hiya thanks for your knowledge on the PDO its helping me massively understand PDO better but I am having a issue.
Reply:
Hello Ricardo.
In order to have the code formatter you must pad it with 4 spaces from the left and an empty line from the top.
So what your issue is?
First, thanks for your insight into PDO and the creation of a wrapper. My wrapper wasn't too far off but was able to make some adjustments thanks to the information you have shared. I have also gone through the delusions article as well and it has also helped me reexamine parts of my code.
Secondly, I have a site running on PHP 5.4.45 with a custom created PDO wrapper as well as other custom classes I created to fulfill my needs. It has been running near flawlessly (except for some of hand PHP errors) for the past 4 or so years this way. Now however is the time to upgrade the server. After upgrading the server to 5.6 I started to experience major issues right away with MySQL. It seems that there was a major shift in code handling by PHP from 5.4 to 5.6 with the way it is handling DB connections. The server is now hating my code and opening way too many connections to MySQL and crashing the server. Do you know what change they made between these two versions that I may be overlooking?
Thanks in advance.
Reply:
Hello Justin!
I cannot think of any reason related to PHP version. It should work exactly as before.
However, there are two most frequent reasons for too many connections.
First of all make sure you are no using a persistent connection.
Then make sure your wrapper connects only once during the script executions. Most likely it's the case.
Not a question, but a thank you and praise for your work. I cannot tell you how much simpler your code examples have made my attempts at being a programmer. You must have a paypal account or a manner to make contributions. If not you should. Once agains thank you.
Please advise whether below method is correct, if I don't set to PDO::ATTR_PERSISTENT => TRUE, I don't know why it's not rolling back.
Hi, Thanks for your replay.
Transactions is not rolling back on mysql query failure, if PDO::ATTR_PERSISTENT => TRUE is not used.
Please advise the solution.
Reply:
Hello Mohamed
The PDO::ATTR_PERSISTENT is not relevant to transactions. Most likely your transaction is not rolled back because the table in the database does not support them. Make sure the table is created with InnoDB engine. And also check out other prerequisites noted in the corresponding section: https://phpdelusions.net/pdo#transactions
Finding valuable sources on the internet is never easy but I am glad I found this one. Great article on PHP and PDO, thank you!
Please advise on usage on below, whether to use or not in transanctions
PDO::ATTR_PERSISTENT => TRUE,
Reply:
Hello Mohamed!
The answer is very simple: just never use PDO::ATTR_PERSISTENT => TRUE unless you have a very skilled dedicated server admin who would tell you this option is necessary and who would then configure your database server, HTTP server and PHP properly.
In any other case this option will do you much more harm than good. Especially with transactions, as such an option will result in transactions extended between different scripts execution with unpredictable results.
Hi, I managed to solve my issue sent earlier today by replacing the while loop with [ $user = $stmt->fetch(); ] which i grabbed from your site - thank you :)
Reply:
Hello Elizabeth,
Thank you very much, such feedback means a lot!
Hi, very good article! Thanx
Hi, I'd like to use PDO to access a reviews database with mysql. I'm completely new to PDO and am upgrading from PHP5 to PHP7. The query I am having trouble with is:
Could you assist?
Reply:
Hello Lynne!
Three things:
In case it doesn't help, please get back with a more detailed description what particular trouble you are having.
What does PDO stand for? PHP Data Objects? Always give a definition for an acronym, if it is one.
}
}
function select($sql, $cond=null){ $result = false; try { $this->stmt = $this->pdo->prepare($sql); $this->stmt->execute($cond); $result = $this->stmt->fetchAll(); } catch (Exception $ex) { die($ex->getMessage()); } $this->stmt = null; return $result; } } ?>
You are great! I'm quite new to PHP and I'm renewing old code of a former colleague with PDO and I found your example with the IN clause very helpful. Many many thanks Here I have an example where the placeholder after the IN clause could be a single value or a range of values. I wanted to share. I hope it is useful for others.
I noticed something weird about pdo ssl, if you misspell the key name values such as PDO::MYSQL_ATTR_SSL_KEY => 'mispeled key name' it still works which is kinda strange.
I have issue with single quote ' I can't see the word like Don't or won't instead of don't I can see "don" missing apostrophe and t how do I fix it? Thank you
Reply:
Hello Joe,
This is a very common problem. Just look at the HTML source of your page and you'll find why. When yo have "don't" in the value, it becomes like this
see - it closes the quote.
To avoid this, you must apply
htmlspecialchars()withENT_QUOTESparameter to all your variables.Hello Expert
To prevent my apps from sql injection, I use str_replace for any input parameter ($kiki) as below :
then :
is it still vulnerable?, can you help how to inject with above sanitation ? regards
Reply:
Hello.
Yes it is vulnerable. You can find many answers on Stack Overflow that explain how to inject with above sanitation.
You should use prepared statements instead of this.
Hi, i am currently using php framework Yii 2. Yii 3 is in development, and i am planning to use that when it is release. Can you verify if they use proper PDO? If Yii doesnt use proper PDO, what php framework do you suggest?
Hi. This is an excellent web site - thanks!
I have read page after page about retrieving multiple rows and multiple row sets, but I can't find anywhere which explains the mechanics of how multiple row sets work. My question is... When calling a stored procedure via PDO which returns multiple row sets, are all the row sets returned to PDO at once, or is there subsequent communication between the server and PDO every time I make a call to GetNextRowSet? Thanks in advance. Dave.
Reply:
Hello Dave!
That's a very good question, and the answer is the option 2: subsequent communication between the server and PDO.
That's actually the whole point, as each resultset is completely separated from others, bearing its own metadata such as error information number if returned rows, etc.
After all, you can tell that other resultsets are waiting on the server by trying to execute another query while not finishing with getting them all - the error message will say that you cannot execute any queries while other results are waiting.
Hope it is clearer for you now! Feel free to to ask if you still have questions though.
hi, i have problem with prepare query and the asc/desc parameter, i cant bind in the execute, i tried to catcatinate it before the query and dont manage to do it. what is the right way?
Please NEVER take this website down. It is EXTREMELY useful and concise. Thankyou
Please Sir, While running a query, this error message came up: Unknown Character Set! While building the DSN, I added charset=$charset to the string. And $charset = "utf8mb4" . What did I do wrong here Please?
And, when I do search and retrieve pages of data, to format display pagination, is it wrong to use PDO::rowCount() in this case too?
Reply:
Hello Nedum!
Most likely your database server is rather old and doean't support this rather new encoding. Use utf8 instead.
As of PDO::rowCount() - yes, it is gravely wrong to use it for pagination. Use SELECT COUNT(*) instead.
Great article with great details. I spent years and years on using PDO and I learnt a lot with pain and blood, but this article still had new things for me. I wish I had read that many many years ago. Thank you!
However I found a paragraph in this article that somehow I think that can be misleading for beginners:
"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."
So for Beginners, I want to rephrase this paragraph:
PDO::lastInsertId IS GUARANTEED to return correct ID of last inserted row in the script, even on concurrent environments, but it CAN BE harmfully dangerous and unsafe with seeded IDs (e.g. mysql's auto-increment or most pseudo randoms like rand() function) on concurrent environments IF you use it to calculate next row's ID. One should never calculate next row's ID based on previous row's ID in a concurrent environment. If you need next ID, just insert a new row in the table, and use PDO::lastInsertID to get that ID. That is because with seeded row IDs on concurrent environments like the web, collision IDs WILL happen and you end up in tears.
So, just to repeat it again:
Reply:
Hello Ali!
Thank you very much for such a thoughtful comment!
I wholeheartedly agree with this warning, but I feel that it is a bit off topic, as it is not directly related to PDO, but to the abuse of the auto-generated identifiers in general.
Ho be honest, given right now am not thinking of reducing the size of the article rather than enlarging it, I am reluctant to adding any additional information not directly related to PDO. Either way this comment will remain here to warn all the readers, so thank you again for it!
How can you execute trigger queries with pdo.
I am using easydb and I get the following error. I have tried with and without delimiter statements and both fail.
Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.
I am using paragonies easydb pdo wrapper. but for connection errors, it displays username and pwd. i have tried using silent mode. but, no luck. is there any other way to ensure username and pwd are not used. fyi: it uses the try catch mechanism.
Reply:
Hello!
That's a very good question.
The leak is coming from the way Factory::create() method is called.
The simplest solution would be to wrap the Factory::create() call in a try-catch, as it is used for vanilla PDO in this article:
It will take the credential details out of the error message.
This method is not very clean but it's quick and it works.
The better solution would be to change the way Factory::create() is called, so I will reach Paragonie to discuss the matter.
Hey, Thanks alot for the reply! I looked into the CSRF token and I'm definetly going to be using that for my forms. Also about the third point you made, did you mean I shouldn't be giving users the ability to input/check some box in order to include some php file? or did you have something else in mind? Also thanks for the file upload part, I'll definetly go by that advice next time I am going to be working with files.
Silver
Reply:
Choosing a file to include based on the user input is perfectly fine. I meant you should never directly put the user ipout in include (or any other function dealing with files).
Hey I'm building a web application with php/sql and also js on the user's side. I have a question regarding security, specifically about input/output sanitization. Right now I'm using PDO with prepared statements to contact my DB (SQL) and also I am using Regex for input validation both on the server side and user side. Is there anything else I should be doing to user inputs? Secondly on the output side i understand it is needed to sanitize output with htmlspecialchars() so people can't add in "evil html code/and js" is there anything else I should look into there or is that enough for a small userbase web application? I can also add that some of the data I'm working with falls under delicate user data according to GDPR. Sorry for the long question! :)
Reply:
Hello Silver!
First of all, security is not a simple matter that can be covered in a comment. There are a lot of areas to look for.
But speaking of SQL injection and XSS you are taking it right.
Other areas to look for are (you can google up the terms)
that's of course not the full list, just to elaborate a bit on your question. But again, it it not a simple matter that can be covered in a comment.
Hello! Thank you for this site! Although a lot is very clear, there is something I can't work out. So, my problem is: How can I connect my database class with my other class which has a function insert() to start a transaction with prepared statement to start a transaction for multiple inserts... Can you show me a example? My Database connection class:
Reply:
Hello Dennis!
Using the database connection is a tricky subject. There are several approaches discussed in the adjacent chapter: https://phpdelusions.net/pdo/pdo_wrapper#availability It's hard to tell with one will suit you best, but at least you should make sure that there is only one connection opened and used for all database interactions during a single script runtime. Feel free to reach back if something is still unclear.
Also, a bit irrelevant, but I noticed some issues with your DatabaseController class. I have an article on the common issues with database classes: https://phpdelusions.net/pdo/common_mistakes you may find it interesting.
hello, the table contains over a million records. 1 record per minute for several years. No wonder the treatment times were getting longer and longer! In short, I removed almost the entire beginning of the code to process a week of data and the processing time is now 1.24 seconds :) Thank you very much for opening my eyes to the problem.
You are absolutely right ! I focused on the query itself but it is actually the 2 min and max queries that are not good! Obviously the whole table is treated by the WHERE clause since $timestampmin is the timestamp of the first record of the table and $timestampmax is the last! I will look at this tomorrow because it starts to be late (and my eyes do not see much ...) thank you again for your precious help.
Here is the request
Reply:
Hello Jerome
To me, the above code makes no sense. It looks like all the records from table are selected. You are selecting all rows between min and max value in some column - so it is effectively all the rows. What's the point of it? Why bother with WHERE condition then?
In case this query returns too much rows, then indeed in will take a lot of time. Any chance you could reduce the number of rows selected?
the code
Thank you again for your help
Hello, thank you for responding quickly. After analysis of some requests it turns out that it is the extraction of the data which is extremely long. For example, the next query runs in about 1 second while data retrieval takes 11 seconds! I know I'm abusing (a bit) and optimizing queries is not part of your post but if you could give me a solution it would be really nice. An article above would be welcome and I think that newbies like me would find very valuable information.
Hello, first of all I apologize for my english (I'm French). I am a student and I am currently working for a company that would like to take over a web site developed by another student in the past ... in short, my knowledge of PHP are rudimentary and your site has taught me a lot but I ask myself a few questions about the obtimization of performance in the writing of PDO requests. For example, is it possible to optimize this query in terms of CPU utilization and RAM. The site being sullied on a Raspberry PI v2, the speed of display is also an important criterium ...
thank you for your reply
Reply:
Hello Jerome!
This question is a bit out of the scope of this article but I'll be glad to help. I think you should make sure there is an index on the timestamp field. It's hard to tell any more without knowing the details. But you could always try to check the performance by adding EXPLAIN keyword before SELECT in your query and studying its output.
Some of the " in example code are unnecessary and could be '
Please help with SQL application.
I want to
Select * from table Quotes, where one or more items exist in tableWork OrderswhereQuotes.quote = Work Orders.quoteandWork Orders.stat='OPEN'I know I could do a query on table
Work Orders, fetch column, and then feed the result into a query on tableQuotes,but I feel like the database can do this. What is the SQL to have the database make this comparison?Reply:
Hello Walter!
Yes you are right, a database can do it. The query is almost one you wrote already, you only need to add a JOIN clause
Stack Overflow ans Wikipedia has many great articles explaining JOIN, you may want to learn about joins there.
Hello, I do not see what's wrong in my request! Can you give me the solution please?
thank you so much
Reply:
Hello Khangi!
I don't see it either. Just to be sure, don't you forget to add a WHERE clause?
In any case, any problem with a PDO query would raise an error that should help you to find the problem
Great article. Helped me a lot. Thanks!
thank you very much for your super fast response! Actually, I had thought to do like that but I thought wrong that as I had no data fields in the table I could not do it ... error!
it is unfortunate that no command like BOTTOM or TOP is available under SQL, it would facilitate many things ... :) Thx
Reply:
Hello John!
I must warn you in this regard again, because if you think that using BOTTOM or TOP would facilitate many things you are probably doing something wrong.
And your UPDATE query is the example. If you want to update the "last record" than you definitely doing it wrong. To update a record you must know for certain which particular record you want to update, not just a relative position. If you can explain why do you need this update I will tell you ow to do it right
I will have another question about UPDATE. How to formulate the query to modify only the last record of the table? Of course, there are no ID fields in this table, only a primary key. thx
Reply:
Hello John.
At least for mysql, there is no way to tell "the last" record, unless you have a field designated for this purpose. So I would recommend either an auto_incremented id, or at least a datetime field that would hold the time when the row was added.
Besides, "the last" is too clumsy a description, you must know the exact key for the row you need to update. Other wise a race condition may ruin your operation
Hello, by doing some research I found your site which unfortunately did not answer to my problem. I would like to UPDATE a table looking for a particular record using WHERE but obviously my syntax should not be correct. Can you help me please ?
The request is executed but the table is not modified! Can you tell me how to implement this please? WHERE name = $data
Reply:
Hello John!
Well, the answer is written on this very page, although I admit it is not that explicit as should be:
It meand that every single variable should be substituted with a placeholder and then go into execute:
In you current form the query is not executed but rather produces an error, due to that $data veriable i the query and also a space between a colon and a placeholder name (it should be :bic, not :bic).
I fixed these issues in your code and now it should work.
i want a to select data between two dates using pdo here is the column value (12/31/2018 6:25:21 PM)
Can you teach me how to search for my question?
My question is: I did this:
However, only by luck did I try to qualify my todos table with its database mytodo and see that when I
there is anything but an empty array.
I was adding to this table, and on my sql gui I could see they were adding, and same with the sql terminal, yet the fetchAll array was empty when I neglected the
mytodo.part.If I didn't have luck, what search terms could I have entered on search engines to tell me that I need to do that prepending of the database name to my column?
I'm following these beginner PHP videos on laracast, and quite a few times copying the exact thing in the video doesn't seem to work (I'm on PHP 7.2 with MySQL8, that itself was not smooth to set up with all the troubleshooting the "caching_sha2_password" stuff), so I've been searching a lot from the beginning. Couldn't search for the right term for this problem I had and I'm scared once I progress to bigger errors, I wouldn't be able to find my way out!
Thanks
Reply:
Hello momo
I don't know if it's your case, but only prepare() is not enough. You must always execute a prepared query to get the results.
As of searching the answer, I would probably search for the basic PDO select example and follow it attentively.
However, if it works only if you add mytodo. part, it simply means that you are connection to some other database and natuarally you have to denote the database yoiu want to work with in the query.
Searching would be straightforward, something like my query works only if I add database to the table name
Hi, any compliment is superfluous. A question fi possible, I have some difficulties to run a query LIKE :
suggestions ?
Reply:
Hello Gio!
You cannot use positional and named placeholders in the same query
So it should be either
or
Just want to say thank you for your articles. They have been incredibly informative and helpful.
I'm not sure if it's possible to reply to a reply here for us regular peons, but thank you for your quick and very thorough reply (and I apologize for starting a new comment thread if it's possible to reply to my own). That was an extremely helpful response.
To be honest, I actually always saw the backticks used elsewhere and had no idea the purpose... "what an unnecessary, and ugly-looking thing to do", I thought. I come from a professional Oracle background, so MySQL is "familiar", but not second nature. It wasn't until reading this (fantastic) article that I learned what those backticks are for. Thank you!
I'll go update my code to use the backticks, just in case. And yeah, sorry, "user input" wasn't a very descriptive term to use, but I can say that yes, everything is hardcoded. I definitely would not be using any reserved words in table names, but I like the idea of just following good standards, and doing that early on enough in development (which is the case here) and sticking to them throughout any coding I do. So, off I go to fix it all up!
Thanks again. I can't stress just how much I learned from this article already.
I'm a little confused on whether I need to worry about escaping/backticking my table names in querying/DDL statements.
My table names are all constants, stored in a constants php file so that if I ever change things like schema name or something like that, I have one central place to update. So for example, I have a constant defined like so:
So in my code I may do something like:
My syntax above might be a little off.. part of that is by memory, but that's not the point of my qustion. , I'm just wondering if I open myself to SQL injection by doing the table dynamically like above, without any backticking/escaping or anything. A user has no input whatsoever into what "NS_TBL_PLAYERS" is... that all comes from "constants.php" file I have on the server. So I assume that unless they somehow gain access to that file to update it (in which case I probably have bigger worries) the above code is safe?
Thanks for your time and advice, and this article ((The only proper) PDO tutorial) is incredibly well done.
Reply:
Hello Chris!
That's a really good question.
From the SQL injection point of view, rules are simple:
So as far as I can see, your code follows the rules, therefore there is no problem.
However, from the correct SQL syntax point of view there could be a problem. Your table name could be an SQL reserved word or an incorrect syntax (contain a space or a hyphen for example) so it's much better to enclose it in backticks, just in case. So the only difference I would make is
And it should be OK.
Whereas you need to worry about escaping/backticking only if your query parts are coming from the user side (field names for example). In this case you must choose them from a whitelist - so effectively it will make them hardcoded and thus immune to injection.
I would also avoid the "user input" term as being quite vague and uncertain. A "hardcoded value" is much better to me, as you can always be certain, whether it's written in your code or not. If not - then ot hould be added to the query through a placeholder.
Hope this long and winding rant would be of any value for you! :)
Hello, thank you for this excellent articles that filled a lot of my shortcomings. unfortunately there is still a lot and if you want to fill another ... :)
I am stuck in my development because I can not use the min and max functions in a select with a limit. Visibly, 'limit' is incompatible with these 2 functions but there may be a trick ... here is my code:
I also tested with this code:
with this code I would like the min and the max of the last 10 records of my database but obviously as soon as 'limit' is used the returned values are not good! Do you have a way of doing that works please? Thank you for your help
Reply:
Hello Harachi!
Thank you for the good question.
Your queries don't work because LIMIT is indeed incompatible with the way you are using min and max. SQL operators work from left to right, so min and max are taken from the entire table ordered by timestamp, and then only one row returned gets limited to 10 rows.
The simplest solution would be to add a sub-query (not tested):
Also note that the way you are taking a single value from the query is rather peculiar, given PDO has a method for that already:
Hello, certainly off topic but I start ... Already, very good article on PDO, I learned a lot. My problem is that I have to resume a development written in C CLI that I have to adapt to the PDO. Of course I can not find any documentation. Would you be so kind as to indicate a URL or can I find this information?
for example, how to program this function?
With my thanks
Reply:
Hello Zeef!
That's a standard Mysql C API. To rewrite it to PHP/PDO, you basically need this:
note that the function is called with a parameter $pdo, which should be a valid PDO instance created before.
The function is written to the best of my C knowledge (which is very limited), so it could be wrong. Feel free to ask any questions, I'll be happy to help.
Please accept my gratitude for your kindness and willingness to take questions and sacrifice your time for me and a host of others. My question: In the Catch Block, following the $pdo instantiation, a backslash preceded the PDOException. What role is the slash performing there?
Reply:
Hello Adolf!
Thank you for your kind words!
A backslash here denotes a namespace - a relatively new concept in PHP.
It does not affect your code if you don't use namespaces but prevents an error/confusion if you do. Basically all PHP built-in classes are in the root namespace, so just a single backslash means - the root namespace.
Hello Thanks for PDO Examples :D Very Helpful <3
That is one fine article about PDO. Thanks a million.
$opt does not equal $options, see below.
];
try { $pdo = new PDO($dsn, $user, $pass, $options); }
Reply:
Hello moose!
Thank you very much, fixed!
Can you make a PDF version of this walk-through?
You're doing god's work.
I am a novice. I want to build a scoring application for a 'cricket' sports match which will involve a lot of real time inserting recalculation and updating and outputting. I've set up WampServer, I know I can build the Database and tables. Because of all the good things I've heard about PDO and because I will need to be connected to Mysql for about six hours straight, My question is, Do you think that this is the best or a good way to approach such a project. (Software wise)?
Reply:
Hello Stephen!
Yes, of course, PDO is the best API a novice could choose.
Please note that unless you are using websockets, there won't be a constant connection to a database. By default, PHP processes are atomic, a request initiated from a browser makes a php script run, return the requested data, and die. So it connects to a database every time. A very good and simple model.
Great stuff, but... I've posted a question at stackexchange for "PHP 7.2.8 PDO Fails to Connect to MySQL 8.0.12" that you may be interested in: https://serverfault.com/questions/924367/php-7-2-8-pdo-fails-to-connect-to-mysql-8-0-12-ga-in-aws-ec2-lemp-stack
In short, I used your advice above to write code more than a year ago, that has worked great with PHP 7.0 and MySQL 5.7. But though all other connections that I can think of to this MySQL work, PHP will not connect. Have tried many things so far. Wondering if you will have better ideas.
Thanks much.
Reply:
Hello Jeff!
Glad you got your case resolved. I had no idea of this issue and had nothing to offer but now I see it was resolved.
This is tutorial is superb. I would advice that you compile this into a book.
When is it OK NOT to use parameter binding, and when should you REALLY use it? Use when there is user input, or data being passed from one page to another (PHP)? Don't need to use when getting data within the same code page without any input except for the code itself? Would appreciate our opinion. Thanks!
Reply:
Hello Henry!
Thank you for the good question. There is indeed too much misunderstanding in the matter. So let's make it straight:
In the article it is explicitly stated as:
Notice that there is not a single condition you mentioned in your comment like "user input", "data within the same code page" and such. Simply because all these terms are essentially vague and uncertain. There is no certain definition for "user input". Different people take it differently, with catastrophic results. The moment you start separating the seep from the goat, the "clean" data from "unclean", the moment you are making your first step to disaster.
Remember, every application evolves over time. And something that has been hardcoded for ten years, one day becomes dynamically changed and dangerous. Why take chances? Why base your decision empty musings instead of following one simple rule - any variable that goes into query should be substituted with a placeholder, regardless of the source.
user interface for upload a photo
for transaction
// this part of user profile picture preview
I have already tried googling anywhere but has no result, when i remember i have bookmarked your page, then I open it. I cannot upload my photo with this.
Reply:
Hello Erik!
At least I an see that enctype="multipart/data-form" part is wrong. It should be enctype="multipart/form-data".
There could be other issues as well. But it makes very little sense in posting a code and asking what is wrong with it. Humans are not intended to run a code mentally, it's a job for a computer. So you have to run your code, make sure that all possible error reporting is on and start debugging. To tell you truth, debugging is the main occupations for any programmer. So it is not writing a program that takes most time but finding out why doesn't it work.
Reply:
This code should work, given there is a closing PHP tag ?> between PHP and HTML
code:
Reply:
Hello Erik!
You have problem in your code. it should be
Then you can display all the user data that is already fetched. You need to know the column names in order to display them though.
For example, if there is a name column, you can display it using $usr['name'] and so on.
What a fantastic article. Very helpful, packed full of information! Many thanks.
Excelent article! Answered some of my questions right up !
Hi, I was wondering if you could do a simple client/server pdo api example? I have been unable to find an examples/tutorials for a simple one. This should be geared towards having an application log data and then submit it either php or json/js to a remote DB site. I have made it work without an api, and with a simple php api . However I fail to be able to do this with PDO. It is not as simple as one would think.
Reply:
Hello Johnny!
You are probably mistaking PDO with something else. PDO is simply a database API, it knows nothing of json/js or application log data. It belongs to that "remote DB site" only. How this site is called is of no PDO's concern. In short, PDO is just a function to run your SQL query against a database server, no more.
Such a client/server pdo api example simply doesn't exist. The only server PDO talks to is a database server. Whereas any API is always implemented in PHP, which, in turn, uses PDO to query a database.
This resource you have come up with is extremely helpful, especially the parts where you explain where prepared statements can be used.
Thank you for all the effort you put in to it.
In the transaction section when you make mention of the rollback function, is it not meant to be in camel case as shown in the documentation?
(http://php.net/manual/en/pdo.rollback.php)
Have you considered writing articles on design patterns and or generic articles regarding writing smarter code in general?
Reply:
Hello!
In PHP, function and method names are case-insensitive, so technically it could be anything.
Although it could make sense to use a camel case to separate different words in the method name, "rollback" is the established term by itself in a database word and could be count as a whole word, thus making it unnecessary to use a camel case in it.
Does this statement prevent sql injection or does it turn emulation mode off??
setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
Reply:
Hello Marvin!
It does turn emulation mode off
Regarding the following error when using PDO fetch():
It seems as though there are multiple ways to fix this, however I'm really curious what YOU would recommend. There are so many differing opinions online, however yours is the only one I actually trust, so I thought I would ask you directly.
The possible fixes seem to be:
Are there any of these methods that you prefer? Or NOT prefer?
Reply:
Hello Greg!
This error can be caused by different problems. Therefore different solutions.
Most of time you should never see such an error at all. The only plausible case could be caused by a stored procedure, and should be treated as explained in the corresponding chapter.
If you have a certain scenario in mind, please share, I'll try to sort it out
$stmt = $pdo->prepare("SELECT value FROM table WHERE id=?"); $stmt->execute([$_GET['id']]); $data = $stmt->fetchColumn(); if ($data){ if ($data==0){ // do something } else { // do something else } } else { // do default action }
If value is 0, this code always do default action. How to select only one column a check it when this column is 0?
Reply:
Hello eFko!
If I get you right, instead of
if($data)you can test for FALSE value with strict comparison in the first condition:should do something when $data = 0
That was it! I closed the <input> tag too soon. I also needed to remove id= for every input field, including the submit button, which I changed from type="button" to type="submit". Now everything works! I as able to insert into select fields in the table, not all fields. Thanks so much for your help!
You are not showing an INSERT example and I really need help using a FORM and getting the info from the form into the db. AMPPS says I'm using php7 but my exports say it's 5.6.37. Here's my info and using php tags:
I have two TIMESTAMP fields for start/end times and an AI id field which auto-fill with each new record so I don't insert into them. Can you see what I'm doing wrong and help me? Thanks.
Reply:
Hello1 tharring!
There is nothing wrong with your insert. Its HTML form which is at least wrong. You are closing an HTML tag too early should be like
Hi there, firstly thanks a lot for the article. I have a question of my own.
Is there an efficient way of doing an
UPDATE..SET..WHEREwith large dataset? For example, instead of multiple SET
queue_order=$queue_order WHERE queue_id=$queue_id;, is there a way to bundle them in a single PDO statement without using a for loop?Thanks.
Reply:
Hello James!
Although there are ways to do that, I would advise against. There is no point in stuffing as much queries in one statement as possible. Prepared statements are exactly for that purpose. Just prepare your UPDATE statement once and then execute it in a loop, like it shown in this article: https://phpdelusions.net/pdo#multiexec
It will be as fast as a single statement.
If your updates will run unexpectedly slow, then it's your database settings to blame. To fix that, wrap your updates in a single transaction.
Hello I wish to resume coding after more than 2 years without practice. now i want to realize my personal projects with php mvc in a first so I want to understand how to start! thank you!
Reply:
Hello Armel!
My strong recommendation wouild be to try a well-established framework, Symfony preferably. It will give you the best understanding of MVC.
Bonjour Je souhaiterai reprendre ? coder apr?s plus de 2 ans sans pratique. maintenant je veux r?aliser mes projets perso avec php mvc dans un premier tant je veux comprendre comment d?buter! merci!
Thanks for your reply. I followed your guide for beginners and implemented the logging-errors advice. So far, the best approach :-)
$query = "SELECT tbl_product.*, tbl_cart.id as cart_id,tbl_cart.quantity FROM tbl_product, tbl_cart WHERE tbl_product.id = tbl_cart.product_id AND tbl_cart.member_id = ?";
what does this queery mean?
Hi men, good post, VERY GOOD, i have a question about procedures.
How can i use procedures with FOREACH, how can i replace the while with foreach, implementing the empty result prevention, or is more efficiente use while instead?, because i like work with foreach
Reply:
Hello Victor!
It is not very clear what you are asking, but in general there is no difference between using foreach and while.
In both cases you can just iterate over the statement. In case there is no result, then there simply will be no output:
In case you want to know if there is any result beforehand, just fetch the resulting dataset using fetchAll():
Hope it is clear now, but if not, please ask
Hi, Very good tutorial, thanks for sharing. My production database server went down and when the app tried to connect to it, the error report threw back the database connection details. Not nice. How would you recommend to handle this error? Thx
Reply:
Hello Michael!
This is a very important question! However, it is not actually related to PDO or database, but rather to the whole PHP configuration. Your PHP sees to be misconfigured, leaking every error message outside, which shouldn't be. Actually, every error message is a breach in the security, so the solution should be a general one.
On a live server PHP must be configured to log errors instead of displaying them, so it would never happen again to leak your database credentials outside. You may find useful my article on the PHP error reporting configuration: https://phpdelusions.net/articles/error_reporting
Feel free to drop a line if something is still unclear or you disagree with the proposed solution
first thx for your tutorial. injection paranoia : prepare = safe ?
it is safe ? thx
Reply:
Hello Gio!
Yes, that's perfectly safe. PDO witll either correctly format a parameter for you (if emulation mode is turned on) or even send it completely separated from the query, and so there would be no way for it to interfere.
Beware that bitfields in MySQL suffer from the same problem that terms in the LIMIT clause do when using prepared statements, i.e. in emulation mode, strings are not correctly interpreted. So if you are converting code like mysql_query("create table foo (bits bit(48))"); mysql_query("insert into foo (bits) values ($val)"; into $pdo->prepare("insert into foo (bits) values (?)")->execute([64]); you will actually get the value 13876 inserted (0011 0110 0011 0100) which is the bytes of the ASCII characters '6' and '4'.
To fix this, either turn off emulation, or use bindParam as described in the section on LIMIT, or change your code to $pdo->prepare("insert into foo (bits) values (cast(? as decimal))")->execute([64]); Something for the next version of this page perhaps?
hi i am getting such an error- " Please make the application/config/database.php file writable".
how to make a php file writable using chd in remote server?
i am brand new to pdo and i have a question about database connections.
on one page you say:
but on another page you say:
these statements seem contradictory to me. can you please explain how to properly connect to a database with pdo? I am using oop php.
thanks, jeff
Reply:
Hello Jeff!
Thank you for the good question!
When you assign an object to a variable, there remains just a single object, whereas the second variable being just a reference to it. So when you are sending a PDO instance into a class' constructor, then it's actually a reference, that points to the same PDO instance. So the initial PDO object is not gets duplicated but remains essentially a single instance.
So you can tell that the former statement doesn't violate the rule from the latter but rater gets along with it
Hope it is clear now but feel free to ask if not!
Great Column!
I was wondering on the following code snippet:
Why are you escaping the field names. Should the $allowed whitelist already handle any SQL injection risk?
Reply:
Hello T G!
Yeah, that's an awkward moment. On the one hand, you don't have to escape a whitelisted value as nobody in their right mind would use a backtick in the field name. On the other hand, however, if you take the formatting routine as an abstract one, unaware of the data source, it would be natural to do the full processing, both quoting and escaping - just like any good quoting function should do.
I think it would be better to move the full quoting routine into a distinct function that will be doing the correct formatting regardless. I suppose it will arise less questions. What do you think?
Hi and thanks for this great information. I have learnt a lot about PDO and - at least as useful - about a consistent approach to error handling! I also like your style of writing. Thanks mucho!
Reply:
Hello Jayenn!
Thank you for your kind words and especially for mentioning the error handling stuff! I am nor sort of crusade in changing the current state of error handling in PHP.
Feel free to check back if you have any problem though - any real life use case can make my articles batter for everyone.
I have a pdo search engine using MYSQL database. How can I get the number of search results per "MYSQL field"?
Reply:
Hello!
Just run a
and select that number using fetchColumn()
It appears that adding a code block does not work. I just tried submitting another comment with code, but it did not appear as I expected.
I submitted a comment a few days ago about escaping operands for the LIKE operator. Do you think it may have been sent to a spam folder?
I figured it would have made it through by now...
Reply:
Hello Jason!
There is no spam folder, but it could have been an error on the site.
Escaping special characters used for the LIKE operator is not specific to PDO, it's rather related to the particular database. But probably I should add a mention at least for MySQL.
What was your question anyway?
I was talking with another programmer and hes was complaining about SQL query not working (He has not the use to test his queries on the database before coding them on PHP). And he said :
-- It would be usefull to be able to directely see the query made by PDO to the database.
-- you mean with the values of binded ?
-- Yes !
-- For debugging ?
-- Of course, what kind of programmer do you take me for ?
I searched I stumbled on you (Your articles are wonderfully readable by the way).
Reply:
Hello Vincent!
Although it is often useful to have a query from PDO with all the data interpolated, I would rather use it for the profiling.
Whereas when a query is not working, all you need as an error message from a database, that will tell you precisely, what is the problem. Note that you should be using prepared statements to avoid silly syntax errors caused by the data - it goes without saying.
That said, there are several code snippets and packages that can get you raw SQL from a prepared query, just google for "pdo interpolated query" or "PDO raw query".
I have a simple loop that runs a small number of update statements if there's only one in the loop it works fine more than one and only the first works, with no errors reported I'm confused!!
Reply:
Hello Mike!
Dunno why did you cut off the part after WHERE but it's impossible to tell anything without seeing the actual code, sorry.
Most likely you are just updating the same row.
How frustrating. I just tested with PostgreSQL, but it uses single quotes so it worked there. One wonders why in so many years, such a basic and necessary feature is not there (found a thread from 2008 with people complaining about this, sayng it's years overdue). I just migrated from MDB2 because of it's unfinished state and difficult install method and chose PDO. One of my tables matches one of MySQL's many reserved words, so I must quote it and I was using quoteidentifier from MDB2. I see Zend Engine has it, but that means a large dependency for a single quoting function. Seems I'll have to roll up my sleeves. Thanks for the heads up, it could have gone unnoticed otherwise!
Nice rundown, much better than overly complex tutorials. However, identifier quoting does exist in PDO (didn't back then?).
$pdo->quote('mytablename'); will take care of any strange quotes. Keep in mind only MySQL uses backticks, so using this is better if you want to keep it a little more DB-agnostic.
Reply:
Hello Jorge!
Thank you for your feedback.
Unfortunately, there is some confusion. PDO::quote() creates a data literal, not an identifier. For example, for MySQL the returned value will be enclosed in quotes, not backticks, which will make it unsuitable to be used as a table name.
Dear Colonel, I greatly appreciate this website and the common sense you share on Stack Overflow. May I ask you, please, if you have time, to take a look at a conundrum I have posted on SO? (https://stackoverflow.com/questions/47278348/php-output-echoed-out-of-order). I understand if that's not possible for you. Best wishes for the future
Reply:
Hello Emma!
I see it has been happily resolved already. Glad you got it working and thank you for your kind words.
Feel free to come back if you have any other questions :)
Oh I typed in my second thought before I saw that you had responded to my question. Looks like we came to the same conclusion,
Awesome Thanks for your response
I suppose I could run a sub-query on COUNT(*)
Hello, I am running into a problem with pagination. As you recommend I use COUNT(*). But when using with GROUP BY I have a problem. With just one GROUP BY I can use COUNT(DISTINCT column). But now I have a query that requires 2 GROUP BY's and am having a problem and SQL_CALC_FOUND_ROWS is not a solution for my case. It's looking like 'then you can either use rowCount() or simply call count() on the array returned by fetchAll()" are my only solutions.
Do you have any suggestions?
P.S. Thanks for your great PDO reference site.
Ty
Reply:
Hello Tyler!
Thank you for the good question. I should have added it to the article. And especially thank you for asking. It's always better to ask than devise something of your own.
Yes, there is a way to ask a database to count on its side in this case as well. Just wrap your query into another query, like
The idea is to wrap your query into another, and use it to count results. For this purpose we are selecting just a constant value "1" instead of the actual fields in your main query, just to make a database to do less job.
Feel free to ask if you got any other questions!
In testing I notice that if you use sql statements containing LIKE in combination with $_GET it is possible to inject % into the statement and retrieve all the data from a table. e.g. if I do not escape the % this retrieves all users. http://example.php?first_name=%
The issue is referenced here http://php.net/manual/en/pdostatement.bindvalue.php
but I can't find any mention of it in your more extensive documentation. I have to say that I did not understand the code example you use to explain how to use 'like' with pdo.
Thanks, John
Reply:
Hello John.
first of all, be advised to distinguish an official manual page for user-contributed comments. The latter can be left by any unsuspecting fellow and should be taken with a pinch of salt.
As of your concern, it's really simple.
Greetings. I have enjoyed reading your content, especially the part about the PDO wrapper. Your site has become a bit of a reference on the topic. Helps people not to reinvent the wheel every day. As a personal wish, if I may, I'd like to get your insights on 'cache invalidation' an PDO. A bit off topic perhaps - it's just something I am working on these days. An ambitious idea of having a "caching system" underlying the app, i.e. transparent to the app who whouldn't know if data is coming from Memcache or the database. Queries would be handled by the PDO wrapper (-ish) and cached (using memcache), and also invalidated when 'interested' rows are updated/deleted. Not an easy task to develop something like that. Also, it'd be nice to have the User comments about "nested transactions" (see http://php.net/manual/en/pdo.begintransaction.php) incorporated in your articles, would probably be useful for a bunch of people. Great work.
Reply:
Hello M!
Yes this question a bit off topic, but from my experience I would rather warn you against such a generic all-embracing cache solution. In my opinion, your regular queries should be fast by themselves, without the need of any cache. And it's only a few exceptional cases should be handled using a cache, but such cases should be carefully handpicked, considered using a regular optimization, and only then optimized using a cache, with a carefully considered invalidation.
Regarding transactions, thank you for the suggestion, I will add a link to that comment.
Hi, I again stuck at some point, Now I am trying to edit product details which I already have added. I also have added image field when I click on edit button and then when I add only some feature after clicking on update button I find Picture which I did not touch have gone.... Now after analysing I have understood the Problem but I am unable to resolve the issue if you can help me in this please. my code is:
See in above When we use move_uploaded_file then what happen actually is our database only get the path of image while image actually move or resides into the folder we have mentioned into path. So, when I try to edit/update page when code approached to database field product_img1 it only find path but not actual image. I think there must be a way like move_uploaded_file which I don't know :(
Please could you help me in this? how to fix this issue that if I don't want to update image, image should remain there?????
Hi David, Yeah, you are right, I am mad that since hours I was checking my code madly, then took some rest and when again checked, I found my mistakes, both foreign keys I had placed wrongly; I corrected in the way below and my code is now working perfectly: into insert statement (cat_id, sub_cat_id) and into values($cat_name, $sub_cat_name)
I also removed function NOW() and mysql is taking time itself. i might don't need to do anything. I was so happy and wanted to let you know, when came here I found your reply. I really appreciate you for a quick response, you are doing such a great job by helping students. Great work! I will try to bound foreign keys as well as suggested. Thanks a lot and Regards,
Hi, I am trying to learn PDO but stuck as receiving error message says: "PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: parameter was not defined" here is code:
cat_id and sub_cat_id are foreign keys while I also want to get date and time into Products table by function Now() Prepare statement and bindParam is as below:
Call to function has been made into form where I need this to be executed.
It looks like I have set bindParam wrongly, I don't understand how to make it perfectly for cat_id, sub_cat_id and function NOW(); your help will be appreciated. regards,
Reply:
Hello Zohra!
All you need is to brace yourself and write a consistent code. Either in your editor or here in comments.
I took your code and run it on my local server and there is no "Invalid parameter number". Therefore I suppose that you are just doing some mistakes when running your code and don't verify it, hence the error.
So just double check your code, and it will work!
Note that you should use prepared statements for ALL variables. $cat_id, $sub_cat_id should be bound as well.
Can you help me please. I am trying to perform the following PDO. All the values are here are present. However I get an error message " Call to a member function prepare() on null in "etc.
Reply:
Hello David!
This error means that you are calling $pdo inside a function, where variable scope is different. You need to make your PDO instance accessible. A short list of variants is listed here: https://phpdelusions.net/pdo_examples/connect_to_mysql#access
Please feel free to comment back if you need any help with any particular method
Thanks so much for your quick response. I think I just need to take a step back and do some more reading. I think this code wont work anyway because the scope of the $bdo variable is outside of the function. I need to work that one out next!
Reply:
Hello Ed!
Whoops, I didn't notice the issue with variable scope, shame on me.
There are several ways you could get a PDO instance. You may check this page for some suggestions: PDO wrapper
Thanks for the great and extensive instructions. I havent got very far but I am having a problem with passing the result of a function into a conditional statement. The database connects ok but I think the problem is here:
Any ideas?
Reply:
Hello Ed!
Thank you for the perfectly formatted question!
Yes, you spotted the problem perfectly. The value returned by fetch() method is not a number but array, and therefore comparing it with a number won't give you any good result.
If you want to fetch the number, you should use fetchColumn() instead. So your code would be
Besides, the result of == operator is already a boolean, no need to add another condition, you can return it right away.
Also note that echong the error message right away is not advised. Consider the information from the section related to PHP error reporting
Feel free to ask if you have any other questions!
Hi.
I have a small suggestion - if there are more than e.g. 10 comments, they could be hidden under "show more comments" link, or something. IMHO it doesn't have to be ajax, just a hidden <div> which you show upon clicking the link.
Right now people look at the scrollbar and get scared, because they think the article is soooo long. While it is quite long, and comprehensive, it is not that huge :P
Plus, next to the 'address' field in the 'comments' section, you could add "not required" and "will not be published"
Thank you very much for this article, I like pointing here ppl who want to start with PDO :P
Reply:
Hello Konrad!
Thank you for the suggestions! I'll definitely implement both of them soon.
Hi, please am trying to get a value from a field in a datatable, the scenario is like this: I have a table with data about four columns, at a click of a button which is an action in the table, a modal appears with four input boxes, I made the first input box disabled so as to pick a value from a field in the table. But i can't seem to implement it. Please can you help?
Thank you for your reply. I am a little confused though, but will try to solve my lack of knowledge. Regards. David.
I would appreciate your help with a PDO issue. I am calling a PDO select function which works except I only get one piece of data from a file that contains two pieces. the code is
The field "Dn" should return "Firstname Surname" separted by a space. All I get returned is the Firstname. Please can you help me ? Regards David.
Reply:
Hello David!
Most likely your issue is not with PDO but with HTML. Check the HRML source of your page and find your last name all right.
Then follow the HTML syntax and wrap Dn field value in quotes :)
Hey, I was linked to your site, and it seems to have some nice recommendations. That said, I was looking at the section on rowCount, and it seems like you take quite the position on 'it shouldn't be necessary'. I've run into this quite a bit when performing pagination of results, and I'm curious as to what your recommendation is to do in this case instead of getting a row count?
Reply:
Hello Tyler!
I should have probably emphasized it better, but using anything like row count for pagination is not something "not necessary" but a big mistake.
For paginafion you need 2 queries: one that gets actual data for a particular page, for this query you don't need no count at all.
Another query, which is used to get the total number of rows, should never be using anything like row count. Instead of selecting actual rows and then discarding them, only to get the count, one have to get just the count already, by using a select count(*) query. Otherwise it will defeat the very purpose of pagination: selecting only a smaller amount of data that will be actually processed. It's not a big deal for a personal home page, but selecting all the big data only to count it may kill a mature site.
Hi! Whoever you are. I just wanted to let you know, that all your tips and best practices are helping me a lot to code better and more efficient. I can't thank you enough!
I just wanted to say thank you - your solution worked perfectly!
So I understand PDO Prepared Statements should protect from SQL injection and ' escapes. But when I attempted the following...
Then the row with id=2 and name=entry2 would be updated. I should note, I only saw this behavior within the "WHERE ..." part of the query. It did not escape in the "SET...WHERE" part of the query, and I wasn't able to add any additional queries in either. I observed the same behavior when I tried with mysqli as well.
That said, I didn't find anyone pointing this out anywhere, and when I asked on Stack Overflow, no one believed me. I could probably just manually replace ' instances with something else for storage and replace again for output, but the fact that I'm the only one who seems to observe this has me seriously confused. Any insight would be greatly appreciated.
Reply:
Hello James!
It is quite strange that on Stack Overflow nobody believed you, as the behavior is quite expected, given the way mysql (and PHP as well) treat input values. Well, nothing strange on the other hand, given the average level of expertise there.
So, the issue is not actually an injection, neither it has anything to do with PDO. It's an integral part of MySQL. By default, in the older versions of Mysql (pre 5.7. if I am not mistaken), when casting input value to an integer, it takes first numeric characters, cutting off everything else. You can see it with such a simple example:
As you can see, the value become just 25 when cast to an integer to perform an arithmetic operation.
Exactly the same happened in your example. Value "2" has been taken out of the "2' AND name='Entry2" string which made a legitimate query and thus the record got updated.
To prevent this behavior you can set mysql in strict mode, like
it will start throwing errors for the malformed numeric values instead of silently casting them.
I've reopened your question on Stack Overflow, you can now write an answer for it and link to this comment, https://phpdelusions.net/pdo#comment-277 as a proof
Beware that nothing will alert you about the lack of support for the
PDO::MYSQL_ATTR_FOUND_ROWSattribute.Only way to check if your PHP is going to ignore this attribute is to call
$pdo->getAttribute(PDO::MYSQL_ATTR_FOUND_ROWS). Not sure why this isn't done when setting the attribute. Go figure the reasoning behind this.To reiterate:
PDO::MYSQL_ATTR_FOUND_ROWSdoesn't always work, even if the constant is defined.getAttribute().Reply:
Hello Neighbour!
I thank you for your valuable info! Looks like an important case. I added the link to this comment to the article.
Though I can answer one of your concerns. Given that
MYSQL_ATTR_FOUND_ROWSis a connection option for MySQL, it's impossible to use it anywhere else, and thus there is no way to change this parameter on the fly, within the same connection, unless Mysqli C API will be changed.Hi,
I'm sorry if my previous question was unclear, but I'd love an answer, even as to whether this is possible, if you have the time.
I'd like to do something like this:
where customer_name and customer_email come from (sanitized) user inputs. Is it possible to format this query so that if I wanted to update ONLY the customer name or ONLY the customer email it would not overwrite the item that I'm NOT updating? So if the user inputs a customer name and leaves the email blank, it will update the name but preserve the email that is already in the database.
In order to do this would I have to write a separate query for each item? In my actual code I have many other items that I'd like to be able to update without overwriting the information in all of the database columns at once. It seems like using positional parameters might be a solution, but I haven't been able to get it to work. I was hoping you could tell me if this is possible, thanks!
Reply:
Hello!
Sorry for the delayed answer, by the time you asked for the first time, I've been on vacations and missed your comment.
What I would suggest is to create a query dynamically, from the array provided, as it's explained in this article
So it should be like
Feel free to ask if you have any other questions
Thank you for your reply. I will give more detail into my reasoning.
I have an online store i have built and i am afraid that a customer may be left on a blank page or an abruptly ended script without reason during the checkout process, or post checkout process receiving their order confirmation. I've encountered such an error myself on another website from having an intermittent internet connection and being left without any notification it leaves fear of having paid for something without the order having being processed properly on their end.
I've been trying to handle every single type of error imaginable so my code is full of try... catches as well as a billion if statements. I'm desperately trying make my coding much more efficient.
My use of the try... catch operator would also be that if there was an error on a product page I would have liked it to just keep the script running and display the 'Sorry we could not find your product' situation, as a kind of diversion until i can fix what is wrong.
I will read you article on error reporting in further depth.
Thank you
Reply:
Hello Jeremy!
I am eager to hear what are your thoughts after reading the article? Do you agree that centralized error reporting is better than catching every exception separately?
At the very least you an create a global try..catch wrapping your whole code from start to end. But To me, using a handler is more elegant solution
Please can you help me resolve an issue? I have a user input box which starts a search of a PDO MySql table :- using this code snippet.<input type="text" name = "t1" id="t1" placeholder="Enter an RD number"> <input type="button" name="button1" value="GO" onClick="aa();"> </td> <td colspan="2" align="center"><div id="d1"></td> via this code- <script type="text/javascript"> function aa() { var xmlhttp=new XMLHttpRequest(); xmlhttp.open("GET","getrd.php?Regno="+document.getElementById("t1").value,false); xmlhttp.send(null); document.getElementById("d1").innerHTML=xmlhttp.responseText;<script> this returns 3 data items. All this works. My issue is how can I put these 3 data items into <input type="text" name = "Rd" id="ret1" placeholder="Rd No" > <input type="text" name = "Dn" id="ret2" placeholder="DN"> <input type="text" name = "St" id="ret3" placeholder="Status"> I have tried a number of variations of the above code without success, I am sure there is a way to do it but I am unable to get there yet. Many thanks. David.
So i understand that i shouldn't use try .. catch for error reporting. But is it fine to use for displaying a custom notification to the user that an error has occurred?
Furthermore, if omitting rowCount as a boolean flag and using the $stmt variable to see if any data was returned;
If an exception has been thrown and caught the $data variable remains set and giving a true boolean value even though there is no data present, and it doesn't seem possible to globally unset($data) inside the catch as it's a function.
I would like to catch an error to notify the use 'Something went wrong' and to return a false boolean flag to handle the rest of the script outside of the try... catch. How would you recommend the best way i can achieve this?
I'm still learning programming.
Reply:
Hello Jeremy!
Sorry for the delayed answer, I've been on vacation.
Regarding the first question, it is extremely seldom when you need to provide a custom error message. Given you are still learning, it is possible that your understanding of the error reporting is rather wrong. Kindly read this article, https://phpdelusions.net/articles/error_reporting and I hope you'll change your mind.
The second question is simpler to answer. If an exception has been thrown and caught, the $data variable is not set, so there is no trouble with unsetting it whatsoever.
If you want to continue the script execution even in case of error, it's a fair use of the try..catch operator. But still, I cannot imagine the practical case. Could you please comment back with a little more detailed description of the scenario you have in mind?
Thank you for that. However, I'm now confused even more. I get: "Fatal error: Call to a member function query() on null" and then the path to the document... Alternatively, I get a blank page
Reply:
Well, this one is simple. All you need is to read the article above, Particularly the error reporting part. You didn't tell a database to report mysql errors and thus getting this cryptic one. Just make it the proper way and see.
Thanks very much (hope formatting is correct). Here it is. I created one class for connecting to my database:
And now I'm making one that is supposed to handle queries (called it ActiveRecord as that is the pattern I'm trying to implement):
Finally, I plan to create a class for each table in my db, that will extend the previous class and use one of the methods I'll create in ActiveRecord. Something like this:
When I run the last code, I get the error, so obviously, my query is wrong. I realize this is quite basic, but I can't seem to solve the problem. Thank you!
Reply:
Hello Adri!
Sorry I forgot to mention that a code should be padded by empty lines as well. But nevermind, I already added them.
First and foremost: the "error" you are getting is not just a red card telling you that you failed something. It usually explains what specific error you made. So it's always worth to read what the error says or at least provide to a person you are asking for help. There could be thousands errors, and its no use to stare at the code looking for them if PHP can tell you that already. Especially because it can be caused not by the code itself but by some other issue - a database, or server config or whatever.
As of your code, I tried it and it works.
Hi! I'm trying to implement PDO in my code (I'm a complete noob, by the way) and I have a "why won't this work" type of issue. Would you mind checking it out (I'm not sure if that is OK, which is why I'm not sharing the code now)? Thanks for your explanations, by the way, they are incredibly useful!
Reply:
Hello Adri!
Yes it's all right to ask a question here. Just make sure that your code is indented by 4 spaces, so it will be nicely formatted
Thanks a lot, PDO is FTW :)))
Hi, sorry if this is a double post! I wasn't sure if my other comment was in moderation or disappeared.
Thanks for the great tutorial, it's one of the best I've come across for learning to use PDO.
I was wondering if you could give an example of how to create a PDO update query where you can have a variable number of inputs. So for example if I have a form where I can update a customer's name, email, or phone number. But sometimes I want to update only one at a time without overwriting the information already in the database.
Awesome! I'm just learning PHP and this was the first tutorial that made any sense. Most have code without going to the details which are the most confusing part at the start
Reply:
Hello Tomi!
Thank you for your kind words!
Can you do me a favor? I am going to add a section called "PDO examples" with practical examples of PDO usage. If you can think of any example case you'd like you see, please drop a comment.
Thank You
This is an awesome guide, thanks so much for this.
So a normal use case would be like this.
Now $table is:
Then I use this for query:
or use
?
Thanks again.
Reply:
Hello Christian!
If backticks are already added to the identifier, then there is no point to add them again. So it should be your first variant.
Thank you for articles and your responses to questions. Can you please clarify the section ‘Prepared statements and table names’?
I read that MySQL an identifier needs a (`) backtick if it contains special characters or is a reserved word. In your example we concatenate (`) backticks on both side of the identifier and replace single backticks with double backticks on original $table variable. So the new $table variable would be ``identifier`` because extra backticks are like an escape character(\) and that would remove one backtick? How does this sanitize $table variable ? Does this have anything to do with PHP Execution Operators? Thank you for your help!
Reply:
Hello Christian!
Thank you for the good question. Look at this from the point of view of the regular string formatting. To format a string we need to enclose it into delimiters and then escape those delimiters that could happen to be inside. Exactly the same we are doing here: We are adding delimiters (single backticks around) and then escaping these that are could be already inside of the table name. But we don't double the surrounding backticks, neither removing any. The above statement could be split into two:
Imagine we have a table name coming from the user input and a hacker put a backtick inside. Without doubling, with only surrounding backticks we'll get an injection:
will result with malicious query
whereas with doubled backticks inside the whole statement will make a query like this
Where
is a single identifier (a-non existent of course, which will produce an error, but an error is better than injection).
Hope it is clear now. If not, please ask :)
Thank you for articles and your responses to questions. Can you please clarify the section ‘Prepared statements and table names’?
I read that MySQL an identifier needs a (
) backtick if it contains special characters or is a reserved word. In your example we concatenate () backticks on both side of the identifier and replace single backticks with double backticks on original $table variable. So the new $table variable would beidentifierbecause extra backticks are like an escape character() and that would remove one backtick? How does this sanitize $table variable ? Does this have anything to do with PHP Execution Operators? Thank you for your help!There seems to be a bug in the code sample that describes the foreach functionality:
This should be:
Alternatively, you could put ->fetchAll() at the end of the $stmt assignment instead of in the foreach.
Reply:
Hello, Bill!
There is no error and there is an explanation why it is so.
Let me suggest you to try the first code snippet yourself, see it actually works and then follow the link from the article explaining the trick :)
Does Sanitizing like the following make sense?
Reply:
Hello Christian!
If you mean
filter_input(), it's superfluous for this code snippet. Using prepared statement is secure enough. So, an extra sanitization won't make too much sense.Personally, I would make such a snippet quite shorter
but it's only a matter of taste and you can keep with manual binding and named parameters.
Thanks for your reply. When a user has logged in they presented with a menu screen to choose where on the site they want to work. Then a menu second allows them to select a sub area to work in according to their user rights. its function is to open MySql tables via a PDO connection. this is the problem screen. it is taking too long to appear. Thanks David E.
Reply:
It's hard to tell what could be a problem. But at least try to change "localhost" to 127.0.0.1 in the DSN.
If it's not the case, you need to investigate more, what particular operator in your code takes all the time.
Many thanks for your prompt reply. I was trying to avoid the "waiting for" messages. Your answer is very much appreciated. Thanks again. David E.
Reply:
Can you please elaborate on these "waiting for" messages? May be I can offer some solution, but for the moment I am not quite getting what is it.
A question of good practice. Is it appropriate to create database connection at login time and then not close the connection until logging out ?
Reply:
Hello David!
thank you for the good question.
It depends on what you're calling "login" and "logout".
In case you're talking of a user visiting your site, it would be impossible. The nature of PHP's life-cycle is atomic. It starts every time a user visits another page, generates the page to be shown, and then dies, a fraction of second after starting. And so with every new page a new PDO connection inevitably have to be established.
In case you are talking of a single script execution - yes, like it said in the article, the connection should be established only once.
You're welcome to ask if something is still unclear for you!
Many thanks for the updated code, it is working perfectly now. Very much appreciated. Kind regards. David E.
Many thanks for rapid response to my PDO query and thanks for the conversion coding. I have this line in my connection code already, but I did add your line to the new script. PDO::ATTR_EMULATE_PREPARES => false, However I am still getting error message SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIKE ? ORDER BY Friendship LIMIT ?,?' at line 1. I am stuck now. Hope you can help me. Many thanks.
Reply:
Hello David!
First, I must confess that I made a terrible mistake. I overlooked the severe vulnerability in your code. If $option is coming from the browser, then it's a disaster. You should sanitize this variable, checking it against an array of predefined values. And most likely this is the source of the error you are getting.
So the final code would be
The first three lines are responsible for the $option santitization.
s/count(/COUNT(/g
"Good ORMs are Doctrine, Eloquent, RedBean, and Yii::AR. Aura.SQL is a good example of a PDO wrapper with many additional features."
Please provide a review of these or maybe a separate post. It is a huge qualifier when you say "for real applications you'll need to use one of these bunch of libraries but for now let me tell you all about PDO..."
Reply:
Hello William!
Thank you for a good suggestion. Indeed it's a very good idea to outline at least essential features of these libraries and to show why they actually to be preferred. I cannot promise it will appear soon, as it' pretty much an undertaking, but I'll definitely will start composing such an article
How may I convert this mysql query to PDO query.
$optionis user selected from dropdown list.$srchis user input search criteria.Reply:
Hello David!
There are two issues to keep in mind when converting such a query, both mentioned in this article:
So the final code would be
Hope it helps!
Very, very good website for someone starting with PDO. Thank You for making it!
Hi,
Trying to get last 25 row in ascending order but getting error any idea what could be error
output
Reply:
Hello Ganesh!
Thank you for your question. I never seen such an error message before. Looks like you already posted a bug on the PHP site, https://bugs.php.net/bug.php?id=74769
Looks like it's all you can do.
Hi, Thanks for Awesome Documentation and easiest on
what should i care about when i am migrating to postgresql from mysql ???
Thanks
I Want to use dbprefix
in select,update and insert also should i use :dbprefix but not working
Reply:
Hello Ganesh!
Unfortunately, PDO doesn't have a placeholder for the table name. So So all you can do is to use a PHP variable and make sure it is not coming from user input:
Very instructives Thanks.
Thank you for your advice. I would like you to be my mentor. I have still going through your site and I am happy to tell you that I am really learning a lot.
Thank you very much.
Hi, I found out that your site is very useful to me as i am a beginner to coding. I wanted to know whether to use mysqli or pdo. I searched and came to a point which i decided to pay more attention to using pdo than mysqli. My question is, am i on the right track with my decision or do i have to lern php with mysqli first?
Reply:
Hello!
PDO is easier to use, therefore it is easier to learn as well. So I would recommend to keep up with PDO.
"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."
You mention that bindParam has side effects, but there's no explanation as to what they are. PHP The Right Way and some other sites recommend bindParam, so I'm really curious why you recommend bindValue.
Thank you for providing a great resource. I want to reference your site in my undergrad paper to Flidners University South Australia, on serving my first database with WAMP, can i obtain your name ?
I'm trying to run a stored procedure on a Sybase 11.0.1.2596 database in a PHP file using PDO and dblib as the driver. I can call a procedure with no parameters with no problems using something like:
However, if the procedure takes parameters, I get an error. If I have a procedure like this that takes 2 parameters:
I have tried calling it many ways and received different errors including this:
etc. If I run this procedure in a SQL client like RazorSQL using:
it works perfectly. The exact way I'm trying to call it is like this:
I would like to know more, is there any pdf's available about this tutorial I'm eager to learn
Hi! Another brief question: I was expecting to receive the exact same results using this approaches, but it seems I got different results (results in terms of the output results/data of the query)
And then this one: $stmt->execute(["Lim" => $L, "Lim2" => $L2, "Name" => $Name, "Name2" => $Name2]);
I'm I missing something? Shouldn't have been the exact results?
Thanks!
Reply:
Nope, as long as this code works, there should be no difference. The only case I can think of is when you are comparing a numeric value with a string field, something like
name=1in SQL. In this case results are indeed unpredictable.Do you mind to provide more details on the difference you are talking about?
Hello, I am following closely your tutorial and I am really grateful for the useful information. Now I need your help again:
As you said, there is a problem with the Limit clause.. and I'm stuck in the middle.
On one hand, I need the emulation mode turned ON as I have to query for multiple fields having the same name, as in your example:
And on the other hand I need the Limit parameters, for which the emulation needs to be OFF in order to function.
Otherwise, Is there any way to bind only the values for Limit parameters and continue executing: $stm ->execute(array_merge($params,${"in_params{$x}"}));
Thanks a lot!
Reply:
Hello Alex!
Thank you for a good question!
Indeed there is no way to combine the execute and bindvalue methods. So in general you just have to write all the binding by hand. I suppose that for an exceptional case it is not a big deal.
However, I think there is a workaround, which is quite close to your requirements: you can write a loop, binding all other parameters from array, and then make two distinct bindvalue calls, something like this:
This is all I can offer. Hope it helps!
Hi,
I have a piece of hardware that collects data and operates a machine and then you can log into it to get statistics. A second operation it does (not very well) is send out data to a web database which stores what it sends. Every tutorial i find with api for php uses mysqli connect or other bad practices. Do you have to do a tutorial with the right pdo framework?
There's a misspelling in the Transactions section:
"to cancel all the changes you made sihce transaction start"
sihce should be since
Reply:
Thank you, fixed.
It's c. 2017, seven years after mysql 5.5.3 was published, which supports utf8mb4 - don't you think you should suggest this as encoding instead of utf-8?
Have you considered
Reply:
Hello Dave!
Everyone is tempted to do that at first, but soon realizes it's impossible :)
The method chaining returns the return value of the last method in the chain,
execute()here, and it's a boolean value. So it won't work.To overcome this inconvenience I propose to extend PDO a bit, as it shown in the other article, https://phpdelusions.net/pdo/pdo_wrapper
So we can make it
Got caught out last night when upgrading our production server's PHP7 version and modules (most likely php7-pdo?). PDO couldn't parse this previously working DSN:
Fixed with this (note the port has moved in the DSN):
Better to stay safe and follow the manual gents.
Hi, really good stuff to know. Helped me!! Thanks!
I'm having a hard time gaining an understanding of best practices of real world implementation of PDO. Especially file structuring - as in where my PDO connection should be located and efficiently used throughout a php app. If this is not within the scope of this site perhaps you can direct me to a good reference. Thanks!
Reply:
Hello Jeff!
Thank you for the good question, it's perfectly within the scope of this site.
In general, you just create a separate file where PDO instance is created, like
$pdo = new PDO .... And then just include/require that file into every script that needs a PDO connection.The main question here is how to use that instance. And this answer depends on your coding style. If your code is procedural, then just use that
$pdovariable. To make it accessible inside functions, either make itglobalor use a static wrapper like one is shown on the next page: https://phpdelusions.net/pdo/pdo_wrapperIf your code is OOP, that it's a different tory, too big to cover in a comment. You may google for the Dependency injection and IoC containers.
If you still have some questions, I'll be glad to answer.
So the following query seems to not work:
I don't know if is due to the fact that there are 2 WHERE clauses (I've removed one). Also as logic I don't know if it is intended to return what I expect.
Isn't the JOIN supposed to return only those that have ratings?
Thanx!
Reply:
Hi Alex,
Sorry it was a copy paste error. There should be ON instead of first WHERE. Regarding JOIN, the the original one indeed will get you only users with ratings. But LEFT JOIN is a special join to give you all users regardless.
As of your other question, remember those u and r used in the query? they are called aliases and intended to distinguish field names from different tables. So the final query would be
And, regarding your last comment, PHP is all right.
Hi again, (it seems that my first comment wasn't posted - It was submitted several minutes before this one: Alex, 21.02.17 01:36)
OK, thanks a lot for suggestions, I'll give it another shot during this night. Only one more question here:
Thanks a lot!
P.S. My first comment (really first comment that I cannot see it here / maybe it wasn't even submitted successfully) was about your suggestion between PHP / Java / Python for a good, robust, stable but flexible web application, based on your experience. Maybe also suggesting a set of "knowledge toolkit".
Kind regards, Alex
Hello again, How do you see a better implementation for this case, as migrating it from simple mySql to PDO is seems a lot slower: I have multiple users to retrieve from DB and show their information, but some information is stored in another tables, so then I have to run another query in the while loop, for each User ID
Example:
so the point here is that I'm assigning the details from DB to arrays of First Names, Last Names, User ID's, then Ratings and some other information that I need to retrieve from other tables based on the specific UserID. Is there a better, cleaner, faster way to do it, maybe not using so many arrays and using the $row['anythingHere'] variables and using it / displaying in the loop (not just storing / assigning in the loop and then use it later based on the array indexes).
Thanks a lot!
Reply:
Hello Alex!
That's a really good question. Indeed, there are means for the improvement, both for the performance and cleaner code.
From what I see, there are three main points
So it should be something like this
Now you can loop over single $users array and get any column. Fname, for example:
If the code still be slow, make sure that you have an index for the UserID field in AverageRating table (and in the all other linked tables as well).
Feel free to ask if you have any other questions!
Thanks it help me.thanks again if i had any inquiry regarding pdo i will back to this web again.
how suppose im write using pdo style for user redirect their respective webpage after login. example admin login will re direct to admin page and normal user redirect to user page after login. please show some simple example.
Reply:
Hello!
this question doesn't really belong to PDO as you have to deal with the information already fetched from the database. Assuming you have a variable
$rowwith user data where levelfieldis responsible for the user level, the code would beYou can add as many such condition as you like. Hope it helps!
how do i make something like this with pdo
$userquery = mysql_query("SELECT fname, lname, email FROM register WHERE email ='$login_session'") or die("the query could be fale please try again"); if(mysql_num_rows($userquery) != 1){ die("that username could not be found!"); } while($row = mysql_fetch_array($userquery, MYSQL_ASSOC)){
thanks i advance
Reply:
Hello!
With PDO this code will be much simpler:
Thank you! Great job. You helped me to understand better what I was just assuming as best practice.
How can I use PDO for this kind of statement:
Thx!
Reply:
Hello Andy!
Thank you for a real good question.
For PDO there is no simple solution. But it can be done with a code like this
Here we are using just a single $sql variable which is getting field names based on your conditions. Also, we are adding all our variables into
$parametersarray.Finally, we are adding VALUES part to the query, creating as many
?marks in the query as many items in the$parametersarray.And all we need now is to prepare a query and execute it with parameters.
Feel free to ask if you have any further questions!
Thanks a lot for this. As a beginner with PDO this taught me a lot.
I will definitely share your work with my classmates.
Cheers,
Thank you for this extraordinary work. I found your concise, well formed presentation of material an easy read and the explanations, examples and insights on technique to be extremely helpful. My gratitude to you for your work on and in, presenting this document.
Lots of useful details on PDO! I use wizzyweb as it automates the code creation for data entry and reports, even prepared statements for safer code.
Hello!
I was at the beginning of writing a complex tutorial on how to use PDO in polish, because there's not even one good tutorial about it and while I was researching for some resources other than PHP manual I encountered your The only proper PDO tutorial and I got an idea to translate it into polish, of course give you a proper credit and publish it on my blog (http://nastoletni.pl).
I'd be very grateful and I think polish beginners would be really grateful too for such a quality tutorial in such an important and trivial thing which is connecting to database in PHP.
Don't mind sending an email if you prefer it.
Best regards, Albert Wolszon
Reply:
Hello Albert!
Thank you for giving this article such a credit! That would be a honor for me if you translate it to your native language. Surely, I am giving you the permission to translate.
Please send me a link when it will be done!
Also, feel free to ask for clarifications, whenever you find it necessary.
Thanks for the the articles. Very educative even for experienced programmers. Keep up the good work bro.
Reply:
Hello Kalule!
Thanks a lot for your feedback! May I ask, what you find especially interesting in this article? and also, may be there are some parts that are not very clear or detailed? Please share your opinion.
In this example: $stmt = $pdo->prepare("DELETE FROM goods WHERE category = ?"); $stmt->execute([$cat]); $deleted = $stmt->fetchColumn();
Shouldn't it be rowCount instead of fetchColumn?
Reply:
Hello Danilcha!
Sure, that's my fault. Fixed. Thanks for pointing out!
Hi,
You seem to be very a skilled PHP developer, could you link me a good PHP tutorial so I can start learning the language the right way? Right now I have a website that I wrote from scratch using HTML, CSS of course and a bit of copy-pasted, slightly edited JS. The only PHP I'm using are include statements so that my pages don't become too hard to maintain. I'd like to be able to load images, table content and text from a database, dynamically creating a page the same way it's also done on forums and webshops. I understand that PHP 7 is relatively new so most of the tutorials out there are written for PHP 5.6 or older. Is this a problem or is the conversion to 7 easy?
Thanks,
Viktor
Reply:
Hello Viktor! Thank you for the good question.
The best resources to learn Php are http://www.phptherightway.com/ and http://www.laracasts.com/
As of the version, just don't worry: php7 is 99.9% the same as 5.6.
Feel free to ask other questions!
Just a note, if you are using an IN clause, and you need to ->execute more values (besides those in the IN clause), this seems like a clean way to do it:
Reply:
Hello Tyler!
Thank you for the suggestion!
Indeed, last time I've answered the similar question on SO, I tought I should add such an example but got busy and forgot it.
Yet I think that you made your example a bit overcomplicated. You need only one
array_merge()call:Please check the updated section: https://phpdelusions.net/pdo#in
Question(s) regarding "Mysqlnd and buffered queries. Huge datasets.".
With the mysqlnd driver, what does that mean for fetch vs fetchall?
Thanks.
Hi, If I wanted to save a single row from my database as a variable for later use, would this be the correct way to do it ?
Thanks :)
Reply:
Hi Joe!
You just have to read this tutorial a little further ;)
Depends on whether you're using a variable in your query or not, there will be either one line or three lines, not five. Just check the code by the link and choose the variant that suits you best.
Hi, I am following your code, however I am getting the following error.
Reply:
Hello Joe!
This one is very easy to fix. 5 years ago a new syntax for arrays was introduced in PHP - square braces instead of
array(). You can change your code as follows. But better consider upgrading your PHP to a more recent versionHope you get the idea
Hi everyone!
If you you wish to be able to full Unicode in MySQL database, I would read here on how to do that utf8mb4
Make your code appear like this:
Notice the "utf8mb4" instead of "utf8". 😺
Then you will be able to store these into your database.
Reply:
Hello PHP Ninja Cat!
Thank you for your suggestion!
[ Thank you for your reply dated 12 Dec 16] Hello again. After using the code above:-
and inserting the server ip address, my database user name, password, database name. I'm finding it don't work. Am I going wrong?
Would you write a sample code which will, Connect and open the database, create a table and finally insert data and close? Thank you.
Reply:
Hello Ian!
Well, this this is the exact code that should work, given you provides the correct database credentials. To avoid a confusion, please keep in mind the following:
Hello. Just a quick query, I'm currently teaching myself php, and started looking at database. I'd just like to ask, how do I insert using mysqli as the data is been displayed on the screen only, and not been inserted into the database? Secondly is pdo better, if so, how do I get started with it?
Thank You
Reply:
Hello Ian!
Thank you for your questions.
For the first question it's hard to tell, without a code. All I can tell is that you have to use a prepared statement for your query because it helps you to avoid many problems.
As of the PDO - you just landed on the exact page you need: this is a very good PDO tutorial. Feel free to ask any question you have.
I really like your page and whole blog :) everybody should use code like you explain. If you ever release a book i'll buy it :)
Oh wow didn't know about the trailing comma in the arrays, learn something new every day :D Great tutorial, very comprehensive and useful !
Reply:
You're welcome!
You have an error in one of your first snippets :
You have a comma after your last key-value pair! I'm sure that's not right :)
Reply:
Hello Sam!
Thank you for taking care, but there is no error: )
Unlike JS, in PHP it's pretty legitimate and hugely useful too, allowing an uniform syntax for all array members.
in the try-catch you allow, the
did not work for me as the value was 23000 or some such on a duplicate. What did work for me was
I'm running on a dell Inspiron 15 laptop under windows 10 using Chrome Version 54.0.2840.99 . PHP version: 5.6.21 MySQL 5.7
Reply:
Thank you for the report!
Although it's just to illustrate the handling process, I have to dig into these codes deeper. I had the same issue before but no time to investigate and develop the universal solution. Will add to my TODO list.
This is a great article, and I have bookmarked the site for future use. Having seen your comment about inviting corrections to your English (which is excellent, by the way), I have one that will improve readability in this article: in several places, you say "one have to", which should be "one has to". The verb "to have" is conjugated like so: I have; you (singular) have; he/she/it/one has; we have; you (plural) have; they have. Hope this helps :)
Reply:
Hello Ian!
Thank you for your kind words and a suggestion! Been busy recently, but finally got time to fix that.
That code is vulnerable to sql injection. you obviously havent tested it thoroughly.
I would like more on how to INSERT INTO a MySQL database. I am having trouble understanding some code I have gathered from the internet and the insert function of the DB.php after assembling the data keys and values as fields and placeholders calls the query function of the DB.php to do the insert as a Query. It then checks to see if an error occurred using
This is returning without any error but no data is inserted. What is the correct way using PDO to check for successful completion of the insert?
Reply:
Hello Alan!
Thank you for the good question. Although the example you posted is not a genuine PDO (there is no error() method), and thus I cannot comment on it, I can answer the question regarding error handling in general.
Most of time, you don't have to check for the success. Just write your code out of the assumption that everything goes well. So in your case you can just return true, unconditionally.
As of the possible failure, you don't have to write any dedicated handling code either. Let me explain. You see, an error message like "Sorry insert failed" exists only in schoolbooks. While for the real life sites it is agreed upon, that on any unexpected error your program should just halt. Given PHP can halt on error by itself, you don't have to write any handling code by hand. However, this halting process consists of several steps. I am still in the process of writing a whole article on the matter but in short:
Both actions (logging and showing an excuse page) can be set up in a centralized way, written only once, somewhere else in your code. Means for the moment you can keep your code as is, while by the time it goes live, you will be able to add whatever centralized error handling code you wish.
To let PDO halt on errors, just set it in exception mode is it's shown in the article above, and avoid catching exceptions right in place.
Please feel free to ask if something is still unclear to you or you have some particular scenario in mind you have to handle.
Thanks for the reply and the solution.
I really don't see the benefit of PDO. I've written almost 100 e-stores from the ground up with all SQL flavors. This just seems like a big headache and annoying. It's easy to prepare SQL statements and send them to a server. I personally don't think 'oh wow its gonna help my queries so much' ... I usually output the query in text to test it in a shell window.... and PDO is the type of thing that causes sites to crash , be slow, and be dumb.. because the developer put too much faith into it..! it just makes it harder to debug later....
Reply:
PDO doesn't cause sites to crash or be slow. You confused it with something else.
pdo o mysqli?
Reply:
Hello robot! :)
Here is an article for you, https://phpdelusions.net/pdo/mysqli_comparison
Interesting and logical points of view on this site, I like it.
I have a question regarding exception handling though: ok, it makes sense to let exceptions bubble up the call stack and use an application-wide handler, but what do I do if I need to show an error message in a certain place on the page?
For instance, I have a page that displays a menu at the top, and underneath that menu is the place where I want success/error messages to appear. If the user tries to perform an operation in that page, and the operation fails because of the db, how can I display an error message in the proper place on the page if I use a generic exception handler?
Reply:
Hello Ionut!
Thank you for a very good question.
Speaking of database errors, you don't actually show error messages for them. For the success message it's all right; For the validation errors it's all right; But for the system errors it is not.
For such a system-wide error like a database failure (or whatever else PHP error), it's better to show a static 500 error message. Because if your database failed, then most likely you won't get your page properly anyway - there are a lot of other queries that won't fire too. Just look at all these big boys - they don't show you anything like that on page reload.
The only case when such an error message can be shown is AJAX call. But again, for a server it's all the same: on failure it have to send an error HTTP status code and a static error page. Then either the latter will be shown (if requested directly) or the AJAX handler will consider the response status code (which is not 200) and show an error message.
If you still have any doubts, please provide a particular error example you are thinking of. It will make my answer more focused.
I was waiting for my comment to be approved by moderator as it has link of image file : explaining my Question in detail.
I really need answer for the question, can you please look into it.
Reply:
Sorry, but there was no comment with an image. Can you please try to re-post it? Or you can reply to the notification email directly, with your image attached.
"if you need to know how many rows in the table, use SELECT COUNT(*) query."
Partially false. First, don't take every column into the counting, it's worthless wasting. Second, make use of indexes, especially the primary key (or closest unique key of same purpose if no primary exists) like this:
Reply:
Hello Joni!
Thank you for your contribution. Unfortunately, both your statements are not true.
The first one is just irrelevant to the question. count(*) has nothing to do with selecting columns. When counting, your database actually doesn't select anything, it just counts.
The second statement is wrong too, just because your database is smart enough to pick up the appropriate index by itself. You can test it yourself, by running query
and checking the KEY section. There will be a key, if you have one defined in your table.
Hope it is all clear now :)
You can't seriously call this a "proper PDO tutorial" when you don't ever say what "PDO" means, not even once. I had to google it to find out that it means "PHP Data Objects", which doesn't appear anywhere on this page (except now in my comment).
Reply:
Funny, but you are right!
Dunno what practical use you can make from knowing that acronym meaning, or whether "PHP Data Objects" makes any better sense for you, but indeed you are right - I didn't bother to put it here. I will fix it, special for you!
I am glad it was the only factual error you found :)
Your post covers every thing about PDO and its execution, but i have following doubts:
eg :
sorry if query is not well formated
Reply:
The kind of SQL query is irrelevant to PDO: you can run any kind of query with PDO. You can run joins, sub-queries, stored procedures; set variables - whatever. PDO has nothing to do with your SQL - it will just relay it to database server.
Hope it's clear now.
You have a typo in the INSERT/UPDATE example:
should be
Reply:
Thanks a lot! Fixed.
I am not a robot >.>
Reply:
Neither I am :)
Hello
You say "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"
But $mysqli->prepare supports prepared statements. So prepared statements do not seem to be a reason to use PDO instead of mysqli.
Reply:
Thank you for catching that. Indeed the phrasing is not that clear. However, mysqli is not mentioned not implied here. This article is not about mysqli vs PDO comparison. It's solely about PDO, and it is expected that the reader is interested in PDO and this is why it mentioned here.
If you're looking for the article explaining why one should prefer PDO over mysqli, here is one: https://phpdelusions.net/pdo/mysqli_comparison
Hope it is clear now.
As requested, a correction to the last paragraph on your landing page. You can delete this comment after using it.
Text with improved grammar: Disclaimer: I am not a native English speaker and have never even attended an English class, so there are tons of mistakes in the text for sure - I beg your pardon for them. Please, feel free to leave comments with corrections.
Reply:
Thanks a lot! Corrected.
This is the best PDO tutorial I have found yet and I have SCOURED the internet. Your explanations are so much more logical than anyone else's and you have covered so much. Thank You.
If one should never use the rowCount() to count rows in database, what is the best way to handle pagination?
Reply:
Hello Tyler!
Thank you for the good question. Pagination is exactly the case when one should never ever use the
rowCount().By using this function you will ask a database to send into your PHP script all the rows found. It is highly inefficient and even may lead your server to failure.
Instead, just like it is said in the article, you have to ask a database to count the number of rows, by sending a query
and then reading that single result.
Feel free to ask if you have any further questions!
This site has taught me more than 100 others. Thanks for the clear explanation of these confusing points.
One comment, it might be helpful to absolute beginners like myself, if you showed both email and status in your placeholders example. It was a little confusing for me to make the leap from SQL to named placeholder substitution.
Reply:
Hi there!
Thank you a lot for the kind words, and - especially - for the suggestion. Indeed it was a stupid for me to omit status in the examples. Fixed now!
You're welcome to share any other suggestions or confusions - it will help me to make the site better for other people!
Merlin rolls a +8 fireball against the robot.
iRobot
I have tried everything to get a piece of code to work. Can I pay you to give me a solution that works, as well as review other pieces of code I have written. If you reply to my email I will send the code. Unfortunately the time I have spent trying to learn this stuff has been sucking up all my time when I should be doing other things that make money.
"Recently all PHP extensions that work with mysql database were updated based on a low-level library called mysqlnd, which replaced old libmysql client"
This means PHP 5.3+. The word "recently" should be replaced.
Reply:
Thank you for your suggestion.
I will try to re-phrase it, but the problem is that replacement is not permanent. There are distributions, where libmysql is used by default. Means one could still encounter with outdated setup. So, for the end user the change is still recent, in the meaning one cannot rely on mysqlnd for sure.
It did not work, beacause PDO::FETCH_UNIQUE does not work with PDO statement::setFetchMode, it affect only if use in fetchAll method :(
Reply:
Thank you for shring!
I will not delete your comments because it may help someone else. We all learn from our mistakes and sharing a mistake is as good as sharing an achievement.
I will add a clarification regarding setFetchMode() to the article.
Thank you for this finding again!
About previous comment Sorry it was my mistake. I use ORM over PDO and it did not work, but clean PDO is working. You can delete this and my previous comment
Thanks for great job But unfortunely PDO::FETCH_UNIQUE in mysql is not work as wrote in article. This options tell PDO return unique value not row indexed by key :(
Reply:
Such a use case (to get only unique values) would make a very little sense, as any database can return you unique values already.
Besides, all PDO fetch modes are irrelevant to database backend, working exactly the same way for all databases including mysql.
I tested this code many times and always get the result as shown in the example.
What is your setup? Do you have an unique value listed first in the field list in your query?
Hi, thanks for the excellent article. It gives some really helpful advice even for a little advanced developer.
Reply:
Thank you for your kind feedback! This kind of response helps to keep the thing going. Check back for updates!
Great article. It convinced me to switch from mysqli to PDO. There is just one detail that bother me, about the prepared statements and LIKE clauses. I may be wrong, but I'm pretty sure this don't work as intended:
PDO will escape the two extra '%' in $search2 as it will escape every other '%' that may have been already in $search. It's the same thing for this:
A proper way to handle this might be:
Reply:
This confusion is very easy to clear: pdo won't escape % symbol and has not a single reason to do so. Therefore all the above codes would work.
Your variant is perfectly legitimate too, but to me it's just too lengthy.
This is a test to see if i am a robot works
Reply:
Passed, of course. There is no armor to break.
I mean what do you think about using for simple task smth like http://stackoverflow.com/a/27826114/1767461
Reply:
Thank you for the suggestion.
There is a serious flaw in the answer by the link provided, as it doesn't sanitize the field names.
You may find a correct solution in this article, in the following section: https://phpdelusions.net/pdo#identifiers
Please ask if you find anything unclear or if you have any further questions.
Hi! I would like you to put some simple active record function, to update and insert assoc array
sorry, i wasn't clear. i was asking about what actually gets sent to mysql/maria (hereafter, myria). your comment about the artificial query made me curious about what PDO actually sends. i used wireshark to expose the conversation (WS is your friend :) and it appears the completely prepared query is sent to myria.
so, now, i'm a bit more confused - can you illucidate? (i'm really NOT trying to make a point here, it's just that inquiring minds need to know :)
Reply:
Got you. It's ok with your confusion. I understand the feeling. You just need to put all the tiles together. Either way, I don't mind.
If you are using PDO with default settings, then emulation mode is used, when PDO is sending a regular SQL with all the values put i the query. It occurs when execute() method is called.
While with setting PDO::ATTR_EMULATE_PREPARES to FALSE, the behavior changes: PDO sends a query with placeholders, when prepare() is called. So my comment on the artificial query is only applicable to this mode.
If you are already turned emulation off and still observing SQL with data substituted - then it is something unusual.
last question (promise) - you say that the query in mysql log is "an artificial query that has been created solely for logging purpose, but not a real one that has been executed" - exactly what is sent to mysql?
Reply:
Well, first of all please promise to ask more. When asking, you are helping me to make this article better.
When in native mode (i.e. when emulation is turned off) the exact query with placeholders is sent. I.e. if you wrote
SELECT * FROM t WHERE id=?
then exactly the same query is sent to mysql and executed. This is how native prepared statements are intended to work. You may read my answer with explanation here: http://stackoverflow.com/a/8265319/285587
thanks - in practice, never. but i ran across this catch-22 behavior experimenting with PDO in phpsh quite by accident. i thought there might be some way to recover, but, at the time, i had to exit phpsh then re-connect.
i take it that, short of using buffered queries (or script exit), using PDO::query('SELECT something') will get you into a position where you can't recover, since there's no PDOStatement on which to do a fetch/fetchAll(). or did i miss something? i assume the situation clears on script exit and/or connection loss.
tnx for the write up, both here and in SO - i refer to your thoughts frequently.
Reply:
Yes, you are right. Everything that was associated with the connection (unfetched resultsets, prepared statements, transactions etc.) will be cleared up on the connection close (and PHP will close a connection on exit).
However, I am not quite getting your point, why would you run a query without assigning the result to a variable.
You have an error in text: for all undefined properties get magic method will be called if there is no get method in the class, then new property will be created
PDO doesn't execute get, instead it calls set to set values. Cheers ;)
Reply:
Thanks a ton! Fixed!
Thank you so much for this brilliant document. It really covers most of the things in an inspiring way.
Reply:
Thank you for your feedback!
If you have any questions, I'll be glad to answer, as answering questions lets me make the article more clear and useful.
One other downside to unbuffered queries is you can't run any other queries until the current query has returned all of its results. That's why it is more convenient for the user to have PHP retrieve all the rows of a dataset before iterating over them - doing so allows other queries to be run inside the while loop.
Reply:
Thanks a lot for the reminder. Surely it's a really important issue. Added it to the text.
Yes great and simple.
Great article - love the gotchas at the end. I would love to see an addition dealing with the INSERT... ON DUPLICATE UPDATE procedure (which I'm currently exploring) and how the single statement would take x number of bound variables on the INSERT with additional variables if the UPDATE procedure is sent.
Reply:
Hello!
I am in doubts whether to add on duplicate case or not, as it's not directly related to PDO. Either way the answer is sumple.
This kind of query has a neat feature exactly for your case. You can refer to an insert part value using values() function (which is different from regular values()):
Insert into t (foo, bar) values (?,?) On duplicate key update bar= values(bar);
So you have to bind your variables only once. Hope you get the idea.
Either way feel free to ask any questions!
This tutorial is a hackers wet dream. The statement $pdo = new PDO($dsn, $user, $pass, $opt);
could possibly dump your complete login details (server, user, pw, etc) if there is any issue with connecting to the db (timeout or whatever). This info is contained in the exception. I stopped reading after that statement, I don't care if it is mentioned later on, YOU SHOULD NEVER send that command naked. I know what you are going to say later on but what I wrote is 100% true and I tested it.
No wonder php has such a bad reputation.
Reply:
PHP has its reputation due to users. Who is ignorant about most basic PHP settings. display_errors for instance. Which have to be turned OFF on a live site - a PROPER way for hiding error messages.
ALL messages, mind you, not only one you've accidentally became aware of.
Thanks for the great article! I've been modifiying my code already!
A few things:
Hi, I'm kinda new to PDO and would like some advice.
Right now I have three different tables in three different & separate databases (2 PGSQL, 1 MSSQL) that I have to INSERT into for user registration. This means I have 3 PDO connections, now all three INSERT are a bit different:
SQL 1: INSERT into user (Name, Password, Age) SQL 2: INSERT into users ([LOGINNAME], [PASS], [OCCUPATION]) SQL 3: INSERT into users (Name, Address, Position)
Now what I want to do is to somehow rollback the INSERTs if one or more fails to insert, and commit only if all three can be inserted? Can I rollback or commit outside of the try catch? or do I put all of them in one huge try catch block? or is there a better way that I can approach this?
Reply:
Hi!
I am afraid I can't offer anything better.
It's impossible to have a proper transaction for the separate connections. If you commit outside of try catch, there could be an error during commit itself - yet you'll be unable to rollback.
But that's better than nothing - so, I think that you have to commit outside of try catch. You may also want to add another try catch to wrap commits, and try to recover successful inserts somehow.
Well done tutorial especially for who is new to PDO.
I use it as OOP mode on base class, way easier that way :P
Please tell about PDO::FETCH_CLASSTYPE, it comes handy when you store objects of different subclasses in a single table
Not covered topic with inserting batches. E.g. INSERT INTO MyTable (col) VALUES (1),(2),(3); AND INSERT INTO MyTable (col) VALUES (1),(2),(3),(4); Is two separate prepared needed? $q1 = $pdo->prepare("INSERT INTO MyTable (col) VALUES (?),(?),(?),(?)"); $q1 = $pdo->prepare("INSERT INTO MyTable (col) VALUES (?),(?),(?)");
nice job ripping the design from SO
Reply:
You meant bootstrap and PHP.NET? ;)
@Ry nothing wrong with doing so, if you actually handle the error. If you are just echo'ing out the error message, you are better off not catching anything at all, you will get more information that way. And you can then turn off display_errors in production.
great article, but what's wrong with wrapping pdo code by try - catch? I thought it was the way one handles exceptions in PHP?
Reply:
I completely rewrote this section. Hope now it's clear.
Thank you
This article pretty much clarified the proper useage of PDO, but sometimes it got over my head. old habits from mysqli_*
I'm trying to go from old, raw mysql to abstraction. PDO looks like a good path. Thanks !
I am robot
good