Object-Oriented Structure

• MySQLi provides a set of functions for a procedural-style code structure to make the transition from MySQL easier. However, it also provides functionality through a set of object-oriented classes. Using an object-oriented style can make the MySQL integration fit better with PHP version 5 object-oriented features and often eliminates the need to create extra variables to support MySQL operations and tasks. For example, the following shows the code to connect to a database server and open a database in both MySQL and MySQLi:
  MySQL:
  $dbc = mysql_connect("localhost", "user", "password");
  $db = mysql_select_database("database");
  
  MySQLi:
  $db = new mysqli("localhost", "user", "password", "database");
  
  

Prepared Statements

• Perhaps the biggest difference between MySQL and MySQLi is MySQLi's support for prepared statements. With MySQL, you have to take special care to escape every string used in a query to prevent SQL injection attacks. With MySQLi and prepared statements, you bind a set of parameters to a query and set the parameters equal to the different values you want to use in the query. MySQLi takes care of making sure that all code is properly escaped before it is applied to the database. For example, the following code inserts two recrods into a MySQL table using MySQLi:
  
  $stmt = $db->prepare("INSERT INTO PEOPLE (FullName, Email) values (?, ?);
  $stmt->bind_param("ss", $fullname, $email);
  $fullname = "John Johnson";
  $email = "john@johnjohnson.com";
  $stmt->execute();
  $fullname = "Mary Johnson";
  $email = "mary@johnjohnson.com";
  $stmt->execute();
  $stmt->close();
  
  

Efficiency Improvements

• Because of prepared statements and other efficiency improvements, the MySQLi extension should perform faster than equivalent MySQL statements. As you can see from the previous example using prepared statements, the overhead of an insert is only required once, when the statement is prepared. With an equivalent MySQL insert, the overhead of the insert would be repeated with each insert query to the database.
  
  

Transactions

• MySQLi provides additional functionality in the form of transactions. With the MySQLi extension, you can bundle a set of data operations together into a transaction and execute the operations together as a single transaction. If one operation in the transaction fails, the entire operation fails and any changes that have been made are rolled back. For example, you might use a transaction when programming a transfer of money between two accounts, because unless both sides of the operation succeed, the operation is not a success, and you don't want to leave a state where one account has been debited but the other account has not been credited.