Note: The exercise files are free to all members. The code is commented to enhance your learning, but you will need database connectivity for some files to run as intended.
Skill Level Intermediate
- [Instructor] Hi; I'm David Powers, and welcome to this week's edition of PHP tips, tricks, and techniques designed to help you become a smarter, more productive PHP developer. This week's tip is a quick and easy one, but it's one that can help preserve your sanity. I want to show you how to debug a PDO-prepared statement. For this, you'll need to be using PHP 7.2 or later. In this file, I'm using a PDO-prepared statement with a named parameter like this to query a database.
The exercise files for this video contain a copy of this script, but not of the database or the database connection. The technique I'm going to demonstrate is very simple, so you can work with a database of your own. This works not only with named parameters; you can also use question marks as placeholders in the prepared statement. For demonstration purposes, I'm hard-coding the value of year, here on line three. Normally, this would come from a dynamic source, such as a query string.
The script prepares the statement, binds a value to the named parameter, executes the statement, and in the While loop, it displays the results. Finally, on lines 17 through to 20, we check for an error message using a Ray-D referencing. The PDO-statement error info method returns under Ray. If there's a problem, the third element, in other words the element at Index Two, contains an error message in plain English.
If there are no errors, this third element is empty. I can already hear some of you yelling that there's an error in my script. I know; it's deliberate, but let's just run this script by loading this page into a browser. I get a completely blank page. In my testing environment, error reporting is set to it's highest level. Display errors is set to On. The Error Info method hasn't returned an error message.
That tells me, obviously, nothing is wrong; the prepared statement was executed correctly, but I know there should be several matches in the database and I've got no results. Using prepared statements with named parameters or question marks as placeholders is a really secure way of embedding variables in an SQL query, but it can be really frustrating trying to work out why a query produces no results. There's no point in using Echo with this here; that'll simply give us the SQL statement with the named parameter, nor can we use Echo after binding the value to the named parameter.
A PDO-statement object doesn't have a To String method. If we try this, we'll get a fatal error, but a change in PHP 7.2 means we can now finally inspect what's actually sent to the database using the PDO-statement's Debug-Dump Params method. Down at the bottom of the script here, first I'm going to Echo some pre-tags; that will simply make things easier to read.
Then, we have our statement object; we invoke it's Debug-Dump Params method. We don't need to use Echo with that, because it is actually going to dump those params. Then, we just have closing pre-tags there. If I save that and refresh the browser, now we get the output and there are two versions of the SQL. The first one contains that named parameter, but the second one is called Send-SQL; that contains the actual value that is being sent to the database.
I can see that the value that has been bound is null. That gives me the vital clue that there's something wrong with the value that I'm trying to bind. If we go back to the script here, I'm binding Yearmade to this named parameter, Yearmade. That seems to make sense, but if we go back up to the top, ah-ha: the value here is Year, so we need to change this one here to Year, as well. If we save that then refresh the browser, there are the results I was expecting to get and here are the two versions of the SQL; that's the one with the named parameter.
Down here, I can see that the value 2008 has been correctly bound to that parameter. That's how to debug a PDO-prepared statement. All you need to do is to invoke the Debug-Dump Params method on the statement itself. There are just a couple of points to note. To see the bound values, you must call the method after the statement has been executed. Calling it beforehand or without executing the statement, simply shows the original SQL with placeholders.
The other point is that you must be using PHP 7.2 or later. The Debug-Dump Params method exists in earlier versions of PHP, but it doesn't show the SQL with the bound values; it only shows the first SQL. That's the one with the named parameter in there, but you don't get the one with the actual bound value, so it's not really very much use. It's only a small improvement, but for me at least, it's made debugging PDO-prepared statements much, much easier.
That's all for this edition of PHP tips, tricks, and techniques; I hope you found it useful. Thanks for watching.