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 the previous video, we created a transaction to transfer money from one account to another. But there was nothing to prevent the transaction from going ahead if the payer ran out of funds. This is pdo_check_balance.php which you can find in the Chapter 3, 03_07 folder of the exercise files. The script has been updated to roll back the transaction if the balance in the outgoing account falls below zero.
Adding this extra step results in some databases displaying the wrong balance after the transaction has been rolled back. The fix is very easy but first let's take a look at the code and see what's happening. On line nine is a new prepared statement with a named placeholder that gets the balance from the savings account for the payer. Then on lines 16 and 17 the statement is prepared and the payer's name is bound to the named placeholder.
The third statement is executed a bit further down, here on line 31. And then, on line 32, the fetchColumn method is used to get the result. There's only one result. So that's used to get it and it's assigned bal. If bal is less than 0, the conditional statement a bit further down, here on lines 35 to 38. That rolls back the transaction and sets an appropriate error message. The rest of the transaction is inside the else block.
All this looks fairly straightforward, but let's test it with SQLite. So, we'll load it into a browser. And John White has gone down to 600. Black's gone fine there. 1,400 for Jane Black. Refresh. That seems to be okay. Refresh again. 200. He's now at zero. The next time, it should fail. Transaction failed: insufficient funds in John White's account. But look, it says that John White's account is now at minus $200. That's clearly quite wrong. This gives the impression that the roll back failed, but that's not the case. So let's go back to the code. The check prepared statement that's executed on line 31 gets the pair's balance as part of the transaction.
Then, further down. This for each loop on line 71 runs another select query to get the balance again. What's happening is that the result from the first select statement isn't being cleared before the second one is run. The solution is to clear the first statement using the closeCursor method. This frees the database connection to execute the subsequent query correctly. So, let's go back up to where the check statement is executed, and then here, we need to call check, closeCursor.
So, if we save the page, and go back to the browser, and refresh it. This time we get transaction failed, insufficient funds in John White's account. But we've now got the correct balance of 0, and 2,000 for Jane Black. And just to prove that it works correctly, let's go back, and we'll reverse the payments. So, we'll make John White the payee. And we'll make Jane Black the payer. We'll save that.
Go back to the browser and refresh. We'll have do this quite a few times, but as we keep on going, now we've got her at zero. We don't have the minus 200 at all. Incidentally, if you run the original script using MySQL as your database, it'll work correctly without needing to call closeCursor. However, using closeCursor makes the code more portable, so if we just go back, this is what we have done. Here we have used closeCursor to free the connection to the server to allow other queries to be run, but it leaves the statement object in a state that allows it to be executed again if necessary.
It's not required by all databases, but certainly, calling this method doesn't do any harm.
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.