When should I use MySQLi instead of MySQL?

Can someone clarify for me what the advantages and disadvantages of using MySQLi instead of MySQL? Are there situations where I should not use MySQLi? Do I need to configure my server differently in order to use MySQLi? For instance, do I need to upgrade Apache or PHP so I can support MySQLi?

Solution 1:

Reasons why you should use MySQLi extension instead of the MySQL extension are many:

1. MySQLi gives you prepared statements - a safer way of sending data to MySQL and protecting you from SQL injection. This alone should be enough for always choosing MySQLi over MySQL.
2. MySQLi enables most of the MySQL features.
3. MySQLi is object orientated.
4. MySQLi supports prepared statements, transactions and multiple statements.
5. The old MySQL extension is deprecated as of PHP 5.5.0

And there are other benefits. But mainly, you should focus on security and stabiltity - and MySQLi gives you just that.

Solution 2:

PHP team refuse to support mysql extension any further. This reason is alone enough.

All other reasons don't make much sense:

• MySQLi gives you prepared statements - one can use old mysql with manually handled plaeholders and get even safer. This alone should be stopping MySQL from useless deprecation.
• MySQLi enables most of the MySQL features which most of PHP users never ever heard of.
• MySQLi is object oriented - a pair of straight hands can make old mysql objec oriented in a matter of several hours.
• *MySQLi supports
  • transactions* - mysql supports them as well. just run START TRANSACTION query and you're set.
  • multiple statements - yet nobody actually needs them.
  • prepared statements - as a matter of fact, the support is horrible, renders them practiaclly unusable

So, there are no advantages at all.
If you want to get along with non-deprecated but usable extension - go for the PDO.

Solution 3:

If you are using mysql > 4.1.3. Mysqli is a new interface for mysql in php. A quote from http://www.php.net/manual/en/mysqli.overview.php

What is PHP's MySQL 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.

Note:

If you are using MySQL versions 4.1.3 or later it is strongly recommended that you 
use the mysqli extension instead.

Solution 4:

Always, if possible.

It is possible that MySQLi is not supported by your PHP install. However, most hosting providers have support for MySQLi.