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.
Using a prepared statement makes it much easier to embed values from user inputs into an SQL query in a secure way. Instead of adding the values directly to the SQL, you use placeholders, and PHP does the rest. Using the prepared statement involves several steps. We'll begin by initializing and preparing a statement. This is mysqli_statement.php, which you can find in the chapter six, 06_01 folder of the exercise files. Let's load this into a browser to see what the page contains. It's a search form with a text input field, two select menus, and a submit button. At the moment, the PHP script ignores the values in the form fields, so if I change this to 2010, and a maximum price of $5,000, I wouldn't expect to find many cars, if any. But, if I click Search, I get a complete set of results from the Cars and Makes table in the OOPHP database. We need to embed the values submitted by the form into the SQL query.
And, we'll use a prepared statement to do that. To add the search criteria, we need to create a WHERE clause, that needs to go before the ORDER clause in the SQL. So, add a new line on line eight. Then WHERE, make, and we'll use the LIKE operator. Then we need a placeholder. MySQLi uses anonymous placeholders, which is simply a question mark, and the AND clause, yearmade. That needs to be greater than or equal to, then another place holder, another question mark. And, price is less than or equal to, and a third place holder, a third question mark. So, these three question marks will represent the values that are being brought in from the search form.
The original code on line ten uses the query method to submit the SQL statement. That won't work with placeholders. So, we need to get rid of that line, and then we need to initialize a statement. So, we create a statement variable, a variable for our statement object, then use the database connection object, and call a method which is stmt_init(). And, this will create a statement object. We can now use this to prepare the query. What this means, is that the query, complete with placeholders, will be submitted to the database to make sure the SQL is valid. It will also perform any optimization, if necessary. And, if there's a problem, an error will be generated. So, we need to wrap the next bit of code in a conditional statement.
So, if, then not, and our statement object, and then prepare SQL. So, if there's a problem with preparing the SQL, the statement's error property will contain an error message, we can assign that to our error variable. Notice, that the error is a property of the statement object, unlike the original code which used the query method. With query, any error message is on the database connection object, so that's what this code here on lines 14 to 16 were for, so we need to get rid of that.
That's part of the original code. So, get rid of those three lines. But, if there is no problem with preparing the SQL in the placeholders, the rest of the code for the prepared statement needs to go in an else block, so let's just create an else block there. And, then the next stage will be to bind the values to the placeholders, before we can execute the prepared statement. We'll do 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.
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.