There has been a huge discussion over the internet about using MySQL or MySQLi in their PHP code. Before getting to that part, one must have a basic knowledge of it, so lets get started on it. To start off with, there are three main API options when considering connecting to a MySQL database server. They are,
- PHP’s MySQL Extension
- PHP’s mysqli Extension
- PHP Data Objects (PDO)
Of course each of them have their own set of advantages and disadvantages.
What is PHP’s MySQL Extension?
This is the original extension designed to allow you to develop PHP applications that interact with a MySQL database. The mysql extension provides a procedural interface and is intended for use only with MySQL versions older than 4.1.3. This extension can be used with versions of MySQL 4.1.3 or newer, but not all of the latest MySQL server features will be available.
If you are using MySQL versions 4.1.3 or later it is strongly recommended that you use the mysqli extension instead.
In the PHP installation on the remote server or local server, the mysql extension source code is located in the PHP extension directory ext/mysql.
What is PHP’s mysqli Extension?
The mysqli extension, or as it is sometimes known, the MySQL improved extension, was developed to take advantage of new features found in MySQL systems versions 4.1.3 and newer. The mysqli extension is included with PHP versions 5 and later.
The mysqli extension has a number of benefits, the key enhancements over the mysql extension being:
- Object-oriented interface
- Support for Prepared Statements
- Support for Multiple Statements
- Support for Transactions
- Enhanced debugging capabilities
- Embedded server support
If you are using MySQL versions 4.1.3 or later it is strongly recommended that you use this extension.
Along with the object-oriented interface the extension also provides a procedural interface.
The mysqli extension is built using the PHP extension framework, its source code is located in the directory ext/mysqli in the PHP installation in the remote server or local server
I personally use MySQLi extension in my projects as i feel it has future use and it can be used in both Object-Oriented Style and Procedural Style. The official website for PHP itself states reasons for opting the MySQLi extension.
Read about: Migrate from MySQL to MySQLi & vice versa
Why MySQLi?
The mysqli extension allows you to access the functionality provided by MySQL 4.1 and above.
The persistent connection of the mysqli extension however provides built-in cleanup handling code. The cleanup carried out by mysqli includes:
- Rollback active transactions
- Close and drop temporary tables
- Unlock tables
- Reset session variables
- Close prepared statements (always happens with PHP)
- Close handler
- Release locks acquired with GET_LOCK()
This ensures that persistent connections are in a clean state on return from the connection pool, before the client process uses them.
The mysqli extension does this cleanup by automatically calling the C-API function mysql_change_user().
The automatic cleanup feature has advantages and disadvantages though. The advantage is that the programmer no longer needs to worry about adding cleanup code, as it is called automatically. However, the disadvantage is that the code could potentially be a little slower, as the code to perform the cleanup needs to run each time a connection is returned from the connection pool.
It is possible to switch off the automatic cleanup code, by compiling PHP with MYSQLI_NO_CHANGE_USER_ON_PCONNECT defined.
For more information regarding MySQL, you can visit their official website here.
If you have any queries regarding this topic then please feel free to reach me through the comment section below.4