Join David Powers for an in-depth discussion in this video Overview of PHP database APIs, part of PHP: Accessing Databases with PDO and MySQLi.
PHP has a large number of database extensions. At the time of recording, this page in the online documentation shows there are four abstraction layers and 23 vendor specific database extensions. The vendor specific extensions use dedicated functions that interact only with a particular type of database. For example, Microsoft SQL server, MySQL or Post SQL. So, if you create an application with one of these extensions and later change your mind about the database you're going to use, you have to rewrite all of your code.
The idea of an abstraction layer on the other hand is to make your code more portable. Instead we've working with just one database, it'll work with several, as long as the PHP server has the correct libraries or drivers installed. In this course, we are going to look at one abstraction layer, PDO, which stands for PHP Data Objects. And at the object oriented version of the vendor specific database extension from my SQL. But why choose those two? When among the obstruction layers, PDO is the only one that's installed by default in PHP.
It's also the most versatile. Let's take a look at the list of PDO drivers and the databases they support. They include all the most important databases, including Microsoft SQL Server, PostgreSQL and MySQL. However, it's important to realize that the drivers need to be installed separately in PHP. Although PDO is installed by default, the drivers available on individual servers depend on how the server has been configured. You need to run phpinfo on your server to check which drivers are installed.
Let's take a look at the configuration set up on my local testing server. Here is PDO and pdo_mysql and sqlite, but nothing else. On Windows, extra drivers can be enabled by loading the appropriate dll file with php.ini but on Mac and Linux, they need to be compiled into PHP. Setting up PDO drivers is beyond the scope of this course. Speak to your server administrator or hosting company if you need help in doing so.
The vendor specific database extension covered in this course is MySQL Improved, or MySQLi. I've chosen it because MySQL is still by far the most widely used database with PHP. Although PDO supports MySQL, some features are easier to implement with MySQLi because the extension is designed specifically to work with MySQL. Code written using MySQLi will also work with MariaDB, the open source database that some Linux distributions have adopted as a replacement for MySQL.
Perhaps one of the most important considerations is that the original MySQL functions have been deprecated since PHP 5.5. That means functions such as mysql_query and mysql_fetch_assoc will eventually be removed from PHP. If your scripts still use them, you need to replace them either with PDO, or MySQLi, before they stop working. MySQLi has two interfaces. Procedural and object-oriented.
There's no significant difference in performance between the two interfaces. The procedural version was mainly designed to simplify the transition from the old MySQL functions. However, this course we'll use the object-oriented interface for two reasons. The code is more concise, so it involves less typing. And in my opinion, the shorter code makes it easier to read.
- Connecting to a database with PDO or MySQLi
- Fetching a result set
- Executing simple non-SELECT queries
- Sanitizing user input
- Binding input and output values
- Passing an array of values to the execute() method
- Working with advanced PDO fetch methods
- Executing a MySQLi transaction
- Freeing resources that are no longer needed
- Submitting multiple queries
- Creating an instance of a class from a result set