PHP PDO CRUD Tutorial: Complete Guide with Prepared Statements
By Webotapp Academy•Published: •Updated:
PHP Data Objects (PDO) is the modern, secure, and flexible database abstraction layer in PHP. Whether you are building custom web apps, REST APIs, or enterprise software, learning PDO CRUD (Create, Read, Update, Delete) with prepared statements is a vital skill for every professional web developer.
In this comprehensive guide by Webotapp Academy, we break down how PDO works, why it outperforms legacy approaches like mysql_* and standard MySQLi, and walk through full, copy-paste-ready CRUD code examples with best-practice error handling.
Why Use PHP PDO Instead of MySQLi?
Before diving into the code, it helps to understand why modern PHP development relies heavily on PDO:
Frequently Asked Questions (FAQs)
QWhat is PHP PDO and why should I use it?
PHP Data Objects (PDO) is a database abstraction layer in PHP that provides a consistent, object-oriented interface for communicating with various database management systems. It supports prepared statements, named parameters, and robust exception handling to ensure secure and portable database interactions.
QHow does PDO prevent SQL injection attacks?
PDO prevents SQL injection by using prepared statements. Prepared statements separate the SQL query structure from the input parameters. The database compiles the SQL statement first, and input parameters are sent separately as raw data values, preventing user input from altering query logic.
Q
Exclusive Discount Offer
Inquire for Course Fee & Discounts
Get full program details, syllabus breakdown, and claim your exclusive course fee discount for the Best Digital Marketing Course in Guwahati.
Associative arrays, objects, lazy fetch, class mapping
Associative, numeric, array
If you ever need to migrate your database from MySQL to PostgreSQL or SQLite, PDO lets you change just the connection string without rewriting every SQL query in your codebase.
Step 1: Establishing a Secure PDO Database Connection
A secure PDO connection requires a Data Source Name (DSN), database credentials, and critical driver configuration options.
Here is the recommended production connection snippet:
<?php
// db.php — Database Connection Configuration
$host = 'localhost';
$db = 'academy_app';
$user = 'db_user';
$password = 'your_secure_password';
$charset = 'utf8mb4';
// Data Source Name (DSN)
$dsn = "mysql:host={$host};dbname={$db};charset={$charset}";
$options = [
// Throw PDOException on errors (essential for debugging & security)
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
// Default fetch mode: Associative array (e.g. $row['column_name'])
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
// Disable emulated prepared statements to ensure true native database preparation
PDO::ATTR_EMULATE_PREPARES => false,
];
try {
$pdo = new PDO($dsn, $user, $password, $options);
} catch (PDOException $e) {
// In production, log $e->getMessage() to a secure file and show a generic message
error_log("Database connection failed: " . $e->getMessage());
die("Database connection failed. Please try again later.");
}
?>
Security Tip: Always specify charset=utf8mb4 in your DSN string to fully support Unicode (including emojis) and prevent character-set-based SQL injection exploits.
Step 2: Create (INSERT Operation)
To insert a new record, always use a prepared statement with placeholders. Never concatenate variables directly into SQL strings.
There are two clean ways to bind values: using execute() with an associative array, or using bindParam() / bindValue().
Method A: Using execute() with an Array (Recommended)
<?php
require_once 'db.php';
$name = 'Rahul Sharma';
$email = 'rahul@example.com';
$course = 'Full Stack Web Development';
$sql = "INSERT INTO students (name, email, course, created_at)
VALUES (:name, :email, :course, NOW())";
try {
$stmt = $pdo->prepare($sql);
$stmt->execute([
':name' => $name,
':email' => $email,
':course' => $course,
]);
// Retrieve the auto-increment ID of the inserted record
$studentId = $pdo->lastInsertId();
echo "Student enrolled successfully with ID: " . $studentId;
} catch (PDOException $e) {
echo "Error inserting record: " . $e->getMessage();
}
?>
When searching with LIKE, pass the wildcards (%) in the parameter array, not inside the SQL string:
<?php
require_once 'db.php';
$keyword = 'Development';
$sql = "SELECT * FROM students WHERE course LIKE :keyword";
$stmt = $pdo->prepare($sql);
$stmt->execute([':keyword' => "%{$keyword}%"]);
$results = $stmt->fetchAll();
?>
Step 4: Update (UPDATE Operation)
Updating records requires prepared statements to ensure no malicious input alters the database:
<?php
require_once 'db.php';
$id = 1;
$newCourse = 'Advanced Full-Stack Engineering';
$sql = "UPDATE students SET course = :course, updated_at = NOW() WHERE id = :id";
try {
$stmt = $pdo->prepare($sql);
$stmt->execute([
':course' => $newCourse,
':id' => $id,
]);
// Check how many rows were modified
$affectedRows = $stmt->rowCount();
if ($affectedRows > 0) {
echo "Successfully updated {$affectedRows} record(s).";
} else {
echo "No records were updated (either ID was not found or course was already {$newCourse}).";
}
} catch (PDOException $e) {
echo "Update error: " . $e->getMessage();
}
?>
Step 5: Delete (DELETE Operation)
Deleting a record follows the same prepared statement pattern:
<?php
require_once 'db.php';
$id = 1;
$sql = "DELETE FROM students WHERE id = :id";
try {
$stmt = $pdo->prepare($sql);
$stmt->execute([':id' => $id]);
$deletedCount = $stmt->rowCount();
if ($deletedCount > 0) {
echo "Record ID {$id} has been permanently deleted.";
} else {
echo "No record found with ID {$id}.";
}
} catch (PDOException $e) {
echo "Delete error: " . $e->getMessage();
}
?>
Bonus: Database Transactions with PDO
When executing multiple queries that depend on each other (e.g. transferring credits, creating an invoice along with order items), use Transactions. If any step fails, you roll back all changes:
<?php
require_once 'db.php';
try {
// 1. Begin atomic transaction
$pdo->beginTransaction();
// Deduct balance from Account A
$stmt1 = $pdo->prepare("UPDATE accounts SET balance = balance - :amount WHERE id = :from_id");
$stmt1->execute([':amount' => 500, ':from_id' => 1]);
// Add balance to Account B
$stmt2 = $pdo->prepare("UPDATE accounts SET balance = balance + :amount WHERE id = :to_id");
$stmt2->execute([':amount' => 500, ':to_id' => 2]);
// 2. Commit transaction if all queries succeed
$pdo->commit();
echo "Transaction completed successfully!";
} catch (Exception $e) {
// 3. Roll back all changes if an error occurred
if ($pdo->inTransaction()) {
$pdo->rollBack();
}
echo "Transaction failed and was rolled back: " . $e->getMessage();
}
?>
Key Security Best Practices for PHP PDO
Always Use Prepared Statements: Never concatenate variables ($sql = "SELECT * FROM users WHERE email = '$email'"). This leaves your application vulnerable to SQL injection.
Disable Emulated Prepares: Set PDO::ATTR_EMULATE_PREPARES => false to ensure your database driver performs true parameter binding.
Use htmlspecialchars() on Output: Sanitizing on output protects against Cross-Site Scripting (XSS) when rendering user-submitted text in HTML.
Never Expose Raw Database Errors in Production: Catch PDOException and log the exact error to your server error log, returning a safe user-facing message.
Always Set Error Mode to Exceptions: With PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PHP throws an exception whenever a query fails, preventing silent data corruption.
Learn Full-Stack Web Development at Webotapp Academy
Mastering backend database design, PHP PDO, REST APIs, and modern frontend frameworks like React is at the core of our hands-on training.
If you are looking to become an industry-ready software developer in Northeast India, explore our flagship courses:
What is the difference between bindParam() and bindValue() in PDO?
bindParam() binds a parameter to a specified PHP variable reference; the value is evaluated only when $stmt->execute() is invoked. In contrast, bindValue() binds an immediate value directly at the moment the method is called.
QWhat is the advantage of disabling emulated prepares in PDO?
Setting PDO::ATTR_EMULATE_PREPARES => false forces PDO to use native prepared statements handled directly by the database server rather than PHP's internal emulation. This improves security and accurately checks variable data types.
QHow do I get the last inserted ID in PHP PDO?
You can retrieve the auto-increment ID of the most recently inserted row by calling $pdo->lastInsertId() immediately after executing an INSERT query.
QCan PDO be used with databases other than MySQL?
Yes. Unlike MySQLi, which only works with MySQL and MariaDB, PDO supports 12 different database drivers, including PostgreSQL, SQLite, Oracle, Microsoft SQL Server, and IBM DB2.