How to Implement MySQL Prepared Statement in PHP

A prepared statement is a feature used to execute the same SQL statements repeatedly with high efficiency and protect against SQL injections.

Basic workflow of the prepared statement

A prepared statement executes in two phases: 1) preparation, and 2) execution.

Phase 1: Preparation

At the prepare Phase an SQL statement template is created and sent to the database. An SQL statement where certain values are left unspecified, called parameters (labelled through “?”).

Example –

// SQL statement template
INSERT INTO `users` (name, age, email) VALUES (?,?,?)

After receiving the SQL statement template, the database performs a syntax check on SQL statement, and stores the result without executing it.

Phase 2: Execution

During the execute phase, the application binds the values to the parameters, and then the database executes the statement.

The application can execute the statement as many times as it wants with different values.

Here is an example –

<?php
$users = [
    ["John", 21, "[email protected]"],
    ["Rahul", 32, "[email protected]"],
    ["Mark", 33, "[email protected]"],
    ["Baburao", 65, "[email protected]"]
];

$db_conn = mysqli_connect("localhost", "root", "", "my_test_db");

/**
 * In the following SQL template
 * There are three ? marks
 * because there are three places name, age, and email
 */
$sql_template = "INSERT INTO `users` (`name`, `age`, `email`) VALUES (?, ?, ?)";

/**
 * Another SQL template example to understand the placeholder (?)
 *  ↓ First ? is for `name` and second ? is for `age` (Sequence Matters)
 * SELECT * FROM `users` WHERE `name` = ? and `age` = ?
 */

# Preparing the SQL statement
$stmt = mysqli_prepare($db_conn, $sql_template);

# Data binding
/**
 * "sis" is data type (you have to define the data type)
 * where s means it is a string
 * and i means it is an integer
 *
 * $name is s(string)
 * $age is i(integer)
 * $email is s(string)
 *
 * see the following table for more data types
 */
mysqli_stmt_bind_param($stmt, "sis", $name, $age, $email);

foreach ($users as $user) {
    # Executing the same sql insert query multiple times with defferent values
    $name = $user[0];
    $age = $user[1];
    $email = $user[2];
    # Executing the statement after binding the data
    mysqli_stmt_execute($stmt);
}

Character	Data type
i	Integer
d	Double (decimal number)
s	String
b	blob(Binary Large Object)

Examples of PHP prepared statements:

PHP has three types of database API – 1) MySQLi Procedural, 2) OOP and 3) PDO. Therefore there are three different types of syntax for prepared statements. Let’s See –

1. Prepared Statement in PHP MySQLi procedural:

• Prepare – mysqli_prepare()
• Data Binding – mysqli_stmt_bind_param()
• Execute – mysqli_stmt_execute()

<?php
// Database connection parameters
$db_host = "localhost"; // Hostname
$db_name = "my_test_db"; // Database name
$db_user = "root"; // Database username
$db_pass = ""; // Database password

// Establishing a database connection
$db_conn = mysqli_connect($db_host, $db_user, $db_pass, $db_name);

// SQL template for insertion
$sql_template = "INSERT INTO `users` (`name`, `age`, `email`) VALUES (?, ?, ?)";

// Prepare SQL statement
$stmt = mysqli_prepare($db_conn, $sql_template);

// Binding parameters to the prepared statement
mysqli_stmt_bind_param($stmt, "sis", $name, $age, $email);

// Setting parameter values
$name = "John"; // User's name
$age = 21; // User's age
$email = "[email protected]"; // User's email

// Executing the prepared statement
$is_inserted = mysqli_stmt_execute($stmt);

// Checking if insertion was successful
if ($is_inserted) {
    echo "Data inserted successfully.";
} else {
    echo "Something went wrong!";
}

---

2. Prepared statements with PHP MySQLi OOP:

• Prepare – $mysqli->prepare()
• Data Binding – $mysqli->bind_param()
• Execute – $mysqli->execute()

<?php
// Database connection parameters
$db_host = "localhost"; // Hostname
$db_name = "my_test_db"; // Database name
$db_user = "root"; // Database username
$db_pass = ""; // Database password

// Establishing a database connection using object-oriented style
$db_conn = new mysqli($db_host, $db_user, $db_pass, $db_name);

// SQL template for insertion
$sql_template = "INSERT INTO `users` (`name`, `age`, `email`) VALUES (?, ?, ?)";

// Prepare SQL statement
$stmt = $db_conn->prepare($sql_template);

// Binding parameters to the prepared statement
$stmt->bind_param("sis", $name, $age, $email);

// Setting parameter values
$name = "John"; // User's name
$age = 21; // User's age
$email = "[email protected]"; // User's email

// Executing the prepared statement
$is_inserted = $stmt->execute();

// If data is inserted successfully
if ($is_inserted) {
    echo "Data inserted successfully.";
} else {
    echo "Something went wrong!";
}

---

3. Prepared statements with PHP PDO:

• Prepare – $conn->prepare()
• Data Binding – $stmt->bindParam()
• Execute – $stmt->execute()

<?php
$db_host = "localhost"; // Database hostname
$db_name = "my_test_db"; // Database name
$db_user = "root"; // Database username
$db_pass = ""; // Database password

# Database Connection
try {
    // Creating a PDO connection to the database
    $db_conn = new PDO("mysql:host=$db_host;dbname=$db_name", $db_user, $db_pass);
    // Set the PDO error mode to exception
    $db_conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo "Connection Failed" . $e->getMessage();
}

# Inserting data
try {
    // SQL template for insertion
    $sql_template = "INSERT INTO `users` (`name`, `age`, `email`) VALUES (?, ?, ?)";
    // Prepare SQL statement
    $stmt = $db_conn->prepare($sql_template);

    // Binding parameters to the prepared statement
    $stmt->bindParam(1, $name, PDO::PARAM_STR);
    $stmt->bindParam(2, $age, PDO::PARAM_INT);
    $stmt->bindParam(3, $email, PDO::PARAM_STR);

    // Setting parameter values
    $name = "John"; // User's name
    $age = 21; // User's age
    $email = "[email protected]"; // User's email

    // Executing the prepared statement
    $stmt->execute();
    echo "Data inserted successfully";
} catch (PDOException $e) {
    echo "data not inserted";
}