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.
PDO has four different methods for fetching results from a database query. The one you're most likely to use is fetch, which gets the next row from a results set. FetchAll gets all the results at once and stores them as a multi-dimensional array. FetchColumn gets a single column from the next row. You specify which column you want by passing the method, the column number counting from zero as an argument. If no argument is passed, the first column is returned.
FetchObject returns the next row as an object. We'll look at creating objects from database results in chapter four. In the meantime let's concentrate on the first three methods. This is pdo_fetch.php which you can find in the chapter 2 02_04 folder of the exercise files. The try block at the top of the page includes the database connection on line three, then on line four we've got a SELECT query, which is stored in a variable called $sql.
We need to execute the query and store the result set. So we can do that on the next line. We'll create a variable called $result. And then we use the database connection and call the query method. We pass at the SQL statement, the query, and this then stores the entire result set in $result. To be able to display that, we can do that one row at a time using the fetch method in a while loop. So, let's scroll down between the two table rows and add a new line on line 29.
This needs to be a php block. Use a while loop. We need to store each row, so we'll it call row. Then the results object. And call the fetch() method. You can see that my editing program is asking me if I want to set a fetch_style. The way in which you set a fetch_style is by passing a PDO fetch() constant as an argument to fetch(). I'm not going to set a style at the moment, we'll look at some of the options in chapter 4, we'll just used the default for the moment.
So let's just add a curly brace, and we need to close the PHP block there and then put the closing PHP brace at the end of the table row. We're using the fetch method in it's default style. This returns the current row as an indexed array and as an associative array. So you got the choice of using the column number, counting from zero, or the column name to display the values. So, let's do that. In this first table cell we'll have a PHP block and we'll use the first row so we're counting from zero.
So row zero. And then in the next one, let's use a column name. Second value was meaning. And then if we copy that we can also use the name of the column for the last one. That's gender. So if we save that, and launch the page in a browser, there are our results displayed. We used the column number for Name and we used the column names for Meaning and Gender but they're all displayed as normal. So let's return to the editing program and take a look at fetchAll and see how that works.
If you open pdo_fetchAll.php. This contains exactly the same SQL statement, the select statement, and it also submits it using the query method, and stores the value in Result. The Fetch All method gets all of the results together and returns them as a multi-dimensional array. So let's do that. We'll save it as $all. I will use the result object and the fetchAll method.
Add in the HTML body of the page. There's a couple of pre-tags, so between them if we have a PHP block and use print_r, we can inspect the contents of that all multidimensional array. So we'll save that and then view the page in a browser. And here's that multidimensional array. The top level array elements, they're the rows. And each of the sub-arrays.
Notice that it contains each value twice. First with the name, and the second time with the column number. When using fetchAll you're unlikely to want both the associative array and the indexed array. So let's go back to the editing program and see how we can alter that. Scroll back up to fetchAll. We can pass fetchAll and argument. We can use a PDO fetch constant. So, it's all in caps. PDO then two colons and then FETCH.
And if we use FETCH_ASSOC and save that, this will give us an associative array. So let's go back to the browser and reload that and now we've just got the names of the columns. And the alternative to FETCH_ASSOC is FETCH_NUM, and no prizes for guessing what this does. It returns them as numbers, the indexed array. So now a quick look at how fetchColumn works. pdo_fetchColumn.php, executes exactly the same query, and then down here fetchColumn is used and it just displays the column.
So, if we display that in the browser, the first column has been displayed. That's default behavior of the fetchColumn() method, it always returns the first column. But you can change which column is displayed by passing the column number as an argument. So if I pass 2 as the argument and save that we should then get the gender. Let's go back to the browser and reload that. This time we've got the gender. And if I change it to 1 and reload, this time we've got the meaning of the name.
The important thing to remember about the fetchColumn method is that the value passed as an argument must be a number counting from zero. You can't use the column name. Another thing you needs to be aware of is that you can't access another column from the same row. Of the three methods we've looked at, fetchColumn is probably the one you'll use least. The fetchAll method is useful when you want the complete result set as an array. By default, fetchAll returns each result twice, first with the name of the column, and the second time with the number of the column.
If you want to change that, parse PDO FETCH_NUM or PDO FETCH_ASOC as an argument to the fetchAll method. And finally, the fetch method is the most widely used, and we'll be coming back to it frequently. This returns one row at a time.
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.