PDO::prepare() / execute() Since: PHP 5（2004）

Prepares an SQL statement as a prepared statement, binds parameters, and executes it safely. Always use prepared statements for SQL queries that include user input to prevent SQL injection attacks.

Syntax

// 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);

Method List

Method	Description
PDO::prepare($sql)	Prepares an SQL statement as a prepared statement. Use `?` or `:name` as placeholders.
PDOStatement::execute($params)	Executes the prepared statement. Pass an array of parameters to bind values at execution time.
PDOStatement::bindParam($param, &$var, $type)	Binds a parameter to a variable by reference. The variable's value at the time `execute()` is called is used.
PDOStatement::bindValue($param, $value, $type)	Binds a value directly to a parameter. The value is fixed at the time of the call.

Parameter Type Constants

Constant	Description
PDO::PARAM_STR	Binds as a string. This is the default type.
PDO::PARAM_INT	Binds as an integer.
PDO::PARAM_BOOL	Binds as a boolean.
PDO::PARAM_NULL	Binds as NULL.

Sample Code

<?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' => 'John Smith',
	':email' => 'john@example.com',
	':age' => 30
]);
$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' => 'Jane Smith', ':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 = 'Smith';
$stmt = $pdo->prepare("SELECT * FROM users WHERE name LIKE :keyword");
$stmt->execute([':keyword' => '%' . $keyword . '%']);

Notes

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.

If you find any errors or copyright issues, please contact us.

Home