MySQLi - An improved and enhanced MySQL extension

After depreciation of mysql_* functions from PHP 5.5, MySQLi and PDO are two most popular and preferred options to interact with MySQL databases in PHP. This article demonstrates the introduction of MySQLi and highlights the reasons behind the development of MySQLi and its different features. MySQL is one of the most popular open-source Relational Database Management System (RDBMS) which is used by many popular websites such as Google, Facebook, Twitter and many other applications.

There are mainly three API options for connecting and interacting with a MySQL database using PHP:

• MySQL Extension
• MySQLi Extension
• PHP Data Objects (PDO)

Why to use MySQLi / PDO over MySQL ?

MySQL Extension is the older extension which is used by PHP applications to connect and interact with MySQL database. This extension provides a procedural interface only and It is developed to utilize the features of MySQL server older than version 4.1.3. This extension can also be work with MySQL versions 4.1.3 and above, but all the features added to new versions of MySQL Server are not available with this extension.

    After the officially depreciation of MySQL extension in late 2012. Two other alternatives available for PHP 5 and later versions are MySQLi and PDO (PHP Data Objects), which are developed to take advantage of the existing and newly added features in MySQL server versions above 4.1.3. With PHP 5, the best practice is to use the MySQLi functions instead of MySQL. The functions of MySQL extension are no longer maintained, and are officially depreciated from PHP 5.5 and above versions. These functions will be removed in future versions.

A comparison table for above three API's are given under the title Comparison of MySQL API options for PHP in [1]. There also a note on same webpage regarding the recommended use of MySQLi extension.

Note : If you are using MySQL versions 4.1.3 or later it is strongly recommended that you use the mysqli extension instead.

MySQLi :

The MySQLi extension also known as MySQL improved extension, "i" in MySQLi stands for "improved". This is developed to take advantage of new features added in MySQL versions 4.1.3 and later. MySQLi introduced with PHP 5.0 and MySQLi provide support for newer features added to new MySQL server versions.

Features of MySQLi :

Below is the list of features which defines the benefits of using MySQLi instead of MySQL.

1. Both Object-Oriented and Procedural Interface :

MySQLi extension provides both Procedural as well as Object Oriented interfaces for connecting and interacting with MySQL databases. MySQLi procedural syntax is similar to MySQL syntax.

2. Prepared Statements :

Prepared statements are one of the biggest feature of MySQLi, which allow separation of SQL logic and data. This feature allows to pass an SQL query without data, the data is bound as parameters and than the query is executed. This process increases the performance and also useful to prevent SQL injection attacks.

3. Process Multiple Statements :

This feature allows to execute multiple queries in a single call, which reduce the round trips between client server and database server.

4. Transaction Support :

MySQLi also support transactions, which is useful in case if you run multiple insert statements which rely on each other. So in case of failure of one the effects of all other queries also rollback to previous state.

5. Enhanced Debugging capabilities :

This feature allows you to save debugging information into a file.

6. Embedded Server Support :

MySQLi offers different methods for connecting and manipulating embedded MySQL server libraries.

References :

[1] http://php.net/manual/en/mysqli.overview.php