MySQLi Tutorial

MySQLi Introduction

MySQLi is improved version of MySQL. This improved version is built to use with PHP programming language. If you are using MySQL version 4.3 or newer, then it is recommended to use MySQLi Extension.

MySQL related extensions of PHP provide an interface between programming language and database. MySQLi extension is included in PHP version 5 and later.

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. Procedural interface has functions to perform tasks similarly as old MySQL provides, only in the place of 'mysql' prefix, 'mysqli' prefix is used in PHP Mysqli. like - mysqli_connect, mysqli_fetch_object. Object Oriented way requires first instantiate class and then call methods. Object Oriented is the most recommended way.
  • MySQLi support Prepared Statements. Using Prepared Statements, instead of passing data to 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 support 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. Like we can use "mysqli_debug('filename')" to store debugging information in a file.

MySQLi Installation

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 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 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 send back to the client machine.

How to retrieve data from MySQL