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 templates 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 Name FROM City WHERE name = ?;
 
Here is a more complete example that demonstrates the entire 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