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.
We've already initialized the statement on line ten, and prepared it on the following line. So we can now bind values to the placeholders in the else block. We do that using the bind_param method of the statement object. So, statement, and the bind_param method. The first argument of bind_param needs to be a string representing the data type of the value being bound to each placeholder. So, let's pause a moment to see how MySQLi expects the data types to be specified. The data type of each value is represented by a single character. I for integers or whole numbers, d for numbers with a decimal fraction, technically the d stands for double. S for strings and b for binary large objects, such as files or images. So we need to build a string to pass the bind_param using the appropriate characters. So let's get back to the code. The where clause in the SQL has three placeholders.
One each for the make, year made, and price columns. So the first argument to bind_param needs to be a string representing the data type that we want to pass to those columns. Make needs to be a string, so that's S. Year made needs to be an integer, so that's I. Price contains a decimal point, so that's a double D. Then the remaining arguments that are passed to bind_param are the values to be bound to each placeholder. It goes without saying that they need to be in the same order as the place holders, and they must be exactly the same number. We're using the like operator for the make column.
So the value that comes from the form needs to be wrapped in percentage signs as wild card characters. But the values that you passed to bind_param must be variables. You can't use actual values or expressions. So we'll need to create a variable for make. So we'll just call it simply make. The next value will come directly from the form, so we get that from the GET array, GET year made. And the last value also comes directly from the form, that's GET price.
Now, we need to define make. It doesn't matter where you define it as long as it's done before you execute the prepared statement, so we can create that on the next line. Begins with a wildcard character, %, then the value from the form GET make and a wildcard character. Now that we've bound the values to the placeholders, we can execute the statement using the execute method. Although this executes the statement, it doesn't actually retrieve the result set.
To do that, we need to use the GET result method. So we'll assign that to result, and this returns on MySQLi result object in exactly the same way as if we had submitted the query using the query method, and if we scroll down we can see that in the original code, we make reference to the result object to get the number of rows in the result. And also down here in the while loop, we use the fetch sock method to get each individual row and then display it. So, if we save that we can now test this in a browser. So, let's some try some cars.
Let's see, ch, search. Yes, we are getting Chevrolet and Chrysler. So, the results are being filtered now on the basis of what has been submitted from the form. So, let's review how we use the prepared statement to embed from the user input into an SQL query. We began by adding a where clause to the SQL on line eight and using question marks as placeholders for the input values. It's important to note that the placeholders are not wrapped in quotes even if the value is a string. The prepared statement takes care of quotes automatically. Then on line ten, we initialize the statement, and on the following line, we parse the SQL to it's prepare method. This checks that the syntax is okay, and optimizes the query if necessary. If there's a syntax error, it's stored in the statement's error property.
Then we bound values to the placeholders using the bind_param method. The first argument of bind_param is a string that represents the data types of the values that you're binding. In this case, sid stands for string integer and double. The remaining arguments are the values to be assigned to the placeholders and they must be variables. That's why we created this make variable for the value to be assigned to the make column. If we'd attempted to pass this expression directly to bind_param, it would've generated an error. Then with all the values assigned to placeholders we could finally execute the statement and then fetch the result using the GET result method. It’s a more lengthy process than calling the query method, but it does make the SQL easier to read than embedding variables and it avoids complications with getting the right combination of single and double quotes.
Another useful feature of a prepared statement is the ability to bind the results to output variables and we'll look at 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.