PHP MySQLi mysqli_prepare() - Prepared Statements

mysqli_prepare()

The mysqli_prepare() function prepares an SQL statement for execution. Instead of inserting values directly into the string, we use question marks (?) as placeholders.

EXAMPLE

<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "mysqli_database";

$connection = mysqli_connect($server, $username, $password, $database);

if (mysqli_connect_errno()) {
    echo "Connection failed: " . mysqli_connect_error();
    exit();
}

// 1. Prepare the statement (SQL template with question marks instead of values)
// NOTE: Question marks (?) act as placeholders for data. In this step, the server validates and 
// prepares the SQL logic, which completely prevents SQL injection attacks.
$stmt = mysqli_prepare($connection, "INSERT INTO mysqli_table (fruit, vegetable) VALUES (?, ?)");

// 2. Bind parameters ("ss" means both data points are of type string)
mysqli_stmt_bind_param($stmt, "ss", $fruit, $vegetable);

// 3. Set values and execute the template for the first time
$fruit = "Blueberries"; 
$vegetable = "Lettuce";
mysqli_stmt_execute($stmt);

// 4. Set new values and execute the same template for the second time
$fruit = "Peaches"; 
$vegetable = "Artichokes";
mysqli_stmt_execute($stmt);

echo "New records have been successfully created using prepared statements.";

// Close the statement and connection
mysqli_stmt_close($stmt);
mysqli_close($connection);
?>

RESULT

The result will be displayed here...

Practical use: This is the best way to prevent SQL injection attacks. The statement is sent to the server in advance, and then only the data is sent separately. This also enables faster execution if the same statement (e.g., INSERT) is repeated with different data.

It separates SQL logic from the data itself for maximum security.

It enables efficient multiple execution of the same statement.

It is used in combination with bind_param() and execute().