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.
With MySQL improved the query method handles all SQL statements, not just select queries. Let's take a look at using the query method to insert and delete records and to find out how many records have been affected in the database. This is mysql_insert.php which you can find in the chapter five 05_06 folder of the exercise files. This file contains a simple insert query. The insert saying, new value into the names table.
So to insert that, what we need to do is to call the query method on the database object. So it's DB. Query and pass it the SQL. When used with a select statement, the Query Method returns MySQLi result object. But with Insert, Update, Replace or Delete, it simply returns true or false depending on whether it's succeeded or failed. Will be looking at getting arrow messages later, but we can get a useful piece of information from the database connection object immediately after executing a non select query.
The number of rows effected is stored as the effected rows property. So we can display that, so we can caterate on the end of there. Database object, and it is the affected rows property that we want. And with an insert statement, we could also get the new ID of the record, as long as the table is using auto increment for the primary key. The ID is stored as the insert ID Property, also on the database connection object.
So let's display that, before doing so let's just add a line break at the end here, and on the next line inserted with ID, and then we display the ID, database object and then the insert ID property. So, if we save that and load it into the browser, this will insert William into the name's table and we should see the result displayed in the browser. And there we are, rows affected one. And it's been inserted with the ID 11. You can rely on the insert ID property for single insert statements, but you should beware of relying on it when multiple values are inserted by the same query.
Let's see what happens. Going to add a new value to this insert statement. Doesn't matter that we're inserting William again, it's only a test database. So we'll add a new value, Lucy, and the meaning of Lucy is light, and it's a girls name. So if we save that and go back to the browser and refresh. This time we're going to have two values inserted. So you would probably expect that the ID will be 13. Let's see what actually happens. Rows effected two, but inserted with ID 12. So the ID that is reported is for the first new record in an insert statement with multiple values not the last one. So that's how you insert records using the query method.
Let's see how you delete them. So open mysqli_delte.php, which is also in the chapter505_06 folder of the exercise files. On line four, we've got a delete statement, deleting from names, where name equals William, we've got two Williams in our names table now, so this should work quite nicely. We then just submit it using the query method, then we can get the number of rows affected using the affected rows property in exactly the same way as with an insert statement.
So if we load this into the browser, it should get rid of our two rows. And indeed, it has number of records deleted 2. And if we refresh the page it'll say, number of records deleted 0. And that's because we've no longer got William in our database. The query is executed successfully but there's nothing to delete. The query method is an all purpose tool for executing SQL. With a select query, it returns a MySQLi result object.
At all other times, it returns true or false. The number of rows affected by a non-select query is stored as the affected rows property of the database object. And with an insert statement you can get the ID of a new record that has been inserted using the insert ID property of the database object. However, there are two caveats about the insert ID property. It works only if the primary key is set to auto-increment, and it returns the ID of the first new record if the insert statement inserts multiple values.
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.