PHP MySQL Prepared Statements

PHP MySQL Prepared Statements Tutorial

In PHP MySQL Prepared Statements 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.

Why Use Prepared Statements?

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

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.

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

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.

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