In the previous tutorial, you connected to MySQL and ran basic queries. But directly inserting user input into a query string opens the door to SQL injection attacks. PHP Prepared Statements solve this problem by separating your SQL structure from the actual data values, keeping your application secure.
This is Tutorial 8 of 11 in our PHP Advanced series. You will learn how PHP Prepared Statements work, then build complete Create, Read, Update, and Delete operations using them. Every example is a full, runnable script — test each one against your school_db database before continuing.
What are Prepared Statements?
PHP Prepared Statements let you write a SQL query with placeholders instead of actual values, then bind real data to those placeholders separately. MySQLi handles the safe insertion of that data internally, preventing malicious input from altering your query’s structure.
Why Prepared Statements Matter
Without PHP Prepared Statements, directly combining user input into a query string like "SELECT * FROM students WHERE name = '" . $name . "'" allows an attacker to inject extra SQL code through a form field. Prepared statements close this gap completely by treating input as pure data, never as executable SQL.
Example 1: Basic Prepared Statement Syntax
A PHP Prepared Statement uses mysqli_prepare() to create the statement, question marks as placeholders, and mysqli_stmt_bind_param() to attach real values.
<?php
require_once "config.php";
$conn = mysqli_connect($host, $username, $password, $dbname);
$stmt = mysqli_prepare($conn, "SELECT * FROM students WHERE id = ?");
mysqli_stmt_bind_param($stmt, "i", $id);
$id = 1;
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
$student = mysqli_fetch_assoc($result);
echo $student["name"];
?>The “i” passed to bind_param tells MySQLi the value is an integer. Other common type characters include “s” for string, “d” for double, and “b” for blob data.
Example 2: Create — Inserting Data Safely
Let’s build a real INSERT operation using PHP Prepared Statements, taking input from a form and safely storing it in the database.
<?php
require_once "config.php";
$conn = mysqli_connect($host, $username, $password, $dbname);
if (isset($_POST["submit"])) {
$name = $_POST["name"];
$age = $_POST["age"];
$stmt = mysqli_prepare($conn, "INSERT INTO students (name, age) VALUES (?, ?)");
mysqli_stmt_bind_param($stmt, "si", $name, $age);
mysqli_stmt_execute($stmt);
echo "Student added successfully!";
}
?>
<form method="POST">
Name: <input type="text" name="name">
Age: <input type="number" name="age">
<input type="submit" name="submit" value="Add Student">
</form>Notice the type string “si” matches the order of the placeholders — string first for name, integer second for age. Getting this order wrong is a common mistake when writing PHP Prepared Statements.
Example 3: Read — Selecting with a Search Filter
Prepared statements work just as well for SELECT queries that accept dynamic search input from a user.
<?php
require_once "config.php";
$conn = mysqli_connect($host, $username, $password, $dbname);
if (isset($_GET["search"])) {
$search = $_GET["search"];
$stmt = mysqli_prepare($conn, "SELECT * FROM students WHERE name = ?");
mysqli_stmt_bind_param($stmt, "s", $search);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
while ($row = mysqli_fetch_assoc($result)) {
echo $row["name"] . " - " . $row["age"] . "<br>";
}
}
?>
<form method="GET">
Search Name: <input type="text" name="search">
<input type="submit" value="Search">
</form>Example 4: Update — Editing an Existing Record
An UPDATE operation with PHP Prepared Statements follows the same bind-and-execute pattern, this time modifying an existing row instead of creating a new one.
<?php
require_once "config.php";
$conn = mysqli_connect($host, $username, $password, $dbname);
if (isset($_POST["update"])) {
$id = $_POST["id"];
$name = $_POST["name"];
$age = $_POST["age"];
$stmt = mysqli_prepare($conn, "UPDATE students SET name = ?, age = ? WHERE id = ?");
mysqli_stmt_bind_param($stmt, "sii", $name, $age, $id);
mysqli_stmt_execute($stmt);
echo "Student updated successfully!";
}
?>
<form method="POST">
Student ID: <input type="number" name="id">
New Name: <input type="text" name="name">
New Age: <input type="number" name="age">
<input type="submit" name="update" value="Update Student">
</form>The type string here is “sii” — string for name, integer for age, integer again for the id used in the WHERE clause, matching the exact order the placeholders appear in the SQL.
Example 5: Delete — Removing a Record
Deleting data follows the same safe pattern, using the record’s id to identify exactly which row to remove.
<?php
require_once "config.php";
$conn = mysqli_connect($host, $username, $password, $dbname);
if (isset($_POST["delete"])) {
$id = $_POST["id"];
$stmt = mysqli_prepare($conn, "DELETE FROM students WHERE id = ?");
mysqli_stmt_bind_param($stmt, "i", $id);
mysqli_stmt_execute($stmt);
echo "Student deleted successfully!";
}
?>
<form method="POST">
Student ID to Delete: <input type="number" name="id">
<input type="submit" name="delete" value="Delete Student">
</form>Example 6: Checking if the Operation Succeeded
mysqli_stmt_execute() returns true or false, letting you confirm whether the PHP Prepared Statements operation actually succeeded before showing a success message to the user.
<?php
require_once "config.php";
$conn = mysqli_connect($host, $username, $password, $dbname);
$id = 2;
$stmt = mysqli_prepare($conn, "DELETE FROM students WHERE id = ?");
mysqli_stmt_bind_param($stmt, "i", $id);
if (mysqli_stmt_execute($stmt)) {
echo "Delete successful.";
} else {
echo "Delete failed: " . mysqli_error($conn);
}
?>Example 7: A Complete Mini CRUD Flow
Let’s combine insert and select into a single working page, showing how PHP Prepared Statements fit naturally into a real application flow.
<?php
require_once "config.php";
$conn = mysqli_connect($host, $username, $password, $dbname);
if (isset($_POST["submit"])) {
$name = $_POST["name"];
$age = $_POST["age"];
$stmt = mysqli_prepare($conn, "INSERT INTO students (name, age) VALUES (?, ?)");
mysqli_stmt_bind_param($stmt, "si", $name, $age);
mysqli_stmt_execute($stmt);
}
?>
<form method="POST">
Name: <input type="text" name="name">
Age: <input type="number" name="age">
<input type="submit" name="submit" value="Add Student">
</form>
<h3>Current Students</h3>
<?php
$result = mysqli_query($conn, "SELECT * FROM students");
while ($row = mysqli_fetch_assoc($result)) {
echo $row["name"] . " (" . $row["age"] . ")<br>";
}
?>For complete official documentation on prepared statements, refer to the official PHP prepared statements manual.
What’s Next?
You now understand PHP Prepared Statements and can perform full Create, Read, Update, and Delete operations securely. In the next tutorial, we will explore PHP Namespaces and Autoloading, which help you organize larger codebases with multiple classes cleanly.
If you missed the previous lesson, check out Tutorial 7: PHP MySQLi Connection Guide, or explore all lessons in our PHP category.
Practice Exercise
Complete the following tasks to reinforce what you learned in this tutorial. Type and run every example against your own school_db database.
- Build a form that inserts a new student using PHP Prepared Statements, matching Example 2
- Build a search form that finds students by name using a prepared SELECT statement, matching Example 3
- Build an update form that changes a student’s name and age based on their id, matching Example 4
- Build a delete form that removes a student by id, matching Example 5
- Add a success/failure check using the return value of mysqli_stmt_execute(), as shown in Example 6
- Combine your insert form and a student list display on a single page, similar to Example 7
- Test what happens if you type a single quote character into the name field — confirm the prepared statement handles it safely without breaking
Bonus Challenge: Extend your mini CRUD page so that each student row in the list includes a “Delete” link that passes that student’s id to your delete script, removing the correct row without you needing to manually re-type any id numbers.
