Difference Between MySQL and MySQLi Extension

MySQL vs MySQLi Extension

MySQL is a popular Relational Database Management System (RDBMS). It is an open source DBMS that is used widely even in large scale enterprises such as Wikipedia, Google and Facebook. PHP (stands for PHP: Hypertext Preprocessor) is a server side scripting language, especially suited for developing dynamic and interactive web pages. MySQL and MySQLi are two extensions provided for the interaction of PHP applications with a MySQL databases. These two extensions are implemented using the PHP extension framework and these provide an API (Application Programming Interface) to the PHP programmers to interact with MySQL databases.

What is MySQL Extension?

MySQL Extension is the first extension provided for developing PHP applications, which can be used to interact with MySQL databases. This provides a procedural interface for PHP programmers to interact with MySQL databases. This extension is intended to be used only with MySQL versions that are older than version 4.1.3. Even though this could be used with MySQL version 4.1.3 or newer, any of the new features in those versions will not be available. Currently there are no active developments happening on MySQL Extension and it is not recommended for new projects. Further MySQL Extension does not support server-side prepared statements or client-side prepared Statements. It also does not support stored procedures or Charsets.

What is MySQLi Extension?

MySQLi Extension (also called the MySQL improved extension) is the new extension provided for developing PHP applications that can interact with MySQL databases. This extension is developed to get the maximum use of features available in MySQL version 4.1.3 or newer. MySQLi Extension is firstly incorporated with PHP version 5 and included in all the later versions. In addition to providing a procedural interface for PHP programmers, MySQLi Extension provides an object oriented interface as well. This also provides support for client/ server side prepared statements and multiple statements. Furthermore, it supports Charsets and stored procedures.

What is the difference between MySQL and MySQLi Extension?

Even though both MySQL Extension and MySQLi Extension are extensions provided for developing PHP applications that can interact with MySQL databases, MySQLi Extension has some key enhancements over the MySQL Extension. Firstly, MySQL Extension is recommended to be used with MySQL versions which are older than 4.1.3, while MySQLi Extension is recommended to be used with MySQL versions 4.1.3 or newer. Also, MySQLi Extension is only incorporated with PHP 5 or later versions. MySQL Extension provides only a procedural interface for the PHP programmers, while MySQLi Extension provides an object oriented interface (in addition to the procedural interface). Furthermore, MySQLi Extension provides support for prepared statements and multiple statements, which were not supported in MySQL Extension. MySQLi Extension provides improved debugging capabilities when compared with MySQL Extension. In addition, MySQLi Extension provides embedded server support and transaction support, which were not available in MySQL Extension. Even though MySQL Extension could be used with MySQL versions 4.1.3 or newer, any of the new features included with those MySQL versions will not be available.