What is the Difference Between MySQL and mysqli


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 two main API options when considering connecting to a MySQL database server. They are,

  • PHP’s MySQL Extension
  • PHP’s mysqli 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.


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 main useful features are:

  1. an Object-oriented interface
  2. support for prepared statements
  3. support for multiple statements
  4. support for transactions
  5. enhanced debugging capabilities
  6. embedded server support.

Object-oriented interface: You can still use the “old procedural” way of calling the mysql extension but the OO version groups the functions by their purpose.

Support for prepared statements: Those are useful to prevent SQL injections and are executed faster.

Support for 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).

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.

The main differences between mysqli and mysql API are as follows:

  mysqli mysql
Development Status Active Maintenance only
Lifecycle Active Long Term Deprecation Announced*
Recommended Yes No
Asynchronous Queries Yes No
Server-Side Prep. Statements Yes No
Stored Procedures Yes No
Multiple Statements Yes No
Transactions Yes No
MySQL 5.1+ functionality Yes No


If you like this article then please share it and help us grow.


Connect With Us

Let's Talk Business


Abdulrahem Yousef

Email: ayousef@php-jo.com
Phone: +962 79 026 1587

Feras Abualrub

Email: fabualrub@php-jo.com
Phone: +962 79 026 1477


Mohammed Abu-Alhija

Email: mabualhija@php-jo.com
Phone: +962 79 0884362