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.
Transactions allow you to treat a series of SQL queries as a single unit that's executed only if all parts of it succeeds. Let's use a very simple example to see how transactions work. This is pdo_transaction.php which you can find in the Chapter 3, 03_06 folder of the exercise files. If we scroll down a little on line 27 is a loop that loops over a query that selects the name and balance values from a table called savings. Then on lines 29 and 30 those values are displayed. So, let's just take a quick at that in a browser. It's a very small savings bank we've got here, just two accounts. One for John White, one for Jane Black.
They've both got a $1,000 in their account. We'll use a transaction to transfer $200 from John's account to Jane's. So, let's go back to the editing program. And to save some time, I'm going to paste in some prepared statements to setup the transfer. The code is in transfer.txt, which you can find in the exercise files for this video. Needs to go straight after the database collection, create a new line on line four and paste in that code. Let's just take a very quick look at the code that we've pasted in.
Lines five, six and seven, setup three variables, one for the amount and two for the names of the different accounts. Then on lines eight and nine are two prepared statements that use named parameters. The first one deducts the amount from the balance while the second one adds it. And both statements are prepared, and the values are bound to the placeholders. So we now need to begin the transaction. And to do that, all you need to do is to call the begin transaction method on the database connection object. So let's add in some space. Now that we've begun the transaction, we can execute the first prepared statement. The first one is called pay. So we just call the execute method. And we need to check whether it was executed successfully. We can do that using the row count method.
When we looked at the row count method with a select query in the previous chapter, it didn't work with SQLite. However, when used with an insert, update, or delete statement, row count always returns the number of records affected. So, if this is executed correctly and the amount is deducted, row count will return one, which equates to true. If it fails, row count will return 0, which equates to false. So, we can use row count as our condition.
So, if not pay row count. We know that it has failed and if it has failed we need to roll back. So this is done on the database connector. Just call the roll back method. And if we have rolled back, we need to have an error message to explain what the problem was. So, we'll store that as error. But assuming that the first prepared statement was executed correctly, we can then continue with the second one.
So we do that in an else block. The second prepared statement is called receive. We just execute that, and again we need to check whether it was executed correctly. And we use exactly the same structure. If row count is 0, we know that it's failed. If it's 1, we know that it's succeeded. So let's very quickly copy that. And we can paste that inside this else block, and it's the receive row count that we're checking this time. We roll back if it fails, and we have put the correct message that it's the payee's balance that we weren't able to update.
But if it does succeed, if row count is one, we put in another else block here, and we just commit the transactions. So that's how a transaction works. You start off by calling the begin transaction method on the database object. You execute the first part of the transaction. If it fails you use the rollback method, otherwise you execute the next part of the transaction. If that fails again, you roll back.
But if everything has gone fine, you then use the commit method and that changes everything in the database and updates it. So if we save that. And we go back to the browser. If we refresh the page, John White's balance has gone down to $800 and Jane Black's has gone up to $1,200. So the transaction has worked fine. But let's try to transfer John's hard earned cash to someone without an account. So, let's go back here and right at the top instead of transferring to Jane Black, we'll transfer to Jane Brown. And there is no Jane Brown in our savings table. So, if we go back to the browser and refresh.
This time we got a message, transaction failed. Could not update Jane Brown's balance. We can see that John White, $200, hasn't been taken out of his account. So, the transaction has kept everything nice and safe. Make sure the test for failure produces valid results. For example, when we changed the payee to Jane Brown, the SQL was perfectly valid so it didn't produce an error. But nor did it update the table because there's no record for Jane Brown. You must use a database that supports transactions. PDO doesn't simulate transactions for you. Although, MySQL supports transactions with the inner db engine. MyISAM tables silently ignore transactions commands. So, you'll get no warning. MySQL and some other databases automatically create queries that commits data definition language. Such as drop table or create table in the middle of transaction. There is no way of undoing such automatic commits
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.