MySQLi - Prepared Statements

A prepared statement is a feature used to execute the same (or similar) SQL statements repeatedly with high efficiency.

The mysqli_prepare() function is used to prepare a statement. A prepared statement is an SQL statement template with certain values left unspecified, called parameters (labeled "?"). For example: INSERT INTO Employee VALUES (?, ?, ?).

The database parses, compiles, and performs query optimization on the SQL statement template, and stores the result without executing it. At a later time, the application binds the values to the parameters using mysqli_stmt_bind_param() function before executing the statement. The application may execute the statement as many times as it wants with different values.

Note: The markers (?) are legal only in certain places in SQL statements. For example, they are permitted in the VALUES() list of an INSERT statement (to specify column values for a row), or in a comparison with a column in a WHERE clause to specify a comparison value.

However, they are not permitted for identifiers (such as table or column names), or to specify both operands of a binary operator such as the = equal sign. The latter restriction is necessary because it would be impossible to determine the parameter type.

In general, parameters are legal only in Data Manipulation Language (DML) statements, and not in Data Definition Language (DDL) statements.

Compared to executing SQL statements directly, prepared statements have three main advantages:

Prepared statements reduce parsing time as the preparation on the query is done only once (although the statement is executed multiple times).
Bound parameters minimize bandwidth to the server as you need send only the parameters each time, and not the whole query.
Prepared statements are very useful against SQL injections, because parameter values, which are transmitted later using a different protocol, need not be correctly escaped. If the original statement template is not derived from external input, SQL injection cannot occur.

Prepared Statements - Object-oriented style

The example below demonstrates how to prepare statements using object-oriented style.

<?php
//establishing connection to the database
$mysqli = new mysqli("localhost", "user", "password", "database");
if ($mysqli->connect_errno) {
  echo "Failed to connect to MySQL: ". $mysqli->connect_error;
  exit();
}

//preparing an SQL statement for execution
$query = "INSERT INTO Employee (Name, City, Salary) VALUES (?, ?, ?)";
$stmt = $mysqli->prepare($query);

//binding parameters
$stmt->bind_param('ssd', $name, $city, $salary);

//set parameters and execute
$name = "John";
$city = "London";
$salary = 2800;
$stmt->execute();

$name = "Marry";
$city = "Paris";
$salary = 2850;
$stmt->execute();

echo "Records inserted successfully.";

//closing the connection
$mysqli->close();
?>

The output of the above code will be:

Records inserted successfully.

Prepared Statements - Procedural style

The same can be achieved using procedural style with following script:

<?php
//establishing connection to the database
$mysqli = mysqli_connect("localhost", "user", "password", "database");
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: ". mysqli_connect_error();
  exit();
}

//preparing an SQL statement for execution
$query = "INSERT INTO Employee (Name, City, Salary) VALUES (?, ?, ?)";
$stmt = mysqli_prepare($mysqli, $query);

//binding parameters
mysqli_stmt_bind_param($stmt, 'ssd', $name, $city, $salary);

//set parameters and execute
$name = "John";
$city = "London";
$salary = 2800;
mysqli_stmt_execute($stmt);

$name = "Marry";
$city = "Paris";
$salary = 2850;
mysqli_stmt_execute($stmt);

echo "Records inserted successfully.";

//closing the connection
mysqli_close($mysqli);
?>

The output of the above code will be:

Records inserted successfully.

Complete MySQLi Reference

For a complete reference of all properties, methods and functions of PHP MySQLi extension, see MySQLi Reference.