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.
In addition to the query method, MySQLi has the rather oddly named real query method. With the select statement, the query method executes the query and returns a MySQLi result object, with the result set stored in PHP memory, ready for use. The real query method on the other hand, simply submits the query. You need to retrieve the result set separately. This is mysqli_real_query.php, which you can find in the chapter 7 07_02 folder of the Exercise Files.
And we'll use this to experiment with the differences between query and real query. At the top of the page, there are two select queries that are executed one after the other using the query method. And the first result is stored as result. The second one as result2. Down in the page, two while loops display the different results. And if we load that page into a browser, we can see the two different result sets being displayed. So, let's change the first one to real query.
So we need to scroll up, and then on line five, we'll change query to real query. Save that. And then, reload the page in the browser. And we get this message. Error generated by second query. Commands out of sync, you can't run this command now. This message is coming from the second query, so there's obviously nothing wrong with the first one. So, let's comment out the second query and it's related error message and try again.
Let's go back to the code, comment out lines 9 to 12. Save and reload. This time we get fatal error. Called to a member function fetch a sock on a non-object on line 30. So, let's see what's happening on line 30. And line 30 is trying to use result to call the fetch a sock method. So if we go up here, result is coming from real query. But if that's not containing the results set, what does it contain? It's actually a boolean true or false.
When you use real_query, all that it returns is the boolean that tells you whether the query was executed successfully. To get the results set, you need to call another method on the database object. So let's remove result from there, and then on the next line, result equals then our database object, and we call the use_result method. So, let's save that and reload the browser. This time we are getting our first result.
The error messages that we've got down here, are related to result2, which is currently commented out. So, let's go back and un comment that, and see if it makes a difference. When we reload the browser, we've got that second error message again. Commands out of sync, you can't run this command now. What's going on is that real_query is unbuffered. The results from real_query are still on the database server. So, no further queries can be submitted until all the results have been consumed.
So, there are a couple of ways in which we can solve this problem. Let's go back to the code. And one way is to cut this second block. And make sure that it doesn't run until the first set of results has been consumed. So if we put a new PHP block after the first set of results is used, paste that in there. Save and reload the page. Everything is now being displayed correctly. We've consumed the first set of results up here, the unbuffered results. And only after that, did we try to execute the second query.
The alternative is to change use result to store result. So let's go back and try that. We'll select all this code here, cut it. We don't need that PHP block anymore. And move the second query back to its original position. And then change use result to store result. And if we save that, and reload the browser, both results are now being displayed. So, why does store result work but not use result? Well, let's go back.
Actually, what is happening is that we've now created a buffered query. These two lines together five and six have exactly the same effect as using the query method on its own. So most of the time, you'll want to use buffered queries. And for that, it's much simpler to use the query method. However, if you do have a very large results set, using an unbuffered query consumes less memory. For an unbuffered query use the real query method to submit the query, and then call the use result method on the database object to start using the results.
But remember that unbuffered queries have several restrictions. You can't reuse the result set without submitting the query again. There's no way to find out the number of rows returned by the query, and you can't run any further queries until the unbuffered results have been fully consumed or you clear the connection. We'll be looking at three resources that are no longer needed 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.
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.