The mysqli and mysql are the database extension which allows you to access the functionality provided by MySQL 4.1 and above.
The PHP code consists of a core, with optional extensions to the core functionality. PHP's MySQL-related extensions, such as the MySQLi extension, and the MySQL extension, are implemented using the PHP extension framework. An extension typically exposes an API to the PHP programmer, to allow its facilities to be used programmatically. However, some extensions which use the PHP extension framework do not expose an API to the PHP programmer.
MySQL extension is deprecated and will not be available in future PHP versions. It is recommended to use MySQLi extension with PHP 5.5 and above.
MySQL is a relational database management system (or DBMS) –its means that it is a database management system based on the relational model. This RDMS runs as its own server and provides multi-user access to multiple databases at once. The source code of MySQL is available under the terms set forth in the GNU General Public License as well as a plethora of proprietary agreements. Members of the MySQL community have created many different branches of the RDMS –the most popular of which are Drizzle and MariaDB. As well as being the prototype of several branches, most free software projects that must have a full featured database management system (or DMS) use MySQL.
MySQLi Extension (or simply known as MySQL Improved or MySQLi) is a relational database driver that is used mainly in the PHP programming language. It provides an interface to the already founded MySQL databases. It is quite literally an improved version of its predecessor, MySQL, which was simply a means to manage databases over servers.
Key benefits of MySQLi over MySQL extension
- Object-oriented interface. You can still use the "old procedural" way of calling the MySQL extension but the Object Oriented groups the functions by their purpose.
- Prepared Statements. Those are useful to prevent SQL injections and are executed faster.
- Multiple Statements. With this feature, you can execute multiple SQL queries inside only one "mysqli" call. This reduces the round trips between the database server and the PHP server.
- Support for Transactions. This is really useful to write robust applications. It gives you the ability to write a group of SQL statements that will either be executed or all rolled back (usually if there is an error somewhere in the process). MySQLi supports transactions through API.
- Enhanced debugging capabilities. As an example, you can use "mysqli_debug(...)" to save debugging information into a file.
- Embedded server support. Since MySQL 4.0, there is a library available that can be used to run a complete MySQL server embedded inside a program, usually a desktop application.
- Extension Directory. MySQLi uses ext/mysqli directory whereas MySQL uses ext/mysql directory.