Hi all,
There are too many differences between these MYSQL and MYSQLi PHP database extensions. These differences are based upon some factors like features, performance, benefits, library functions and others. The “i” in mysqli stands for “improved”. The “mysqli” extension is an improvement over the old “mysql” extension.
| MYSQLi | MYSQL |
|---|---|
| MySQLi extension added in PHP 5.5 | MySQL extension added in PHP version 2.0 |
| Extension directory: ext/mysqli. | Extension directory: ext/mysql |
| The MySQLi supports prepared statements. | The MYSQL does not support prepared statements. |
| MySQLi supports transactions through API. | Transactions are handled by SQL queries only. |
| MySQLi provides both object oriented and procedural interface. | MySQL provides procedural interface. |
| MySQLi extension is with enhanced security and improved debugging. | MySQL extension lags in security and other special features, comparatively. |
| MySQL provides procedural interface. | MySQLi provides both object oriented and procedural interface. |
| MySQLi supports store procedure. | MySQL extension does not support stored procedure. |
| mysqli_connect($host_name,$user_name,$passwd,$dbname) | mysql_connect($host_name,$user_name,$passwd) followed by mysql_select_db($dbname) |
The Relational Database Management System(RDMS) is MySQL. That means, the database management system based on relational model. MySQLi Extension (or simply known as MySQL Improved or MySQLi) is a relational database driver that is used mainly in the PHP programming language. MySQL is an RDBMS that runs as a server and provides multi-user access to multiple databases; MySQLi is an extension of MySQL. MySQL is the very popular open-source relational database management system
The PHP functions for use with MySQL 5.0 have the following general format described below. The i in the function name stands for improved (MySQL Improved).
mysqli_function(value,value,...);
Two of the mysqli functions:
mysqli_connect(connection information);
mysqli_query($con,"SQL statement");
The corresponding mysql functions are:
mysql_connect(connection information);
mysql_query("SQL statement",$con);
The connection process for mysql functions requires two function calls:
mysql_connect($host_name,$user_name,$password);
mysql_select_db($dbname);
| mysqli Function | mysql Function |
|---|---|
| mysqli_real_escape_string($conn,$data) | mysql_real_escape_string($data) |
| mysqli_errno($con) | mysql_errno() or mysql_errno($cxn) |
| mysqli_select_db($con,$dbname) | mysql_select_db($dbname) |
| mysqli_error($con) | mysql_error() or mysql_error($con) |
| mysqli_query($con,$sql) | mysql_query($sql) or mysql_query($sql,$con) |
| mysqli_fetch_array($result) | mysql_fetch_array($result) |
| mysqli_num_rows($result) | mysql_num_rows($result) |
| mysqli_fetch_assoc($result) | mysql_fetch_assoc($result) |
| mysqli_fetch_row($result) | mysql_fetch_row($result) |
| mysqli_insert_id($con) | mysql_insert_id($con) |
Key benefits of mysqli over mysql extension
- Object-oriented interface
- Dual object-oriented (OO) and procedural interfaces
- Multiple Statements
- Support for Prepared Statements
- Support for Transactions
- Enhanced debugging capabilities
- Embedded server support
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
personally I prefer to use PDO because PDO extension has more database support, 11 different drivers aside from MySQL server