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.
When you run a select query, it's useful to know how many results have been returned. If there are no results, you need to display a suitable message, or to hide parts of the page, or both. Some databases like MySQL, report the number of rows in a result set. But most don't. We look first at how to get the number if it's reported. Then we'll look at a technique that works in all databases. This is pdo_rowcount.php which you can find in the chapter two, ch02 > 02_05 folder of the exercise files.
The page has a select query. It executes it, and then the results are displayed in a table. The PDO row count method, returns the number of results in a results set, if it's reported by the database. So let's go back up to the top. There is our result set there. So if we create a variable called num_rows, and then use the result object, and call the row count method, that should give us the number of results if it's reported.
And we can then display that value, just before the table here, we'll put us in the end of the page, PHP block building on line 24. So if I save that page and then, load it into a browser, there we've got total results found zero, but clearly we have got results. So the implication of that is the database that I'm using, doesn't report the number of results from a select query. Let's go to the editing program and open pdo_connect.php in the includes folder.
The DSN that I'm using is for SQLite, it doesn't report the number of results in a result set. So let's comment that line out, and switch to MySQL. Save that and then Refresh the browser. And this time we've got the correct number of results being reported. That's all well and fine if you're using MySQL, or another database that does report the number of results. But it's not portable. We need to make this portable. So to do that, let's go back to the editing program.
And, scroll up to the top here. Before running this select query, we need to run another select query, to find out the number of results that we are going to get. So let's add another line. We can create this directly. We'll store the result as count, and then it's the database connection. On the query method and we'll type the select query directly in there. Hit Select, and then the count function, followed by parentheses and between those parentheses, an asterisk.
You must make sure there is no space between count and the opening parenthesis. Then we need to get the table we're looking at, so it's from names and we don't have any so this will just get us the total number of rows that we've got in the names table. Select count will produce only one result, so we can get that using the fetch column method. And we'll store that as num_rows.
So, there is our count result, and we use the fetchColumn method. That gives us the number of results. We don't need it down here. So if we save that page and then Refresh the browser, still using MySQL. We're still getting the correct number of results using that different method. Let's go back and see how it works with SQLite. So, if we comment out the MySQL DSN and use SQLite, Save, Refresh the browser, we're now getting the correct number.
With both methods. So this is the portable way of doing it to use select count. Now you've probably noticed that what is happening here is that we're running two select queries. But if num_rows is zero, there is no point in running this second query. So we can wrap these lines here, in a conditional statement. So if num_rows, if there are any results, that will equate to true. If there are no results, it'll be zero, which equates to false.
But if there are no results then result won't exist. So to prevent any errors lower down, we need to have a conditional statement down here as well. So, also if num_rows, opening curly brace there and we wrap the whole of the table that displays the results. In that conditional statement so the closing curly brace goes after the end of the table. So, let's test that by adding a where clause.
Scroll back up to the top, we need to add the where clause to both of our SQL statements. So, the first one is where clause, where name equals David. I know there is no David, in the database. And then again, where name equals David. And if we Save that and Refresh in the browser, this time we get, total results found zero, which is correct.
We also don't see the table. We don't see any error messages because the result hasn't been found. So the portable way to find out the number of results in a result set, is to run two queries. The first one running select count, and then the second one is the query getting the results that you actually want. In this example, I've used the query method and stored the results as result. An alternative way to do this, is to use a four each loop. So basically here, the SQL is written up here and then a four each loop is used to run that down there.
This is the same technique of looping over a select query directly with a four each loop, as was described at the beginning of this chapter. It's just an alternative way of doing it. But in both cases, select count is used to find the number of results to expect. But if you're not interested in the actual number of results, but only want to know if there's at least one, there's no need to run to select quarries. And we'll deal with that next.
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.