PDO Prepared Statements

La Lakshmi Priya Updated 04 Nov 2026
5 min read ·Lesson 228 of 229
« Previous Next »

PDO Prepared Statements Why Prepared Statements? In simple terms: A prepared statement keeps the SQL command and the data separate. You write the query with placeholders, and PDO sends the values separately (or escapes them safely). Whatever a user types — quotes, SQL keywords, special characters — is treated as plain data and can never change the query. This is the essential defense against SQL injection.

Technically: PDO::prepare() returns a PDOStatement for an SQL template with positional (?) or named (:name) placeholders. Values are supplied to execute() as an array, or bound beforehand with bindValue() / bindParam(), optionally with an explicit type (PDO::PARAM_INT, PDO::PARAM_STR, PDO::PARAM_BOOL, PDO::PARAM_NULL). A prepared statement can be executed many times with different values.

The examples use the pdo() helper from PDO Connect (exceptions enabled, FETCH_ASSOC default, native prepares).

Named Placeholders <?php require DIR . '/src/pdo.php';

$stmt = pdo()->prepare( 'SELECT name, price FROM products WHERE category = :category AND price <= :max_price ORDER BY price' );

$stmt->execute([ 'category' => 'electronics', 'max_price' => 1000, ]);

foreach ($stmt as $row) { echo "{$row['name']}: ₹{$row['price']}\n"; } Output Wireless Mouse: ₹599.00 How It Works :category and :max_price are named placeholders. Their names document what each value means — helpful in queries with many parameters. execute() receives an associative array whose keys match the placeholder names (a leading colon in the keys is optional). A PDOStatement can be iterated directly with foreach, fetching one row per iteration in the default fetch mode. The Desk Lamp (₹1,299) is excluded by the price limit. Positional Placeholders <?php require DIR . '/src/pdo.php';

$stmt = pdo()->prepare('SELECT name FROM products WHERE sku = ?');

foreach (['NB-A5', 'NOPE-99'] as $sku) { $stmt->execute([$sku]); $name = $stmt->fetchColumn();

echo $sku . ': ' . ($name === false ? 'not found' : $name) . "\n"; } Output NB-A5: Notebook A5 NOPE-99: not found ? placeholders are filled in order from a list array. fetchColumn() returns the first column of the next row, or false when there is no row. The statement is prepared once and executed twice. Positional and named placeholders cannot be mixed in one statement. Named placeholders are generally clearer; positional ones are convenient for short queries and generated lists.

Insert, Update, and Delete <?php require DIR . '/src/pdo.php';

$pdo = pdo();

$insert = $pdo->prepare('INSERT INTO customers (name, email, city) VALUES (:name, :email, :city)'); $insert->execute(['name' => 'Nila', 'email' => 'nila@example.com', 'city' => 'Madurai']);

$id = $pdo->lastInsertId(); echo "New customer ID: $id (" . get_debug_type($id) . ")\n";

$restock = $pdo->prepare('UPDATE products SET stock = stock + :quantity WHERE sku = :sku'); $restock->execute(['quantity' => 5, 'sku' => 'MOUSE-01']); echo "Rows updated: " . $restock->rowCount() . "\n";

$restock->execute(['quantity' => 5, 'sku' => 'NOPE-99']); echo "Rows updated: " . $restock->rowCount() . "\n"; Output (the ID depends on your data) New customer ID: 15 (string) Rows updated: 1 Rows updated: 0 lastInsertId() returns the generated ID as a string; cast it with (int) when an integer is needed. rowCount() returns the number of rows affected by the last INSERT, UPDATE, or DELETE executed with this statement. For MySQL, an UPDATE that does not actually change a row is not counted. Do not rely on rowCount() for SELECT statements; its behavior varies between databases. Count rows with COUNT(*) or count($rows) instead. Binding Values With Types When a parameter needs a specific type, bind it explicitly. The most important case is LIMIT and OFFSET, which must be integers:

<?php require DIR . '/src/pdo.php';

$stmt = pdo()->prepare('SELECT name, price FROM products ORDER BY price DESC LIMIT :limit'); $stmt->bindValue(':limit', 2, PDO::PARAM_INT); $stmt->execute();

foreach ($stmt->fetchAll() as $row) { echo "{$row['name']}: ₹{$row['price']}\n"; } Output Desk Lamp: ₹1299.00 Backpack: ₹899.00 Values passed in the execute() array are always treated as strings. With emulated prepares, LIMIT :limit would then become LIMIT '2', which is a syntax error. bindValue() with PDO::PARAM_INT sends a real integer and works in both modes.

Type Constant Use For PDO::PARAM_STR Strings (default), dates, decimals PDO::PARAM_INT Integers, especially LIMIT/OFFSET PDO::PARAM_BOOL Booleans PDO::PARAM_NULL SQL NULL bindValue() vs bindParam() Method Binds Value Used bindValue(':p', $value) A value The value at the time of binding bindParam(':p', $variable) A variable, by reference The variable's value at the time of execute() <?php require DIR . '/src/pdo.php';

$stmt = pdo()->prepare('SELECT stock FROM products WHERE sku = :sku'); $stmt->bindParam(':sku', $sku);

foreach (['PEN-01', 'LAMP-01'] as $sku) { $stmt->execute(); echo "$sku: " . $stmt->fetchColumn() . " in stock\n"; } Output PEN-01: 120 in stock LAMP-01: 6 in stock bindParam() binds the variable once; each execute() uses its current value. In modern code, passing values to execute([...]) is usually simpler and clearer.

Lists of Values: IN (...) <?php require DIR . '/src/pdo.php';

$skus = ['PEN-01', 'BAG-02', 'NOPE-99'];

$placeholders = implode(', ', array_fill(0, count($skus), '?')); $stmt = pdo()->prepare("SELECT sku, name FROM products WHERE sku IN ($placeholders) ORDER BY id"); $stmt->execute($skus);

print_r($stmt->fetchAll(PDO::FETCH_KEY_PAIR)); Output Array ( [PEN-01] => Gel Pen [BAG-02] => Backpack ) One placeholder is generated per value; the values are passed in the same order. (FETCH_KEY_PAIR is explained in PDO Fetch Modes.) Check for an empty list first — IN () is invalid SQL.

Identifiers Are Not Parameters Placeholders can only stand for values, not for table names, column names, ASC/DESC, or other SQL syntax. When such parts depend on input — a selectable sort column, for example — map the input to an allow-list of SQL fragments written in your code (see MySQL Order By).

Common Mistakes Concatenating Values Into SQL Effect: SQL injection and quoting errors.

Fix: Use placeholders for every value.

Quoting Placeholders Effect: WHERE sku = ':sku' compares with the literal text :sku.

Fix: Write placeholders without quotes.

Missing or Extra Parameters Effect: PDOException with SQLSTATE HY093 ("Invalid parameter number").

Fix: Supply exactly one value per placeholder, with matching names.

Passing LIMIT Values as Strings Effect: SQL syntax errors with emulated prepares.

Fix: Bind with PDO::PARAM_INT.

Mixing ? and :name Effect: Error.

Fix: Use one placeholder style per statement.

Best Practices Prepare every statement that contains values not written in your code. Prefer named placeholders for readability; use positional ones for generated lists. Pass values to execute(); use bindValue() with types where the type matters. Reuse prepared statements for repeated operations. Check rowCount() after modifications and lastInsertId() after inserts. Official reference: https://www.php.net/manual/en/pdo.prepared-statements.php

← Previous
PDO Connect
Next →
PDO Fetch Modes

More in PHP

See all
Post image PHP
PDO Fetch Modes
PDO Fetch Modes What Are Fetch Modes? In simple terms: After a query runs, PDO hands you the result row…
La Lakshmi Priya 04 Nov 2026
Post image PHP
PDO Connect
PDO Connect Connecting With PDO In simple terms: To use a database through PDO, you create a PDO object…
La Lakshmi Priya 04 Nov 2026
Post image PHP
PDO Introduction
PDO Introduction What Is PDO? In simple terms: PDO (PHP Data Objects) is PHP's standard way to talk to …
La Lakshmi Priya 04 Nov 2026
Post image PHP
Pagination with PHP
Pagination with PHP What Is Pagination? In simple terms: When a list has hundreds or thousands of entri…
La Lakshmi Priya 04 Nov 2026

0 Comments

Reviewed before they appear

No comments yet.

PHP
PHP Home PHP Switch PHP Match Alternative Syntax PHP Loops While Loop Do While Loop For Loop Foreach Loop Nested Loops Break Statement PHP Intro PHP Install PHP Built-in Web Server PHP Syntax PHP Tags PHP and HTML PHP Comments PHP Multiline Comments PHP Variables Variables Scope PHP Casting Logical Operators Null Coalescing Operator Spaceship Operator Operator Precedence Continue Statement Modify Strings PHP If...Else...Elseif PHP If Modify Strings PHP If Operators PHP Functions Concatenate Strings PHP If...Else User-defined Functions Slicing Strings Function Arguments PHP Shorthand if Escape Characters Default Argument Values PHP Nested if Pass by Reference String Interpolation Named Arguments PHP File Upload Variadic Functions Return Values Search in Strings Multibyte Strings Type Declarations in Functions PHP Numbers PHP Math PHP Floats PHP Random Numbers PHP Constants PHP Properties PHP Methods PHP Constructor Constructor Property Promotion PHP Cookies PHP Destructor PHP Access Modifiers Readonly Properties PHP Sessions PHP Inheritance Method Overriding PHP Filters The final Keyword CSRF Protection PHP Filters Advanced Session Security Secure Cookies PHP Magic Constants File Upload Security PHP Callback Functions PHP Operators Filesystem Security PHP JSON Arithmetic Operators Error Reporting in Production Hiding PHP Assignment Operators PHP Configuration (php.ini) Comparison Operators PHP Command Line (CLI) PHP Exceptions Increment / Decrement Operators Migrating to PHP 8 PHP Form Validation PHP Form Required PHP Form URL/E-mail Error Handling Form Input Sanitizing Escaping Output (htmlspecialchars) Custom Error Handler Checkbox, Radio and Select Inputs Sticky Forms PHP Form Complete PHP Date and Time Date Formatting Error Logging HTTP Headers and Redirects Output Buffering Sending Email PHP cURL Consuming REST APIs PHP Generators AJAX PHP AJAX Database AJAX XML AJAX Live Search AJAX Poll AJAX with JSON Security Introduction Password Hashing SQL Injection Prevention XSS Prevention Garbage Collection Project: Simple Blog PHP What is OOP PHP Classes/Objects The $this Keyword Access Array Items Update Array Items Add Array Items Remove Array Items Sorting Arrays Multidimensional Arrays Array Destructuring Array Spread Operator Search in Arrays Merge Arrays PHP Abstract Classes PHP Interfaces Polymorphism PHP Traits PHP Static Methods PHP Static Properties Late Static Binding Magic Methods Object Cloning MySQL Database MySQL Connect MySQL Create DB MySQL Create Table MySQL Insert Data MySQL Get Last ID MySQL Insert Multiple MySQL Prepared MySQL Select Data MySQL Where Debugging PHP Project: Image Upload Gallery $_SESSION $_ENV PHP RegEx RegEx Patterns Heredoc and Nowdoc RegEx Modifiers preg_match preg_replace preg_split PHP RegEx Functions Nullable Types Mixed and Never Types Nullsafe Operator PHP Attributes PHP Fibers Readonly Classes Property Hooks (PHP 8.4) Asymmetric Visibility (PHP 8.4) Reflection API SPL Data Structures Composer PHP Form Handling PDO Transactions PDO Error Handling PHP XML Parsers PHP SimpleXML Parser PHP SimpleXML - Get PHP XML Expat Parser PHP DOM Parser PHP XMLReader PHP XMLWriter AJAX Intro PHP Class Constants Encryption with Sodium Recursive Functions Variable Functions Anonymous Functions Closures Arrow Functions First-class Callable Syntax PHP Arrays Indexed Arrays Associative Arrays Create Arrays Variables Scope Static Variables Variable Variables PHP Echo / Print PHP printf / sprintf PHP Data Types PHP Booleans PHP NULL PHP Strings String Functions array_map / array_filter / array_reduce Array Functions PHP Superglobals $GLOBALS What Is `$_SERVER` ? $_REQUEST $_POST $_GET $_FILES $_COOKIE PHP Type Juggling MySQL Order By MySQL Delete Data MySQL Update Data MySQL Limit Data MySQL Joins with PHP Pagination with PHP PDO Introduction PDO Connect PDO Prepared Statements PDO Fetch Modes
Ask about this post
AI Ask about this post

Ask questions about PDO Prepared Statements and get answers drawn from it.

Signed-in readers only.