PHP mysqli_stmt prepare() Method

The PHP mysqli_stmt::prepare() / mysqli_stmt_prepare() function prepares the SQL statement for execution. 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.

Note: When passing a statement longer than max_allowed_packet of the server, this function returns different error codes depending on whether MySQL Native Driver (mysqlnd) or MySQL Client Library (libmysqlclient) is being used. The behavior is as follows:

mysqlnd on Linux returns an error code of 1153. The error message means got a packet bigger than max_allowed_packet bytes.
mysqlnd on Windows returns an error code 2006. This error message means server has gone away.
libmysqlclient on all platforms returns an error code 2006. This error message means server has gone away.

Syntax

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

//Procedural style
mysqli_stmt_prepare(statement, query)

Parameters

`statement`	`Required.` For procedural style only: Specify a mysqli_stmt object returned by mysqli_stmt_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 true on success or false on failure.

Example: Object-oriented style

The example below shows the usage of mysqli_stmt::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();
}

//creating a prepared statement
$stmt = $mysqli->stmt_init();
$query = "INSERT INTO Employee (Name, City, Salary) VALUES (?, ?, ?)";
$stmt->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_stmt_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();
}

//creating a prepared statement
$stmt = mysqli_stmt_init($mysqli);
$query = "INSERT INTO Employee (Name, City, Salary) VALUES (?, ?, ?)";
mysqli_stmt_prepare($stmt, $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