PHP MySQL Connections
PHP provides three ways to connect with MySQL via MySQL Extension, MySQLi Extension and PDO. Read overview at PHP manual for more information and comparison of these three methods.
This is the first method PHP provided to interact with MySQL. This extension provides a set of built-in functions for connecting and making database queries. If a PHP built-in function starts with term mysql, it’s a function of this extension. For an example, following is the function used for connecting to MySQL.
mysql_connect('hostname', 'username', 'password');
MySQL extension only supports features of MySQL versions prior to 4.1.3. You can’t use advanced features of MySQL like Prepared Statements with this extension that was introduced after MySQL version 5.
But since many PHP applications only need to execute CRUD operations, you would still see functions of this extension in use. As of PHP version 5.3, there is no further development in this extension and it is only maintained.
MySQL Improved (MySQLi) Extension
This is the recommended method for executing MySQL operations in PHP. MySQLi supports basic and advanced MySQL features. It’s available from PHP version 5. MySQLi provides two interfaces to interact with MySQL as Procedural Interface and Objected Oriented Interface.
MySQLi Procedural Interface
This interface provides built-in functions like MySQL Extension. If a PHP built-in function starts with term mysqli, it’s a function of this interface. Following is how you need to connect with MySQL via this interface.
mysqli_connect('hostname', 'username', 'password');
MySQLi Objected Oriented Interface
This interface allows to instantiate an instance of built-in class mysqli. After instantiating an instance properly, the instance will have methods and properties to interact with MySQL. Following is how you need to instantiate an instance of mysqli.
$mysqli = new mysqli('hostname', 'username', 'password');
PHP Data Objects (PDO)
PDO is a built-in object oriented database abstraction layer that is available from PHP version 5. It abstracts specific database operations and provides a common interface for interacting with all supported databases. That is, if you use PDO for database operations in your PHP application, you can switch from MySQL to PostgreSQL with minimal code changes.
Which Method to Use?
Obviously you don’t need to use MySQL Extension. PHP discourages using it and since MySQLi Procedural Interface provides similar and more functions, there is no reason for using it.
If your PHP application is object oriented and you need to give the flexibility of using more than one Database Management System (DBMS) then you can use PDO. For an example, many PHP frameworks use PDO since they need to give the option of choosing the DBMS to the user of the framework.
But PDO doesn’t support all MySQL advanced features. For instance, it doesn’t support MySQL’s ability of using multiple statements. Before choosing PDO, check whether it facilitates all the database operations you want to have in your PHP application.
If you are sure that your PHP application only use MySQL then the best option is to go with MySQLi since it supports MySQL features more than other two methods do and since it’s being actively developed.
If your PHP application is more than few PHP scripts, you would tend to use MySQLi Objected Oriented Interface with other object oriented code for code-reuse and maintainability.