PHP MySQL Prepared Statements

🔒 PHP MySQL Prepared Statements Tutorial

Prepared statements in PHP provide a secure way to execute SQL queries by separating SQL logic from data. They prevent SQL injection and improve efficiency when executing similar queries multiple times.

1️⃣ Why Use Prepared Statements?

Protects against SQL injection attacks
Allows reusing queries with different parameters
Improves performance for repeated queries

2️⃣ Using `mysqli` (Procedural)

<?php
$conn = mysqli_connect("localhost", "root", "", "test_db");

if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

// Prepare statement
$stmt = mysqli_prepare($conn, "INSERT INTO users (name, email, age) VALUES (?, ?, ?)");

// Bind parameters (s = string, i = integer)
mysqli_stmt_bind_param($stmt, "ssi", $name, $email, $age);

// Set values and execute
$name = "Vipul";
$email = "vipul@example.com";
$age = 25;
mysqli_stmt_execute($stmt);

$name = "Amit";
$email = "amit@example.com";
$age = 28;
mysqli_stmt_execute($stmt);

echo "Records inserted successfully";

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

<?php

$conn = mysqli_connect("localhost", "root", "", "test_db");

if (!$conn) {

die("Connection failed: " . mysqli_connect_error());

}

// Prepare statement

$stmt = mysqli_prepare($conn, "INSERT INTO users (name, email, age) VALUES (?, ?, ?)");

// Bind parameters (s = string, i = integer)

mysqli_stmt_bind_param($stmt, "ssi", $name, $email, $age);

// Set values and execute

$name = "Vipul";

$email = "vipul@example.com";

$age = 25;

mysqli_stmt_execute($stmt);

$name = "Amit";

$email = "amit@example.com";

$age = 28;

mysqli_stmt_execute($stmt);

echo "Records inserted successfully";

// Close statement and connection

mysqli_stmt_close($stmt);

mysqli_close($conn);

✅ ? placeholders are replaced with bound parameters.

3️⃣ Using `mysqli` (Object-Oriented)

<?php
$conn = new mysqli("localhost", "root", "", "test_db");

if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

// Prepare statement
$stmt = $conn->prepare("INSERT INTO users (name, email, age) VALUES (?, ?, ?)");
$stmt->bind_param("ssi", $name, $email, $age);

// Execute with different values
$name = "Ravi"; $email = "ravi@example.com"; $age = 30; $stmt->execute();
$name = "Neha"; $email = "neha@example.com"; $age = 27; $stmt->execute();

echo "Records inserted successfully";

$stmt->close();
$conn->close();
?>

<?php

$conn = new mysqli("localhost", "root", "", "test_db");

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

// Prepare statement

$stmt = $conn->prepare("INSERT INTO users (name, email, age) VALUES (?, ?, ?)");

$stmt->bind_param("ssi", $name, $email, $age);

// Execute with different values

$name = "Ravi"; $email = "ravi@example.com"; $age = 30; $stmt->execute();

$name = "Neha"; $email = "neha@example.com"; $age = 27; $stmt->execute();

echo "Records inserted successfully";

$stmt->close();

$conn->close();

"ssi" → specifies data types: string, string, integer

4️⃣ Using PDO Prepared Statements

<?php
try {
$conn = new PDO("mysql:host=localhost;dbname=test_db", "root", "");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Prepare statement
$stmt = $conn->prepare("INSERT INTO users (name, email, age) VALUES (:name, :email, :age)");

// Bind parameters
$stmt->bindParam(':name', $name);
$stmt->bindParam(':email', $email);
$stmt->bindParam(':age', $age);

// Execute multiple records
$records = [
["Vipul", "vipul@example.com", 25],
["Amit", "amit@example.com", 28],
["Ravi", "ravi@example.com", 30]
];

foreach ($records as $rec) {
$name = $rec[0];
$email = $rec[1];
$age = $rec[2];
$stmt->execute();
}

echo "Records inserted successfully";

} catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}

$conn = null;
?>

<?php

try {

$conn = new PDO("mysql:host=localhost;dbname=test_db", "root", "");

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Prepare statement

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

// Bind parameters

$stmt->bindParam(':name', $name);

$stmt->bindParam(':email', $email);

$stmt->bindParam(':age', $age);

// Execute multiple records

$records = [

["Vipul", "vipul@example.com", 25],

["Amit", "amit@example.com", 28],

["Ravi", "ravi@example.com", 30]

];

foreach ($records as $rec) {

$name = $rec[0];

$email = $rec[1];

$age = $rec[2];

$stmt->execute();

}

echo "Records inserted successfully";

} catch(PDOException $e) {

echo "Error: " . $e->getMessage();

}

$conn = null;

✅ PDO supports named placeholders (:name) and array loops for multiple inserts.

5️⃣ Key Points

Use ? or named placeholders for prepared statements.
Bind parameters with bind_param() (mysqli) or bindParam() (PDO).
Helps prevent SQL injection by separating data from SQL.
Efficient for executing same query multiple times with different values.
Always close statement and connection after use.

PHP MySQL Prepared Statements