Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
We've created a prepared statement that uses placeholders to insert values from an online form into an SQL query. To make it easier to handle the result set, we can bind the results of each column to named variables. So, instead of using the get_result method on line 17, we're going to use the bind_result method which takes as its arguments, the variables you want to bind the results of each column to. So, we'll get rid of this and it's our statement object and the bind_result method that we need.
The number of arguments passed the bind_result must be exactly the same as the number of columns in the result set. And they must be in the same order. So you should always declare the column name specifically in the SQL when binding the result. So, let's see. We need one for make, yearmade, mileage, price, and description. They have to be in that order. We've already got a variable called make, so we'll bind the output to maker. Then year, miles, price, and desc.
price, and desc. So, after binding the output like this, we need to make a few more changes to the rest of the script. So, scroll down. And the first change needs to be made here on line 64. The original code was using a mysqli result object. And this has got a num_rows property. We're no longer using that result object, but the statement also has a num_rows property, so we can change that from result to statement, and then further down, originally we were using the result object to fetch each row into an associative array.
we were using the result object to fetch each row into an associative array. That's no longer necessary. So let's change that. And what we need to have is the statement object. And that calls, not fetch_assoc, but simply fetch. And we can now change these variables down here to the variables that we created just a moment ago. So, instead of row make, it becomes maker, and instead of row yearmade, we use year.
Row mileage becomes miles. Row price is price and then row description is simply desc. So, we can save that and test it in a browser. Let's search for some cars that we know are definitely there, I know there are Ford in this table, so let's search for that. And we've got no results found. So, what's gone wrong? Well, by default, prepared statements return unbuffered results, and the problem lies with the number of rows that are being reported.
Lets go back to the code. And up here on line 64. We're accessing the statements num rows property, but because it's unbuffered, it doesn't know how many rows there are. So, what we need to do is to store the result first. So, we call the statement object's store_result method. Save and go back to the browser. Search for ford again. And this time, we get the results that we expected. So, it's important to know that if you're trying to find out the number of results from a prepared statement, you must store the result before you can access the num_rows property.
result before you can access the num_rows property. So, let's just review what we have done. Instead of getting the result directly, and storing it in a mysqli result object, we use the bind_result method on the prepared statement object. That requires as its arguments the variables which you want to bind the results of each column to, and the number of arguments passed to bind_result must be exactly the same as the number of columns in the result set and they must be in the same order.
And if you want to find the number of rows in your result, you need to store the result first using the store_result method, before you can access the num_rows property of the statement. And then, you use the statement's fetch method to fetch the results. Binding the results of a prepared statement to output variables is particularly useful when you've got a lot of columns and you want to embed the values into double quoted strings. It's a lot easier than working all the time with an associative of indexed array.
In this example, the prepared statement has been used only with a select query, but prepared statements can be used with any other type of query including insert, update, and delete.
Get unlimited access to all courses for just $25/month.Become a member
61 Video lessons · 96929 Viewers
56 Video lessons · 110610 Viewers
71 Video lessons · 79436 Viewers
131 Video lessons · 38119 Viewers
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.