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 binding values to placeholders with the bindParam and bindValue methods, PDO allows you to pass the values directly to the execute method as an array. When you do this, all values are treated as strings. You can either define the array first or create it directly between the parentheses of the execute method. We'll look at both ways. This is pdo_named_excute.php, which you can find in the chapter three 03_04 folder of the exercise files.
It's exactly the same file as the one we created earlier using named parameters. Because the parameters have names, you need to create an associative array using the name as the key for each value. Because they're individually named, they can be in any order. So, let's create a new array. We'll create it after preparing the statement. We'll call the array values and just because we can, let's put them in a different order.
So, we'll start off with colon price. And the double arrow operator for the value. That comes directly from the get array, from get price. And a comma and our next value. This time we'll have :make. This needs to begin with the wild card character concatenated with the value from the get array. And another wildcard character, and then our final array element will be yearmade, followed by its value. Now that we've created this array we don't need to bind the values using bindValue or bindParam so we can delete lines 14, 15 and 16, and then just pass the values array directly to the execute method.
So, if we save that and run it in a browser, it should work exactly as before. So let's see if we can find some, I don't know, fords. Here we are. It's working exactly as you would expect. Now we did put price in the wrong order. So let's just test that again. We've got a Ford here that is worth 10,980. So, if we look for fords again. And set the maximum price to 10,000, this one should not be displayed.
And it's working exactly as we would like it to do. And the reason for that, of course, is because we do have named parameters. And the names are identified in the associative array so it doesn't matter which order they're in. So, let's see how it works with anonymous placeholders. This is pdo_anonymous_execute.php, which you can find in the same chapter three, 03_04 folder of the exercise files. It's the same file that we used before with anonymous placeholders. Because there are known names with an anonymous placeholder, you create an indexed array without keys.
So the values this time must be in exactly the same order as the placeholders. That means we need to have make, yearmade and price in that order. Let's do it directly between the parentheses of the execute method so we can get rid of the bindValue and bindParam lines and then we'll create an anonymous array directly inside there between the parentheses. The first value needs to be make. So that begins with the placeholder character, concatenated with the value from the GET array, and concatenated with another placeholder character. Then, a comma for the next array element. This is going to be yearmade. That also comes from the GET array, we just go to it directly. And the final value in that array needs to be price.
So, if we save that and load this page in a browser, it should work exactly the same as the other pages. So, we'll look for BMWs. There it is, it's working perfectly. Passing the values directly as an array to the execute method is a convenient shorthand, but it does have the disadvantage that all values are passed as strings. That means you can't insert a null value. With anonymous parameters, there's also a danger of getting them in the wrong order if you make any changes to the SQL.
However, it is worth considering passing the values directly to the execute method if the prepared statement takes only a single input value. But with a single value, the argument passed to the execute method still needs to be an array. Just make sure it's an array with a single element.
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.