PHP PDO::exec Method
last modified April 19, 2025
The PDO::exec method executes an SQL statement and returns the number of affected rows. It's useful for INSERT, UPDATE, DELETE, and other statements that don't return result sets.
Basic Definition
PDO::exec executes an SQL statement in a single function call. It returns the number of rows affected by the statement. For SELECT statements, use PDO::query instead.
Syntax: public PDO::exec(string $statement): int|false.
The method returns false on failure. Always check the return value.
Basic PDO::exec Usage
This shows the simplest usage of PDO::exec to create a table.
<?php
declare(strict_types=1);
try {
$pdo = new PDO('sqlite:mydatabase.db');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$count = $pdo->exec("CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE
)");
echo "Table created successfully";
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
This creates a new SQLite database table. The exec method returns 0 for CREATE TABLE statements since no rows are affected. Always use try-catch for error handling.
Inserting Data with PDO::exec
This demonstrates inserting data into a table using PDO::exec.
<?php
declare(strict_types=1);
try {
$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$count = $pdo->exec("INSERT INTO users (name, email)
VALUES ('John Doe', 'john@example.com'),
('Jane Smith', 'jane@example.com')");
echo "Inserted $count rows";
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
This inserts two rows into the users table. The exec method returns the number of affected rows (2 in this case). Note this approach is vulnerable to SQL injection with user input.
Updating Data with PDO::exec
This shows how to update records using PDO::exec.
<?php
declare(strict_types=1);
try {
$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$count = $pdo->exec("UPDATE users SET email = 'newjohn@example.com'
WHERE name = 'John Doe'");
if ($count > 0) {
echo "Updated $count rows";
} else {
echo "No rows updated";
}
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
This updates email addresses for users named 'John Doe'. The method returns the number of updated rows. Always check if rows were actually modified.
Deleting Data with PDO::exec
This demonstrates deleting records from a table.
<?php
declare(strict_types=1);
try {
$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$count = $pdo->exec("DELETE FROM users WHERE email LIKE '%example.com'");
echo "Deleted $count rows";
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
This deletes all users with example.com email addresses. The LIKE operator matches patterns. The method returns the number of deleted rows. Use caution with DELETE statements.
Using PDO::exec with Transactions
This shows PDO::exec within a transaction for atomic operations.
<?php
declare(strict_types=1);
try {
$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->beginTransaction();
$pdo->exec("UPDATE accounts SET balance = balance - 100 WHERE user_id = 1");
$pdo->exec("UPDATE accounts SET balance = balance + 100 WHERE user_id = 2");
$pdo->commit();
echo "Transaction completed successfully";
} catch (PDOException $e) {
$pdo->rollBack();
echo "Transaction failed: " . $e->getMessage();
}
This performs a money transfer between accounts atomically. Both updates succeed or fail together. Always use rollBack in catch blocks to maintain data consistency.
PDO::exec with DDL Statements
This demonstrates using PDO::exec for database schema modifications.
<?php
declare(strict_types=1);
try {
$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec("ALTER TABLE users ADD COLUMN age INT");
$pdo->exec("CREATE INDEX idx_email ON users(email)");
echo "Database schema modified successfully";
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
This adds a new column to the users table and creates an index. DDL statements (CREATE, ALTER, DROP) typically return 0 rows affected. These operations are often not transactional.
Security Considerations with PDO::exec
This highlights the security risks of using PDO::exec with user input.
<?php
declare(strict_types=1);
// UNSAFE EXAMPLE - DO NOT USE IN PRODUCTION
try {
$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$name = $_GET['name']; // User input - potential SQL injection!
$count = $pdo->exec("DELETE FROM users WHERE name = '$name'");
echo "Deleted $count rows";
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
// SAFE ALTERNATIVE USING PREPARED STATEMENTS
try {
$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $pdo->prepare("DELETE FROM users WHERE name = ?");
$stmt->execute([$_GET['name']]);
echo "Deleted " . $stmt->rowCount() . " rows";
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
The first example is vulnerable to SQL injection attacks. Never directly interpolate user input into SQL. Always use prepared statements with bound parameters for user-supplied data.
Best Practices
- Avoid User Input: Never use PDO::exec with user input directly.
- Error Handling: Always use try-catch blocks.
- Check Return Values: Verify affected rows count.
- Transactions: Use for multiple related operations.
- Prepared Statements: Preferred for data modification.
Source
This tutorial covered the PDO::exec method with practical examples showing its usage in different database operations.
Author
List all PHP PDO Functions.