PDO::prepare() / execute()

// Prepare an SQL statement.
$stmt = $pdo->prepare($sql);

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

// Bind a parameter by name to a variable.
$stmt->bindParam($param, $variable, $type);

// Bind a value directly to a parameter.
$stmt->bindValue($param, $value, $type);

The following sample code uses this table and data.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    age INT,
    city VARCHAR(50)
);

INSERT INTO users (name, email, age, city) VALUES
('Gojo Satoru', 'gojo@jujutsu-high.ac.jp', 28, 'Tokyo'),
('Ryomen Sukuna', 'sukuna@jujutsu-high.ac.jp', 1000, NULL),
('Itadori Yuji', 'itadori@jujutsu-high.ac.jp', 15, 'Sendai'),
('Fushiguro Megumi', 'fushiguro@jujutsu-high.ac.jp', 15, 'Tokyo'),
('Kugisaki Nobara', 'kugisaki@jujutsu-high.ac.jp', 15, 'Tokyo');

Connect to MySQL with PDO. Setting ERRMODE_EXCEPTION at connection time causes SQL errors to be thrown as exceptions, making debugging easier.

<?php
// Connect to MySQL (adjust DSN, username, and password for your environment)
$pdo = new PDO(
    'mysql:host=localhost;dbname=sample_db;charset=utf8mb4',
    'root',      // username
    'password',  // password
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,            // Throw exceptions on errors.
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,       // Fetch as associative arrays.
        PDO::ATTR_EMULATE_PREPARES => false,                    // Use native prepared statements.
    ]
);

For a detailed explanation of database connections, see PDO (Database Connection).

Verify the table contents.

SELECT * FROM users;
+----+------------------+----------------------------------+------+--------+
| id | name             | email                            | age  | city   |
+----+------------------+----------------------------------+------+--------+
|  1 | Gojo Satoru      | gojo@jujutsu-high.ac.jp          |   28 | Tokyo  |
|  2 | Ryomen Sukuna    | sukuna@jujutsu-high.ac.jp        | 1000 | NULL   |
|  3 | Itadori Yuji     | itadori@jujutsu-high.ac.jp       |   15 | Sendai |
|  4 | Fushiguro Megumi | fushiguro@jujutsu-high.ac.jp     |   15 | Tokyo  |
|  5 | Kugisaki Nobara  | kugisaki@jujutsu-high.ac.jp      |   15 | Tokyo  |
+----+------------------+----------------------------------+------+--------+
5 rows in set

pdo_prepare.php

<?php
// Execute a SELECT statement with named placeholders.
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email");
$stmt->execute([':email' => 'user@example.com']);
$user = $stmt->fetch();
echo $user['name']; // Outputs the name of the matching user.

// Using question mark placeholders.
$stmt = $pdo->prepare("SELECT * FROM users WHERE age > ? AND city = ?");
$stmt->execute([20, 'Tokyo']);
$users = $stmt->fetchAll();

// Execute an INSERT statement.
$stmt = $pdo->prepare("INSERT INTO users (name, email, age) VALUES (:name, :email, :age)");
$stmt->execute([
	':name' => 'Gojo Satoru',
	':email' => 'gojo@example.com',
	':age' => 28
]);
$newId = $pdo->lastInsertId(); // Retrieves the ID of the inserted row.
echo "Registered ID: " . $newId;

// Execute an UPDATE statement.
$stmt = $pdo->prepare("UPDATE users SET name = :name WHERE id = :id");
$stmt->execute([':name' => 'Ryomen Sukuna', ':id' => 1]);
echo $stmt->rowCount() . " row(s) updated."; // Outputs the number of updated rows.

// Execute a DELETE statement.
$stmt = $pdo->prepare("DELETE FROM users WHERE id = :id");
$stmt->execute([':id' => 5]);

// Explicitly specify types using bindValue().
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = :id AND active = :active");
$stmt->bindValue(':id', 1, PDO::PARAM_INT);
$stmt->bindValue(':active', true, PDO::PARAM_BOOL);
$stmt->execute();

// bindParam() is useful in loops — it holds a reference to the variable.
$stmt = $pdo->prepare("INSERT INTO logs (message) VALUES (:message)");
$stmt->bindParam(':message', $msg, PDO::PARAM_STR);
$messages = ['Login', 'Page view', 'Logout'];
foreach ($messages as $msg) {
	$stmt->execute(); // The current value of $msg is used on each iteration.
}

// Use a placeholder for a LIKE search.
$keyword = 'Itadori';
$stmt = $pdo->prepare("SELECT * FROM users WHERE name LIKE :keyword");
$stmt->execute([':keyword' => '%' . $keyword . '%']);

php pdo_prepare.php
Gojo Satoru
Registered ID: 6
1 row(s) updated.

sample_user_search.php

<?php
// Practical pattern: safe search with try-catch error handling.
function findUserByEmail(PDO $pdo, string $email): ?array {
	try {
		$stmt = $pdo->prepare("SELECT id, name, email FROM users WHERE email = :email LIMIT 1");
		$stmt->execute([':email' => $email]);
		$user = $stmt->fetch(PDO::FETCH_ASSOC);
		return $user !== false ? $user : null;
	} catch (PDOException $e) {
		// In production, log the error — do not expose details to the user.
		error_log("DB error in findUserByEmail: " . $e->getMessage());
		return null;
	}
}

// Practical pattern: paginated list retrieval.
function getUserList(PDO $pdo, int $page = 1, int $perPage = 20): array {
	$offset = ($page - 1) * $perPage;
	try {
		$stmt = $pdo->prepare("SELECT id, name FROM users ORDER BY id LIMIT :limit OFFSET :offset");
		$stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
		$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
		$stmt->execute();
		return $stmt->fetchAll(PDO::FETCH_ASSOC);
	} catch (PDOException $e) {
		error_log("DB error in getUserList: " . $e->getMessage());
		return [];
	}
}

Example output when calling findUserByEmail() to search by email address.

php sample_user_search.php
Array
(
    [id] => 1
    [name] => Gojo Satoru
    [email] => gojo@jujutsu-high.ac.jp
)

Embedding user input directly into an SQL string creates an SQL injection vulnerability. Always use prepared statements.

<?php
// NG: Embedding user input directly into the SQL string (SQL injection vulnerability).
$name = $_GET['name'];
$stmt = $pdo->query("SELECT * FROM users WHERE name = '$name'");
// Input like ?name=' OR '1'='1 would return all rows.

<?php
// OK: Always use a prepared statement.
$stmt = $pdo->prepare("SELECT * FROM users WHERE name = :name");
$stmt->execute([':name' => $_GET['name']]);

When using placeholders for LIMIT/OFFSET, passing the value in an array leaves it as a string type, which may cause an error depending on the driver.

<?php
// NG: When using placeholders for LIMIT/OFFSET, you must specify PARAM_INT.
$stmt = $pdo->prepare("SELECT * FROM users LIMIT :limit");
$stmt->execute([':limit' => 10]); // Passed as a string — may cause an error.

<?php
// OK: Use bindValue() with an explicit type.
$stmt = $pdo->prepare("SELECT * FROM users LIMIT :limit");
$stmt->bindValue(':limit', 10, PDO::PARAM_INT);
$stmt->execute();

When using placeholders for LIMIT/OFFSET, always use bindValue() with PDO::PARAM_INT. Passing the value in an array leaves it as a string type, which may cause an error depending on the driver.

Prepared statements separate SQL logic from data, processing them independently. Embedding user input directly into an SQL string is extremely dangerous. Always use PDO::prepare() and execute() to pass parameters through placeholders. This fundamentally prevents SQL injection attacks.

bindValue() fixes the value at the time of the call, while bindParam() uses the variable's value at the time execute() is called. Use bindParam() when executing the same statement repeatedly inside a loop. For straightforward parameter passing, the simplest approach is to pass an array directly to execute().

Connect to a database with new PDO(), and retrieve results with fetch() / fetchAll(). When executing multiple SQL statements together, consider using transactions.