Ready to watch this entire course?
Become a member and get unlimited access to the entire skills library of over 4,900 courses, including more Developer and personalized recommendations.Start Your Free Trial Now
- View Offline
- Connecting to a database with PDO or MySQLi
- Fetching a result set
- Executing simple non-SELECT queries
- Sanitizing user input
- Binding input and output values
- Passing an array of values to the execute() method
- Working with advanced PDO fetch methods
- Executing a MySQLi transaction
- Freeing resources that are no longer needed
- Submitting multiple queries
- Creating an instance of a class from a result set
Skill Level Intermediate
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.