When should I use MySQLi instead of MySQL?

Question

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?

Answer 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.

Answer 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.