Introduction

Since the middle 90th ext/mysql has been the main bridge between PHP and MySQL.
Since the middle 90th ext/mysql has been the main bridge between PHP and MySQL.
But everything changed with releasing of PHP 5 and MySQL 4.1. There began to appear some serious problems.
In ext/mysql there were some �advantages that turned to disadvantages�: at first it is mysql_pconnect()[1], connection by default and automatic connection [2]. More than that there appeared some incompatibilities between ext/mysql functions and functions that were supported by the client MySQL library.
For improving these problems there were created new extension that supports new opportunities of MySQL 4.1+. That extension is ext/mysqli, where �i� is one of the following words: improved, interface, ingenious, incompatible or incomplete. [3]
Main purposes

The main purposes of creating new extension are as follows:

  • Easier maintainability. ext/mysql code became more complicated and confused. Considerable modernization of MySQL functionality required connection or disconnection of some parts depending on the version of the client library. Other problems required functionality changing depending on the operating system.
  • Better compatibility. Extension had to use the client MySQL library more prudently.
  • Backward compatibility. Although compatibility between ext/mysql and ext/mysqli is not perfect there were made some efforts for lightening the applications porting from ext/mysql to ext/mysqli.

Main features

Ext/mysqli supports new opportunities that appeased in the last versions of MySQL and provides new functions:
Main opportunities of the extension:

  • Procedural interface. It is similar to the ext/mysql interface.
  • Object-oriented interface; it allows using more simple style than the procedural interface
  • New binary MySQL protocol support (New protocol is more effective and supports new opportunities)
  • Support of all the opportunities of the client MySQL library. More than that extension supports additional monitoring functions, bug hunting functions etc.

Warnings

Some aspects of ext/mysqli are quite different from the old extension. In an effort to correct certain design flaws or bug-prone behaviors, specific features have been removed:
Default database connection. If you do not explicitly connect to the server, ext/mysqli will not do it for you.
>p >Default link. The database server connection that you wish to use must be explicitly referenced when you use ext/mysqli via its procedural context, i.e. mysqli_query($link, $query); Note that open connections (and similar resources) are automatically destroyed at the end of script execution. However, you should still close or free all connections, result sets and statement handles as soon as they are no longer required. This will help return resources to PHP and MySQL faster.
Basic usage

Here we have simple script that connects to the MeSQL server, sends the query to the server with the help of that connection, prints the results of the query and closes the connection.

<?php

/* connection to MySQL server */
$link = mysqli_connect(
"localhost", /* host */
"user", /* user name */
"password", /* password */
"world"); /* database by default */

if (!$link) {
printf("Connect error. Error code: %s\n",
mysqli_connect_error());
exit;
}

/* sending query */
if ($result = mysqli_query($link, "SELECT Name,
Population FROM City ORDER BY Population DESC LIMIT 5"
)) {

print(
"Large cities:\n");

/* Query results fetch */
while( $row = mysqli_fetch_assoc($result) ){
printf("%s (%s)\n", $row["Name"], $row["Population"]);
}

mysqli_free_result($result);
}

/* Close connection */
mysqli_close($link);
?>
Given script should print as follows:
Large cities:

Mumbai (Bombay) (10500000)
Seoul (9981619)
Sao Paulo (9968485)
Shanghai (9696300)
Jakarta (9604900)
As you can see ext/mysqli and ext/mysql can be very similar. The only major differences are that ext/mysqli is slightly more verbose when used in a procedural fashion.
Note that, without error checking, the above script could fail at any point and display an ugly error message to the user.
Using the Object-Oriented Interface

The object-oriented interface provides a slightly terser, and less error-susceptible, way to use ext/mysqli. The code below performs the same tasks as the code above, however there are a few key differences to note:
We do not need to explicitly specify the connection to use for our commands. The connection information is stored in our $mysqli and $result objects and is accessed as needed when methods are called.
When fetching rows of query data from the results set using fetch_assoc() we do not have to explicitly specify the result set handle to use. As with the connection information, the result handle is stored in the $result object.
<?php

/* Connect to a MySQL server */
$mysqli = new mysqli("localhost", "user", "password", "world");

if (
mysqli_connect_errno()) {
printf("Can"t connect to MySQL Server. Errorcode: %sn",
mysqli_connect_error());
exit;
}

/* Send a query to the server */
if ($result = $mysqli->query("
SELECT Name,
Population FROM City ORDER BY Population DESC LIMIT 5")) {

print("
Very large cities are:n");

/* Fetch the results of the query */
while( $row = $result->fetch_assoc() ){
printf("
%s (%s)n", $row["Name"], $row["Population"]);
}

/* Destroy the result set and free the memory used for it */
$result->close();
}

/* Close the connection */
$mysqli->close();
?>
Prepared Statements

Now that we have seen the basic use of the extension, let�s examine a few of the new features.
Prepared statements provide developers with the ability to create queries that are more secure, have better performance, and are more convenient to write.
They come in two flavors: bound parameter prepared statements, and bound result prepared statements.
Bound parameters

Bound parameter prepared statements allow query templates to be created and then stored on the MySQL server. When a query needs to be made, data to fill in the template is sent to the MySQL server, and a complete query is formed and then executed.
The basic process for creating and using bound parameter prepared statements is simple.
A query template is created and sent to the MySQL server. The MySQL server receives the query template, validates it to ensure that it is well-formed, parses it to ensure that it is meaningful, and stores it in a special buffer. It then returns a special handle that can later be used to reference the prepared statement.
When a query needs to be made, data to fill in the template is sent to the MySQL server, and then a complete query is formed and then executed.
This process has some very important behaviors wrapped up in it.
The body of the query is only sent to the MySQL server once. On requests to execute the query, only the data to fill in the template needs to be delivered to the MySQL server.
Most of the work required to validate and parse the query only needs to be done a single time, instead of each time that the query is executed. Additionally, for queries that contain a small amount of data, the overhead of sending the query is greatly reduced. For example, if you have a query like:

INSERT INTO City (ID, Name) VALUES (NULL, 'Calgary');

Then each time that you execute the query, you will only need to send about 16 bytes of query data, instead of 60 or more bytes. (These approximate numbers include overhead for the foo and bar query data like the id of the prepared statement, the length of the query data for binary safety, etc, but do not include extra overhead for the query string.)
The data for the query does not need to be passed through a function like mysql_real_escape_string() to ensure that no SQL injection attacks[4] occur. Instead, the MySQL client and server work together to ensure that the sent data is handled safely when it is combined with the prepared statement.
The query temtes look something like:

INSERT INTO City (ID, Name) VALUES (?, ?);

The '?' placeholders can be used in most places that could have literal data, e.g. a query could be transformed from

SELECT Name FROM City WHERE Name = 'Calgary';

to

SELECT NaFROM City WHERE name = ?;
Here is more complete example that demonstrates the following process:
<?php
$mysqli
= new mysqli("localhost", "user", "password", "world");

/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}

$stmt = $mysqli->prepare("INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");
$stmt->bind_param("sssd", $code, $language, $official, $percent);

$code = "DEU";
$language = "Bavarian";
$official = "F";
$percent = 11.2;

/* execute prepared statement */
$stmt->execute();

printf("%d Row inserted.\n", $stmt->affected_rows);

/* close statement and connection */
$stmt->close();

/* Clean up table CountryLanguage */
$mysqli->query("DELETE FROM CountryLanguage WHERE Language="Bavarian"");
printf("%d Row deleted.\n", $mysqli->affected_rows);

/* close connection */
$mysqli->close();
?>
Note that bind_param() has a short string as its first parameter. This is a format string that is used to specify how the data in the bound variables should be treated.
In the case of the above script, �sssd� indicates that the values of the first three parameters $code, $language and $official will be sent as a strings, while the fourth parameter $percent will contain a double or float value.
For each bound variable in bind_param(), there should be another letter in the format string that specifies how the variable should be handled. e.g.
$stmt->bind_param("s", $foo);
$stmt->bind_param("si", $foo, $bar);
$stmt->bind_param("sid", $foo, $bar, $baz);
The bind types let the mysqli extension know how to encode the data that it sends for greater efficiency.
The type definitions are very simple: data in the bound variables will be treated as an integer value, a rational number (double) or a string.
There is also a special type that allows long blobs to be sent to the MySQL server in chunks.
The following table shows the types and when to use them:
BIND TYPE COLUMN TYPE i All INT types d DOUBLE and FLOAT b BLOBs s All other types Bound Results

Bound result prepared statements allow the value of variables in a PHP script to be tied to the value of fields of data in a query result set.
The process of setting up this binding is:

  • Create a query
  • Ask the MySQL server to prepare the query
  • Bind PHP variables to columns in the prepared query
  • Ask the MySQL server to execute the query
  • Request that a new row of data be loaded into the bound variables.

Here is a simple code snippet that illustrates this process:
<?php
$mysqli
= new mysqli("localhost", "user", "password", "world");

if (
mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}

/* prepare statement */
if ($stmt = $mysqli->prepare("SELECT Code, Name FROM Country ORDER BY Name LIMIT 5")) {
$stmt->execute();

/* bind variables to prepared statement */
$stmt->bind_result($col1, $col2);

/* fetch values */
while ($stmt->fetch()) {
printf("%s %s\n", $col1, $col2);
}

/* close statement */
$stmt->close();
}
/* close connection */
$mysqli->close();

?>
Using Bound Parameters and Bound Results Together

Here is a more complete example that demonstrates the use of both bound parameters and bound results:
<?php
$mysqli
= new mysqli("localhost", "user", "password", "world");

if (
mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}

/* prepare statement */
if ($stmt = $mysqli->prepare("SELECT Code, Name FROM Country WHERE Code LIKE ? LIMIT 5")) {

$stmt->bind_param("s", $code);
$code = "C%";

$stmt->execute();

/* bind variables to prepared statement */
$stmt->bind_result($col1, $col2);

/* fetch values */
while ($stmt->fetch()) {
printf("%s %s\n", $col1, $col2);
}

/* close statement */
$stmt->close();
}
/* close connection */
$mysqli->close();

?>
Glossary

ext/mysql - PHP's old MySQL extension. Does not support the full feature set of MySQL version 4.1
ext/mysqli - The new MySQL extension for PHP 5. Supports the features available in MySQL versions 3.22 to 5.0
MySQL server - The component of the MySQL RDBMS that does the work of processing and responding to queries, managing the disk-level representation of data within the database, etc.
[1] - The mysql_pconnect() function was designed to provide a mechanism for reducing the cost of establishing and closing connections to the MySQL server. Unfortunately, due to an interaction between the architecture of the Apache server and the architecture of PHP, high traffic on a site that used pconnects could quickly clog up the MySQL server with many unused connections that could prevent many of the active connections from accessing the database.
[2] - The automatic connection features allowed certain function calls to automatically connect to the database (as long as valid connection data was stored in the php.ini configuration file). The default connection feature operated so that the last opened connection to a MySQL database would be the connection used for database operations unless a connection parameter were explicitly specified in the function arguments.
[3] - This extension is still in development. While the core feature set should be fairly stable, neither MySQL 4.1 nor PHP 5.0 are stable software releases. Also, the supporting feature set that does not cleanly map to the MySQL client library is still in development.
[4] - A SQL injection attack occurs when data is input into a query that cases the query to behave in an unexpected and/or malicious way. For example, given a simple query in a PHP script like "DELETE FROM grades WHERE class_name='test_$class'", an attacker who can gain control over the value of $class can force unintended deletes to occur by changing the value of $class to something like "oops' or class_name LIKE '%'".