If you've already connected MySQL using MySQLi, it's time to meet its more flexible cousin - PDO (PHP Data Objects). In this tutorial we'll start from a clean PDO connection and build it up step by step into a full working CRUD app, complete with an HTML form, an editable records table, and update/delete actions. By the end, you'll have a reusable db.php file and a single-page CRUD example you can drop straight into any PHP 8 project.
PDO (PHP Data Objects) is a database access layer that works with 12+ different databases (MySQL, PostgreSQL, SQLite, SQL Server, and more) using the exact same set of functions. That means if you ever switch your project from MySQL to PostgreSQL, your PDO code barely changes - only the connection string does.
Here's a quick comparison so you know what you're getting into:
| Feature | MySQLi | PDO |
|---|---|---|
| Database support | MySQL only | 12+ databases |
| API style | Procedural & OOP | OOP only |
| Named placeholders | No | Yes (:name) |
| Prepared statements | Yes | Yes |
| Exceptions | Optional | Built-in (recommended) |
Open phpMyAdmin or run this SQL to create a simple contacts table:
CREATE TABLE contacts (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) NOT NULL,
phone VARCHAR(20) DEFAULT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;Just like our MySQLi tutorial, we'll keep the connection logic in its own file so it can be reused across every page. Create db.php in your project root:
<?php
function db_connect()
{
static $pdo;
if ($pdo instanceof PDO) {
return $pdo;
}
$host = "localhost";
$dbname = "your_db_name"; // Replace with your database name
$user = "root";
$pass = ""; // Your MySQL password
$charset = "utf8mb4";
$dsn = "mysql:host=$host;dbname=$dbname;charset=$charset";
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
];
try {
$pdo = new PDO($dsn, $user, $pass, $options);
} catch (PDOException $e) {
die("Connection failed: " . $e->getMessage());
}
return $pdo;
}static $pdo - reuses the same connection across multiple calls in one request, just like the static $conn trick in MySQLi.PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION - makes PDO throw exceptions on errors instead of failing silently. This is the single most important setting for PDO.PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC - every query returns associative arrays by default, so you don't need to repeat the fetch mode each time.PDO::ATTR_EMULATE_PREPARES => false - forces MySQL to use real prepared statements instead of PHP emulating them, which is both safer and faster.try/catch block wraps the connection so any failure is caught gracefully instead of dumping a raw error to the browser.try/catch block too - we'll do exactly that.With PDO, you prepare a query once and bind values to named placeholders like :name instead of plain ? - much easier to read when a query has several fields.
<?php
require_once "db.php";
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
$pdo = db_connect();
$name = trim($_POST['name']);
$email = trim($_POST['email']);
$phone = trim($_POST['phone']);
try {
$stmt = $pdo->prepare(
"INSERT INTO contacts (name, email, phone) VALUES (:name, :email, :phone)"
);
$stmt->execute([
':name' => $name,
':email' => $email,
':phone' => $phone,
]);
$newId = $pdo->lastInsertId();
header("Location: index.php?msg=added");
exit;
} catch (PDOException $e) {
die("Insert failed: " . $e->getMessage());
}
}Notice $pdo->lastInsertId() - this is PDO's equivalent of MySQLi's mysqli_stmt_insert_id(), returning the auto-incremented ID of the row you just added.
Fetching data with PDO is refreshingly short, thanks to fetchAll():
<?php
require_once "db.php";
$pdo = db_connect();
$stmt = $pdo->query("SELECT * FROM contacts ORDER BY id DESC");
$contacts = $stmt->fetchAll();For queries with user input - like a search filter - always switch to prepare() with bound parameters instead of query():
$stmt = $pdo->prepare("SELECT * FROM contacts WHERE id = :id");
$stmt->execute([':id' => $_GET['id']]);
$contact = $stmt->fetch();<?php
require_once "db.php";
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
$pdo = db_connect();
$id = $_POST['id'];
$name = trim($_POST['name']);
$email = trim($_POST['email']);
$phone = trim($_POST['phone']);
try {
$stmt = $pdo->prepare(
"UPDATE contacts SET name = :name, email = :email, phone = :phone WHERE id = :id"
);
$stmt->execute([
':name' => $name,
':email' => $email,
':phone' => $phone,
':id' => $id,
]);
header("Location: index.php?msg=updated");
exit;
} catch (PDOException $e) {
die("Update failed: " . $e->getMessage());
}
}<?php
require_once "db.php";
if (isset($_GET['id'])) {
$pdo = db_connect();
try {
$stmt = $pdo->prepare("DELETE FROM contacts WHERE id = :id");
$stmt->execute([':id' => $_GET['id']]);
header("Location: index.php?msg=deleted");
exit;
} catch (PDOException $e) {
die("Delete failed: " . $e->getMessage());
}
}:id bound through execute() - never insert $_GET['id'] directly into the SQL string. That's how SQL injection happens.Now let's combine everything into one practical example - a single index.php with an "Add Contact" form on top and a list of contacts below, each row with Edit and Delete buttons. This is the beginner-friendly all-in-one version; in production you'd usually split add/edit/delete into separate files like the ones above.
<?php
require_once "db.php";
$pdo = db_connect();
// Handle Add / Update
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
$name = trim($_POST['name']);
$email = trim($_POST['email']);
$phone = trim($_POST['phone']);
if (!empty($_POST['id'])) {
// Update existing
$stmt = $pdo->prepare(
"UPDATE contacts SET name=:name, email=:email, phone=:phone WHERE id=:id"
);
$stmt->execute([
':name' => $name, ':email' => $email,
':phone' => $phone, ':id' => $_POST['id'],
]);
} else {
// Insert new
$stmt = $pdo->prepare(
"INSERT INTO contacts (name, email, phone) VALUES (:name, :email, :phone)"
);
$stmt->execute([':name' => $name, ':email' => $email, ':phone' => $phone]);
}
header("Location: index.php");
exit;
}
// Handle Delete
if (isset($_GET['delete'])) {
$stmt = $pdo->prepare("DELETE FROM contacts WHERE id = :id");
$stmt->execute([':id' => $_GET['delete']]);
header("Location: index.php");
exit;
}
// Load record for editing (if ?edit=ID in URL)
$editContact = null;
if (isset($_GET['edit'])) {
$stmt = $pdo->prepare("SELECT * FROM contacts WHERE id = :id");
$stmt->execute([':id' => $_GET['edit']]);
$editContact = $stmt->fetch();
}
// Fetch all contacts
$contacts = $pdo->query("SELECT * FROM contacts ORDER BY id DESC")->fetchAll();
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>PDO CRUD Example</title>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css">
</head>
<body>
<div class="container mt-4">
<h2>Contacts</h2>
<!-- Add / Edit Form -->
<form method="POST" class="row g-2 mb-4">
<input type="hidden" name="id" value="<?= $editContact['id'] ?? '' ?>">
<div class="col-md-3">
<input type="text" name="name" class="form-control" placeholder="Name"
value="<?= htmlspecialchars($editContact['name'] ?? '') ?>" required>
</div>
<div class="col-md-3">
<input type="email" name="email" class="form-control" placeholder="Email"
value="<?= htmlspecialchars($editContact['email'] ?? '') ?>" required>
</div>
<div class="col-md-3">
<input type="text" name="phone" class="form-control" placeholder="Phone"
value="<?= htmlspecialchars($editContact['phone'] ?? '') ?>">
</div>
<div class="col-md-3">
<button class="btn btn-primary w-100">
<?= $editContact ? 'Update Contact' : 'Add Contact' ?>
</button>
</div>
</form>
<!-- Contacts Table -->
<table class="table table-bordered table-striped">
<thead class="table-dark">
<tr><th>ID</th><th>Name</th><th>Email</th><th>Phone</th><th>Actions</th></tr>
</thead>
<tbody>
<?php if ($contacts): foreach ($contacts as $c): ?>
<tr>
<td><?= $c['id'] ?></td>
<td><?= htmlspecialchars($c['name']) ?></td>
<td><?= htmlspecialchars($c['email']) ?></td>
<td><?= htmlspecialchars($c['phone']) ?></td>
<td>
<a href="?edit=<?= $c['id'] ?>" class="btn btn-sm btn-warning">Edit</a>
<a href="?delete=<?= $c['id'] ?>" class="btn btn-sm btn-danger"
onclick="return confirm('Delete this contact?')">Delete</a>
</td>
</tr>
<?php endforeach; else: ?>
<tr><td colspan="5">No contacts found.</td></tr>
<?php endif; ?>
</tbody>
</table>
</div>
</body>
</html>This single file gives you a fully working Add / Edit / Update / Delete flow: submitting the form without an id inserts a new contact, submitting with an id hidden field updates it, and clicking Edit pre-fills the form by passing ?edit=ID in the URL.
When multiple related queries must all succeed or all fail together, wrap them in a transaction:
try {
$pdo->beginTransaction();
$pdo->prepare("UPDATE accounts SET balance = balance - :amt WHERE id = :from")
->execute([':amt' => 100, ':from' => 1]);
$pdo->prepare("UPDATE accounts SET balance = balance + :amt WHERE id = :to")
->execute([':amt' => 100, ':to' => 2]);
$pdo->commit();
} catch (PDOException $e) {
$pdo->rollBack();
die("Transaction failed: " . $e->getMessage());
}By default PDO binds everything as a string. For strict type matching (rare, but useful with LIMIT clauses), use bindValue():
$stmt = $pdo->prepare("SELECT * FROM contacts LIMIT :limit");
$stmt->bindValue(':limit', 10, PDO::PARAM_INT);
$stmt->execute();$page = max(1, (int) ($_GET['page'] ?? 1));
$limit = 10;
$offset = ($page - 1) * $limit;
$stmt = $pdo->prepare("SELECT * FROM contacts ORDER BY id DESC LIMIT :limit OFFSET :offset");
$stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();
$rows = $stmt->fetchAll();Always set ERRMODE_EXCEPTION. It surfaces problems early instead of failing silently.
Use named placeholders for readability. Especially helpful in queries with five or more fields.
Validate before you insert. Trim input, check required fields, and validate email format with filter_var() before saving.
Always escape output with htmlspecialchars(). PDO protects your queries, but it does nothing to protect your HTML output from XSS.
Keep credentials out of version control. Move host/user/pass into a separate config file excluded via .gitignore.
Before we wrap up, here's a handy cheat-sheet of every PDO method and constant used (or worth knowing) across this tutorial - bookmark this section for quick reference.
| Method | What It Does |
|---|---|
new PDO($dsn, $user, $pass, $options) |
Opens a new database connection using a DSN string, credentials, and optional attributes. |
$pdo->setAttribute() |
Sets a connection attribute after the connection is created (e.g. error mode, fetch mode). |
$pdo->getAttribute() |
Reads the current value of a connection attribute. |
| Method | What It Does |
|---|---|
$pdo->query($sql) |
Runs a SQL query immediately and returns a PDOStatement. Use only when there's no user input (no placeholders needed). |
$pdo->exec($sql) |
Runs a SQL statement that doesn't return a result set (INSERT/UPDATE/DELETE/DDL) and returns the number of affected rows. |
$pdo->prepare($sql) |
Compiles a SQL query with placeholders (? or :name) into a PDOStatement, ready to bind values and execute. Always use this when user input is involved. |
$stmt->execute($params) |
Runs a prepared statement, optionally passing an array of values to bind to its placeholders. |
$stmt->bindParam($param, $var, $type) |
Binds a PHP variable to a placeholder by reference; the value is read at execute time, so it reflects later changes to the variable. |
$stmt->bindValue($param, $value, $type) |
Binds a fixed value to a placeholder immediately. The most common choice - use with PDO::PARAM_INT etc. for explicit typing. |
| Method | What It Does |
|---|---|
$stmt->fetch() |
Retrieves the next single row from a result set (as array, object, etc. depending on fetch mode). |
$stmt->fetchAll() |
Retrieves all remaining rows from a result set as an array. |
$stmt->fetchColumn($index) |
Retrieves a single column value from the next row - handy for counts or single-value lookups. |
$stmt->fetchObject($class) |
Retrieves the next row as an object, optionally of a custom class. |
$stmt->rowCount() |
Returns the number of rows affected by the last DELETE/UPDATE/INSERT, or matched by a SELECT on some drivers. |
$stmt->setFetchMode() |
Changes how rows are returned (assoc array, object, numeric array, etc.) for a specific statement. |
| Method | What It Does |
|---|---|
$pdo->lastInsertId() |
Returns the auto-increment ID generated by the most recent INSERT. |
$pdo->errorInfo() |
Returns detailed error info (SQLSTATE code, driver code, message) for the last operation on the PDO object. |
$stmt->errorInfo() |
Same as above, but for the last operation on a specific statement. |
$pdo->errorCode() |
Returns just the SQLSTATE error code for the last PDO operation. |
| Method | What It Does |
|---|---|
$pdo->beginTransaction() |
Turns off autocommit and starts a transaction; following queries won't be saved until committed. |
$pdo->commit() |
Saves all queries run since beginTransaction() permanently to the database. |
$pdo->rollBack() |
Undoes all queries run since beginTransaction() if something went wrong. |
$pdo->inTransaction() |
Checks whether a transaction is currently active. |
| Method | What It Does |
|---|---|
$stmt->closeCursor() |
Frees the statement's result set so the connection can be reused for another query - needed when not fetching all rows before running a new query. |
$pdo->quote($string) |
Manually escapes a string for safe inclusion in SQL. Rarely needed since prepared statements handle this automatically, but useful for dynamic identifiers/values outside bound params. |
| Constant | What It Does |
|---|---|
PDO::ATTR_ERRMODE |
Controls how errors are reported. PDO::ERRMODE_EXCEPTION is recommended. |
PDO::ATTR_DEFAULT_FETCH_MODE |
Sets the default row format returned by fetch methods. PDO::FETCH_ASSOC is most common. |
PDO::ATTR_EMULATE_PREPARES |
Set to false to force real, server-side prepared statements instead of PHP-emulated ones. |
PDO::PARAM_STR |
Tells PDO to treat a bound value as a string. |
PDO::PARAM_INT |
Tells PDO to treat a bound value as an integer. |
PDO::PARAM_BOOL |
Tells PDO to treat a bound value as a boolean. |
PDO::PARAM_NULL |
Tells PDO to treat a bound value as SQL NULL. |
In this tutorial you learned:
db.php connection file with proper error handling