MySQLi is an improved version of MySQL.
This improved version is built to use with the PHP programming language.
If you are using MySQL version 4.3 or newer, then it is recommended using MySQLi Extension.
MySQL related extensions of PHP provide an interface between a programming language and a database. MySQLi extension is included in PHP version 5 and later. It gives various advantages over MySQL extension, as MySQLi supports prepared statements, multiple statements, transactions etc.
PHP has three main extensions to connect to the MySQL Database Server.
1. PHP MySQL Extension: php_mysql
2. PHP MySQLi Extension: php_mysqli
3. PHP PDO Extension: php_pdo_mysql
Advantages of using MySQLi over MySQL
- It provides both procedural and object-oriented Interface. The procedure interface has functions to perform tasks similarly as old MySQL provides. In PHP MySQLi, 'mysqli' prefix is used in the place of 'mysql' prefix, like - mysqli_connect, mysqli_fetch_object. The object-oriented way requires to instantiate the class first and then call methods. The object-oriented is the most recommended way.
- MySQLi supports prepared statements. Using the prepared statements, instead of passing data to a query, we can later bind it and execute the query. This process helps to prevent from SQL Injection attack.
- Using MySQLi, we can execute multiple queries all together. This improves the performance of execution.
- It supports complex transaction statements that have multiple insert queries to execute together.
- It has also embedded server support and enhanced debugging capabilities which help in the development process. As we can use "mysqli_debug('filename')" to store debugging information in a file.
If you are using PHP5 MySQL package, then the mysqli extension is automatic enabled in 'php.ini'.
To support MySQLi in PHP versions prior to 5, you must enable php_mysqli in PHP extensions or
php_mysqli.dll in 'php.ini'.
For getting more installation details, we can go to the the PHP official website.
How to retrieve data from MySQL
MySQL database is stored on a server that is different from the web server. When a user calls some webpage, it first interacts with a web server. As SQL statements are contained within the PHP code. So, the PHP processor sends the SQL statement to DBMS for processing. The DBMS will return the result to the PHP processor. The web server then combines the PHP processor results with the HTML and sends it back to the client machine.