All my life (till now that is) I have been a self taught programmer and it was like seven years back in 2006 when I first learnt to build my first dynamic website using PHP and MySQL and it has been a long time since then. Well, when I started tutoring PHP and MySQL to myself, all the tutorials, self-help books on these subjects made use of the MySQL extension and thus I also continued writing codes in MySQL.
Gradually I started coming across articles on the web that laid the stress about using MySQLi - MySQL Improved and not use the older MySQL extension. Although it took me some time to make the change to MySQLi but I am happy I eventually did that. Recently I came across a huge code-base of a large web application developed in recent times (end of 2011) made using PHP and MySQL. And my job was to re-factor it and write the entire code-base using MySQLi.
Well, this made me feel to put some stress on the fact that any PHP developer should make sure they use the MySQLi extension to code their apps and here I have listed a couple of reasons why the MySQLi extension is much much better than the old MySQL extension for PHP and should always be preferred:
- MySQLi has this feature called prepared statements which is nothing but a safer way of sending data to MySQL and protecting yourself from getting hacked using SQL injection. This is the foremost reason why one should always prefer MySQLi over MySQL.
- MySQLi extension has been specifically developed to take advantage of the new features available in MySQL Server version 4.1.3 and above. So the thing is if you still use the MySQL extension then you might not be able to take full advantage of the new MySQL server features.
- MySQLi is object orientated and a lot has already been talked about how useful Object Oriented programming is.
- MySQLi supports multiple Statements, Complex Transaction statements and has enhanced debugging capabilities and embedded server support
However it has been seen in many benchmarks that the MySQL extension is slightly faster than MySQLi. But this difference is really very very less and it should not come in your criteria of choosing MySQLi over MySQL given the many other benefits MySQLi has. Be it MySQL or MySQLi, you must have some caching mechanism in place to minimize reads from the database.