Viewers: in countries Watching now:
Now that PHP has true object-oriented capabilities, it's best practice to access databases using PDO (PHP Data Objects) and MySQLi. These methods produce database-neutral code that works with over a dozen systems, including MySQL, SQL Server, PostgreSQL, and SQLite. Learn how to use PDO and MySQLi to perform basic select, insert, update, and delete operations; improve security with prepared statements; and use transactions to execute multiple queries simultaneously. Author David Powers also covers advanced topics like instantiating custom objects, and compares PDO to MySQLi so you can decide which method is right for you.
Let's see what happens when there's an error in your SQL and how to get information about the problem. This is pdo_error.php, which you can find in the chapter two, 02_08 folder of the exercise files. On lines four and five, we've got a select query, then it's submitted using the query method on line 6. And lower down on line 30, we've got a while loop which uses the fetch method to get each row and then that is displayed in a table.
The code is identical to pdo_fetch.php from earlier in this chapter, but there's a mistake in the SQL. So let's see what happens if we load it into a browser. We get this fatal error message, Call to a member function fetch on a non-object. Well, that's not really very helpful. It's technically correct, but it doesn't tell us what the real problem is, so let's go back to the editing program and take another look at the script. So if we scroll up to the top, here on lines 7 to 9, we've got a catch block.
This is meant to catch any exceptions. So why hasn't it caught the problem? Well, the answer is that the PDO class hasn't thrown an exception. There's nothing wrong with the PHP code. The error lies in the SQL. And to find out what the problem is, we need to use the errorInfo method on the PDO connection object. So let's add a new line after we have submitted the query. We'll save the result as errorInfo. Then we need the connection object and call its errorInfo method.
And this returns an array, so we can inspect the contents of the array by using print_r. So let's save that, go back to the browser, and refresh the page. We're still getting this fatal message down here, but we've got this array up here. The first element is SQL state. The second element is an error code. The third element, no such table nams, really tells us in plain English what the problem is.
So let's go back and change that table name. It should be names, and we'll save that. And we'll keep our info here, we'll keep the print_r. Go back to the browser and reload. This time, everything is being displayed correctly, but we can see that the third element of that array is empty. In fact, both the second and the third elements are empty, but it's only the third element that we're interested in, the one that tells in plain English what the problem is.
So if we check for the existence of the third element in that errorInfo array, we can use that as a condition to decide whether to display or not. So let's go back and instead of this print_r here, we'll replace that with a conditional statement if isset errorInfo, and then it's the third array element, so the index is 2. We can then assign that value to our error variable. So if that third element is set, error is now set here.
And down here on lines 24 to 26, we've got a conditional statement, if isset error, that displays it. We can use that to prevent the rest of the table being displayed if there is an error, so we can put the rest of the page into an else block. And we need to go down to the bottom of the page after the table and put the closing curly brace there. And if we go back and reintroduce an error into our SQL, let's do that.
Go back to the browser, and reload that page. No such column gende. And nothing is displayed here. We don't get any error message down there, so that is really working very nicely. And if we go back and correct that problem, save and reload, then our display is correct. There is an alternative way to get error messages and that's to change the error mode so that PDO throws an exception when there's a problem with the SQL, and I've got an example of that.
That's in pdo_exception.php, which you can find in the same folder chapter two, 02_08. This contains exactly the same error as before in the SQL. To change the error mode, we need to insert a new line immediately after the database connection has been included, so that's up here on line 3. A new line on line 4. So we need to have our database connection object, which is db, and then use the set attribute method and this will take two values, both of them PDO constants.
The first is the attribute that you want to set, so that's PDO, then two colons, and its attribute error mode. That's the one down there. And then the second argument is PDO, double colon, and then the error mode that we want is error mode exception. This page already wraps the rest of the page in the else block, so we can now just save that and display it in a browser. And here we get SQLSTATE, the SQL error number, General error 1, no such name, nams.
So this is, in fact, exactly the same information that you get from errorInfo, but it's all as one message. So to find if there's a problem with your SQL, you have a choice. You can either set the error mode to exception, as we've done here in pdo_exception.php. You set attributes, then you set the PDO attrib, error mode and PDO error mode exception, and then it is caught by the catch block. But what I think is probably a more user-friendly method is the one here in pdo_error.php.
Immediately after submitting the query, we call errorInfo method on the database connection object, store that as an array, and if the third element of the array is set, that is the error message that tells you what's wrong with your SQL. And if the third element of the array is not set, you know that there's nothing wrong with your SQL, so you can display the results without any problems.
There are currently no FAQs about Accessing Databases with Object-Oriented PHP.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.