Mysql and MySqli both are the PHP based extension which we used to make connection with our database and handle database query through PHP. It makes easier to communicate with database and handle all database related queries, There is large set of the function available in these extensions .
This is mostly ask-able question during interview time that what is the difference between MySql and MySqli, For your information MySQL extension is deprecated now and it will not be available in future PHP versions. So it is recommended to use it’s upgraded version MySqli with more security and performance.
MySql VS MySqli
|Only allow procedural interfaces.||Dual object-oriented (OO) and procedural interfaces|
|There is no support for prepared statements.||support for prepared statements|
|MySQL extension added in PHP version 2.0. and deprecated as of PHP 5.5.0.||MySQLi extension added in PHP 5.5 and will work on MySQL 4.1.3 or above.|
|MySQL extension does not support stored procedure.||MySQLi supports store procedure.|
|MySQL extension lags in security and other special features, comparatively.||MySQLi extension is with enhanced security and improved debugging.|
|Transactions are handled by SQL queries only.||MySQLi supports transactions through API.|
It is strongly recommended to start using MySqli to get better security and performance feature, There is no big changes in syntax. You can find MySqli extension directory here ext/mysqli
Some other major features of Mysqli
1. Enhanced debugging capabilities.
2. Embedded server support.
3. Ability to process multiple statements.
4. MySQLi function mysqli_query() allows to enforce error prone queries and prevents bugs like SQL injection.
5. We can get buffered or unbuffered based on server resource size using MySQLi data fetch.