MySQL PHP Connections
PHP provides three ways to connect with MySQL: MySQL Extension, MySQLi Extension, and PDO.
MySQL Extension
This was 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 example, the following is the function used
for connecting to MySQL.
|
1 |
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 that was introduced after MySQL version 5 with this
extension.
But since many PHP applications only need to execute CRUD operations, you will 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: 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. Below is how you need to connect with MySQL
via this interface.
|
1 |
mysqli_connect('hostname', 'username', 'password'); |
MySQLi Objected Oriented Interface
This interface allows instantiation of an instance of built-in class mysqli. After instantiating an
instance properly, the instance will have methods and properties to interact with MySQL. Below is
how you instantiate an instance of mysqli.
|
1 |
$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 have the flexibility of using more than one
Database Management System (DBMS), then you can use PDO. For example, many PHP frameworks
use PDO, since they need to give the user of the framework the option of choosing the DBMS.
But PDO doesn’t support all MySQL advanced features. For instance, it doesn’t support MySQL’s
ability to use 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 uses MySQL, then the best option is to go with
MySQLi, since it supports MySQL features more than the other two methods do and since it’s being
actively developed.
If your PHP application is more than a few PHP scripts, you would tend to use MySQLi Objected
Oriented Interface with other object-oriented code for code-reuse and maintainability.
