From PHP 5.5 many functions are been deprecated. Programmers are discouraged from using MySQL and are told to use MySQLi as MySQL will be removed in future. PDO (PHP Data Objects) is a API that allows you to itneract with any PDO aware database (11) including MySQl just by replacing database driver (altering initialisation).
So what advantages do MySQLi has over MySQL that you should change?
1. Object Oriented Interface:
You can use the old ways of calling MySQL extension but object oriented version groups the functions according to their job.
2. Prepared statements
A prepared statement is a SQL query where the query itself, and the external data (the parameters) are defined separately. The query is compiled only once, reissuing it only swaps the parameters.
It saves time in execution and prevents SQL injections.
Do Note that MySQLi doesn’t support client-side prepared statements, although PDO does.
3. Multiple statements
You can execute many queries inside only one mysqli call. This reduces round trips between database server and the PHP server.
4. Support for Transactions
With the help of trasactions, you can write a group of SQL statements that will either be executed or everything will be rolled back.
5. Enhanced debugging capabilities
You can use “mysqli_debug(…)” to save debugging information on a file.
6. Embedded server support
Since MySQL 4.0 there is a library that can be used to run a complete MySQL server embedded inside a program.