0
0
PHPprogramming~5 mins

Prepared statements and why they matter in PHP

Choose your learning style9 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime
Introduction

Prepared statements help keep your database safe and make your code faster when running similar queries many times.

When you want to insert user data into a database safely.
When you run the same database query many times with different values.
When you want to avoid mistakes that let hackers access your data.
When you want your database to work faster with repeated queries.
Syntax
PHP
<?php
// Prepare the SQL with placeholders
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email');

// Bind values to placeholders
$stmt->bindParam(':email', $email);

// Execute the statement
$stmt->execute();

// Fetch results
$results = $stmt->fetchAll();
?>

Placeholders like :email are used instead of putting values directly in the query.

Binding values separately helps prevent harmful input from breaking your query.

Examples
Prepare an INSERT query with placeholders and run it with values in an array.
PHP
<?php
$stmt = $pdo->prepare('INSERT INTO users (name, age) VALUES (:name, :age)');
$stmt->execute(['name' => 'Alice', 'age' => 30]);
?>
Use question marks as placeholders and pass values in order as an array.
PHP
<?php
$stmt = $pdo->prepare('SELECT * FROM products WHERE price < ?');
$stmt->execute([100]);
?>
Bind a variable to a named placeholder before executing the query.
PHP
<?php
$email = 'user@example.com';
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email');
$stmt->bindParam(':email', $email);
$stmt->execute();
?>
Sample Program

This program creates a small database in memory, adds two users safely using prepared statements, then searches for one user by email and prints the result.

PHP
<?php
// Connect to database using PDO
$pdo = new PDO('sqlite::memory:');

// Create a simple table
$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)');

// Prepare an INSERT statement
$stmt = $pdo->prepare('INSERT INTO users (name, email) VALUES (:name, :email)');

// Insert two users safely
$stmt->execute(['name' => 'John', 'email' => 'john@example.com']);
$stmt->execute(['name' => 'Jane', 'email' => 'jane@example.com']);

// Prepare a SELECT statement
$select = $pdo->prepare('SELECT * FROM users WHERE email = :email');

// Search for Jane by email
$searchEmail = 'jane@example.com';
$select->execute(['email' => $searchEmail]);

// Fetch and print the result
$user = $select->fetch(PDO::FETCH_ASSOC);
if ($user) {
    echo "Found user: {$user['name']} with email {$user['email']}\n";
} else {
    echo "User not found.\n";
}
?>
OutputSuccess
Important Notes

Always use prepared statements when inserting or searching with user input to avoid security risks.

Prepared statements can improve performance if you run the same query many times with different data.

PDO is a common PHP extension that supports prepared statements with many databases.

Summary

Prepared statements separate SQL code from data to keep your database safe.

They help prevent hackers from changing your queries with bad input.

Using prepared statements is a best practice for working with databases in PHP.