How to use PHP MySQL Prepared Statement?

What is Prepared Statement?

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 is executed in two phases: Prepare and Execute.

1️⃣ Prepare Pahse:

At the prepare phase an SQL statement template is created and sent to the database.

SQL statement template: 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.

2️⃣ Execute Phase:

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 – MySQLi Procedural, OOP and PDO. Therefore there are three different types of syntax for prepared statements. Let’s See –

	MySQLi	OOP	PDO
Prepare	mysqli_prepare()	$mysqli->prepare()	$conn->prepare()
Data binding	mysqli_stmt_bind_param()	$mysqli->bind_param()	$stmt->bindParam()
Execute	mysqli_stmt_execute()	$mysqli->execute()	$stmt->execute()

1. Prepared Statement in PHP MySQLi procedural

<?php
$db_host = "localhost";
$db_name = "my_test_db";
$db_user = "root";
$db_pass = "";

$db_conn = mysqli_connect($db_host, $db_user, $db_pass, $db_name);
$sql_template = "INSERT INTO `users` (`name`, `age`, `email`) VALUES (?, ?, ?)";
$stmt = mysqli_prepare($db_conn, $sql_template);

mysqli_stmt_bind_param($stmt, "sis", $name, $age, $email);
$name = "John";
$age = 21;
$email = "[email protected]";

$is_inserted = mysqli_stmt_execute($stmt);

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

---

2. Prepared statements with PHP MySQLi OOP

<?php
$db_host = "localhost";
$db_name = "my_test_db";
$db_user = "root";
$db_pass = "";

$db_conn = new mysqli($db_host, $db_user, $db_pass, $db_name);
$sql_template = "INSERT INTO `users` (`name`, `age`, `email`) VALUES (?, ?, ?)";
$stmt = $db_conn->prepare($sql_template);

$stmt->bind_param("sis", $name, $age, $email);
$name = "John";
$age = 21;
$email = "[email protected]";

$is_inserted = $stmt->execute();

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

---

3. Prepared statements with PHP PDO

<?php
$db_host = "localhost";
$db_name = "my_test_db";
$db_user = "root";
$db_pass = "";

# Database Connection
try {
    $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 = "INSERT INTO `users` (`name`, `age`, `email`) VALUES (?, ?, ?)";
    $stmt = $db_conn->prepare($sql_template);

    $stmt->bindParam(1, $name, PDO::PARAM_STR);
    $stmt->bindParam(2, $age, PDO::PARAM_INT);
    $stmt->bindParam(3, $email, PDO::PARAM_STR);

    $name = "John";
    $age = 21;
    $email = "[email protected]";

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

Named placeholders

In PDO you can give names (instade of “?”) to placeholders. We have to use the colon(:) before a place holder text.

$sql_template = "INSERT INTO `users` (`name`, `age`, `email`) VALUES (:name, :age, :email)";
$stmt = $db_conn->prepare($sql_template);

$stmt->bindParam(':name', $name, PDO::PARAM_STR);
$stmt->bindParam(':age', $age, PDO::PARAM_INT);
$stmt->bindParam(':email', $email, PDO::PARAM_STR);

$name = "John";
$age = 21;
$email = "[email protected]";

$stmt->execute();