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.
Sometimes you need to reuse a result set. There are several ways of doing so. Chosing the right one, depends on the database system you're using. This is pdo_reuse.php which you can find in the chapter 04_04_04 folder of the exercise files, along with the other files used in this video. The try block at the top of the page, submits a select query, and stores the result set in result. In the body of the page, there's a while loop on lines 27 to 29, that uses the fetch column method to get the values from the first column, and displays an unordered list of links.
Further down on line 33 to 36, there's another while loop, that uses the same result object, to display a definition list. So let's load this in a browser to see what happens. The unordered list is displayed correctly, but the definition list, isn't. This is because a result set uses an internal cursor to keep track of the next row. Displaying the unordered list puts the cursor at the end of the result set. And before trying to use the results again, we need to move the cursor back to the beginning.
In PDO, if you're using a database such as post PostgreSQL that supports scrollable cursors, there's a way of doing that. Let's see how you do it. We need to open pdo_scrollable.php. And on line six, the setAttribute method has been called on the database connection object, before submitting the query. And this sets the cursor to scroll. The set attribute method, takes two arguments, both of them PDO constants. The first one is the type of attribute that you want to control, in this case cursor.
And the second one is what you want to do with it, in other words, make the cursor scroll. So, once you've got a scrollable cursor, you can reset the result set before reusing it. Let's see how that's done. We need to scroll down to the bottom. And here, on line 36, the fetch method is called with three arguments. The first argument is a PDO constant which sets it as an associative array, sets the result set as an associative array. The second argument, sets the cursor.
And what this is does is it sets the orientation of the cursor, to an absolute position. And the final argument is the position you're setting it to, which is zero, in other words, the beginning of the results set. So this resets it back to the first row. The first row is stored as row, so you need to use a do while loop. And then the while condition also uses the fetch method, this time with the argument for the associative array, and the second one to fetch the orientation next. The problem with using this method, is that it doesn't work in all databases. So it's not portable. Let's just try this. I'm currently using SQLite. And again, we've got the unordered list, we don't have the definition list.
This won't work in MySQL either. So let's see if we can find an alternative solution that will work in all database systems. Go back to the original page, pdo_reuse.php, and the very simple way to do this, is to store all the results, as an array, so after getting the results set, we'll add a new line on line six. And we'll call the new array names. And we'll use the result object, with the fetchAll method.
By default, fetchAll returns each value twice, indexed both with a number and with a column name. Because we're getting a complete array, we need to keep memory use to a minimum, so it's a good idea to specify just one type of array. So we'll get an associative array. We do that by passing a PDO constant as an argument to fetchAll. So PDO, then two colons, and FETCH_ASSOC. So, all the results in the result set are now in an associative array called names.
So, we need to change the way in which everything is displayed. So, I'll go down to the first while loop, and we need to replace this with a for each loop. Replace that entirely, for each. And the array that we're going through is called names, and we'll refer to each item in there as name. So, instead of col here, we need a names array, and it is the name element that we want from the names array.
And the same here. I've just noticed I've got names there. It should be the alias that we're using, so it should be name, name. Looks a bit strange, but this will work. And then to reuse, we need to change this also so a for reach loop. But arrays work in exactly the same way that they've got a sort of internal cursor. And we need to reset the array before we can use it. So let's add a new line here. And we use the reset method, this is a PHP function. And we just pass it the array that we're working with, so it's names. And this will automatically set the internal cursor for the names array back to the beginning.
So, this needs to be, again, for each names is name and then instead of row, we are using name. And again here. And in the last row. So, if we save that, go back to the browser, go back to this PDO reuse page, and refresh it, we now get the definition list at the bottom of the page.
So this works very well. It's totally portable. There's only one potential disadvantage, and that's that it will tie up a lot of memory, if you're working with a very large dataset. So, there is another way in which you can approach this. If we open pdo_resubmit.php, here on line 35. The query has simply been submitted again. So, the result set is available for a second time. I won't bother to go through all the code. It's extremely simple, and you can see it for yourself, in the exercise files.
But, if we load this page into a browser. We get the unordered list at the top, and the definition list at the bottom. So it works just fine, the only minor disadvantage, is that it does involve a second round trip to the database server. So, to reuse a result set, you have three options. The first one that we looked at, was using a scrollable cursor, but this works only with some databases. The other one is to use fetchAll to get everything as an array.
And finally, to resubmit the query. And with that, we come to the end of our review of accessing databases with PDO Database Abstraction layer. The remaining chapters, are dedicated to the object oriented interface of MySQL Improved, which works only with MySQL and MARIADB
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.