PHP mysqli prepare() Method

The PHP mysqli::prepare() / mysqli_prepare() function prepares the SQL query, and returns a statement handle to be used for further operations on the statement. The query must consist of a single SQL statement.

The statement template can contain zero or more question mark (?) parameter markers - also known as placeholders. The parameter markers must be bound to application variables using mysqli_stmt_bind_param() before executing the statement.

Syntax

//Object-oriented style
public mysqli::prepare(query)

//Procedural style
mysqli_prepare(mysql, query)

Parameters

`mysql`	`Required.` For procedural style only: Specify a mysqli object returned by mysqli_connect() or mysqli_init().
`query`	`Required.` Specify the query, as a string. It must consist of a single SQL statement. The SQL statement may contain zero or more parameter markers represented by question mark (?) characters at the appropriate positions.

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.

Return Value

Returns a statement object or false if an error occurred.

Example: Object-oriented style

The example below shows the usage of mysqli::prepare() method.

<?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 similar to:

Records inserted successfully.

Example: Procedural style

The example below shows the usage of mysqli_prepare() function.

<?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 similar to:

Records inserted successfully.

❮ PHP MySQLi Reference