Difference between mysql & mysqli?

There are three main API options when considering connecting to a MySQL database server:

• PHP's MySQL Extension
• PHP's mysqli Extension
• PHP Data Objects (PDO)

In relation to PHP, MySQL is the old database driver, and MySQLi is the Improved driver. MySQLi takes advantage of the newer features of MySQL 5. Lifted verbatim from the php.net site:

• Object-oriented interface
• Support for Prepared Statements
• Support for Multiple Statements
• Support for Transactions
• Enhanced debugging capabilities
• Embedded server support

The "i" stands for "improved".

mysqli stands for mysql improved. The reason to use this over just mysql_ is because it is safer to use.

MySQLi is the OOP version of MySQL extension. In the end, the accomplish the same thing: extension for interacting with MySQL from PHP. A lot of people still use the original MySQL extension vs the new MySQLi extension because MySQLi requires MySQL 4.1.13+ and PHP 5.0.7+ .

MySQLi supports some things that the old MySQL extension doesn't. Things like prepared statements, multiple statements, and transactions on top of my head.

• Dual object-oriented (OO) and procedural interfaces – This versatility was absent in previous version of MySQL. The procedural interface may be preferred by users migrating over from the old MySQL system, with function names differing only in prefix (simply add an “i). The object-oriented interface shows functions grouped by their purpose, making for an easier start. The two interfaces perform similarly with user preference being the deciding factor between the two.
• Support for prepared statements – Bringing MySQL/PHP up to date with other platforms, MySQLi is able to pass a query without any data to MySQL. After binding the parameters, the query is executed. This process results in increased performance, convenience for the developer and more security by preventing SQL injection attacks.
• Ability to process multiple statements – The ability to string together multiple statements in one statement string reduces client-server round trips. The individual statements that make up the string are separated by semicolons, then the results of each executed statement are fetched.
• Embedded server support – MySQLi offers methods of connecting and manipulating embedded MySQL server libraries.
• Enhanced debugging capabilities – Users are able to enter debugging queries into the system which results in a more efficient development process. An example of MySQLi debugging query would read: mysqli_debug(“d:t:o,/tmp/client.trace”)
• Support for ability to use transactions –  Using transactions allows you to input a group of queries that execute but do not save their effects in the database. This is helpful if you have multiple inserts that rely on each other, if one fails, the others can be rolled back and none of the data is inserted.

The Pros and Cons of the Changes

While MySQLi has its advantages in interfacing with MySQL, the other options sometimes have a leg up and may be a better fit for the user, depending on personal preference and convenience. Like most things, the option that works best depends on whom you ask and what situation you need MySQLi or its competitors for.

Pros

1. MySQLi’s interface is similar to the older MySQL interface. This versatility in using both object-oriented and procedural formats makes it easier for users of the older system to make the change to the improved one, as well as facilitating use for people that are completely new to the system. Using the old system, the user would enter a function looking like this: mysql_connect(), using the new system, this old code can be updated simply by changing the function to this: mysqli_connect().
2. MySQLi has good support and maintenance, making the transition to and use of the new system safe and secure.
3. The user has the ability to utilize new features available in the newest versions of MySQL servers. This is one of the biggest advantages of MySQLi. Other platforms are unable to take full advantage of MySQL’s newest capabilities.
4. The installation process with MySQLi not only easy, but is automatic when the PHP5 MySQL package is installed in Windows or Linux.
5. MySQLi performs (slightly) faster than its competition. With non-prepared statements, the system performs  ~2.5% faster, and ~6.5% faster with prepared ones. This is only a slight advantage and may only matter to certain users.

Cons

1. MySQLi only works with MySQL databases, whereas PDO is flexible and able to work with multiple database systems, including IBM, Oracle and MySQL. If you have to switch databases, MySQLi is not the best option.
2. MySQLi is not as portable as PDO.
3. Binding parameters with MySQLi is not as easy or flexible as with PDO. MySQLi uses a numbered binding system and doesn’t support named parameters like PDO does.