Lesson 8 — PHP PDO and SQL CRUD Methods

Short description: This lesson teaches you how to use PHP's PDO (PHP Data Objects) to perform secure, maintainable CRUD (Create, Read, Update, Delete) operations with MySQL. It includes ready-to-use code examples, real-life business scenarios (e.g., ecommerce product management), best practices for security and performance, and patterns you can reuse across projects.

---

Why use PDO?

• Database abstraction: PDO supports many databases with the same API.
• Prepared statements: Prevent SQL injection by separating SQL from data.
• Flexible fetch modes: Fetch rows as associative arrays, objects, or numeric arrays.
• Error handling and transactions: Consistent exceptions make robust apps possible.

Sample database table (products)

Use this schema for the examples in this lesson:

CREATE TABLE products ( product_id INT AUTO_INCREMENT PRIMARY KEY, product_name VARCHAR(255) NOT NULL, product_price INT NOT NULL, product_status TINYINT(1) DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

---

PDO connection (reusable)

Always have a single, reusable function or class to create PDO connections. Set PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION so errors throw exceptions you can catch and handle.

<?php function getPDO(): PDO { $dsn = 'mysql:host=127.0.0.1;dbname=aiwephppdobasics_example_basic_ecommerce;charset=utf8mb4'; $user = 'root'; $password = ''; try { $pdo = new PDO($dsn, $user, $password, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, ]); return $pdo; } catch (PDOException $e) { // in production: log error and show friendly message die('Connection failed: ' . $e->getMessage()); } } ?>

---

CREATE — Insert a product

Use named parameters or positional parameters and bind values. Named parameters are often clearer in larger queries.

<?php $pdo = getPDO();

if ($_SERVER['REQUEST_METHOD'] === 'POST' && !empty($_POST['pr_name'])) {
$product_name = trim($_POST['pr_name']);
$product_price = (int)($_POST['price'] ?? 0);
$product_status = 1;

$sql = "INSERT INTO products (product_name, product_price, product_status)
        VALUES (:pr_name, :price, :pr_status)";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':pr_name', $product_name, PDO::PARAM_STR);
$stmt->bindParam(':price', $product_price, PDO::PARAM_INT);
$stmt->bindParam(':pr_status', $product_status, PDO::PARAM_INT);
$stmt->execute();

$id = $pdo->lastInsertId();
// fetch row after insert (useful for APIs)
$select = $pdo->prepare("SELECT * FROM products WHERE product_id = :id");
$select->execute([':id' => $id]);
$newProduct = $select->fetch();
echo '<pre>'; print_r($newProduct); echo '</pre>';


}
?>

Notes: Use lastInsertId() carefully with multi-master DBs; for typical single MySQL server it’s fine. Validate and sanitize inputs server-side before inserting.

---

READ — Selecting rows (single & multiple)

Simple select (multiple rows):

<?php $pdo = getPDO(); $minPrice = 10; $sql = "SELECT * FROM products WHERE product_price > :minPrice AND product_status = :status"; $stmt = $pdo->prepare($sql); $stmt->execute([':minPrice' => $minPrice, ':status' => 1]); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); echo '<pre>'; print_r([$stmt->rowCount(), $rows]); echo '</pre>'; ?>

Selecting a single row:

<?php $id = 2; $stmt = $pdo->prepare("SELECT * FROM products WHERE product_id = ?"); $stmt->execute([$id]); $product = $stmt->fetch(PDO::FETCH_ASSOC); if ($product) { // found } else { // not found } ?>

Best practice: Use named parameters for clarity in complex queries. Use appropriate fetch mode to reduce memory usage.

---

UPDATE — Updating rows

Bind parameters to avoid injections and to let PDO handle quoting.

<?php $pdo = getPDO(); $new_name = 'iPhone x1811'; $id = 2;

$update_sql = "UPDATE products SET product_name = :product_name WHERE product_id = :id";
$stmt = $pdo->prepare($update_sql);
$stmt->bindParam(':product_name', $new_name, PDO::PARAM_STR);
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->execute();

echo 'Rows updated: ' . $stmt->rowCount();
?>

Notes: rowCount() returns the number of affected rows. When updating multiple fields, only set the fields that changed.

---

DELETE — Removing rows

<?php $pdo = getPDO(); $idToDelete = 13; $stmt = $pdo->prepare("DELETE FROM products WHERE product_id = ?"); $stmt->execute([$idToDelete]); echo 'Deleted rows: ' . $stmt->rowCount(); ?>

Soft delete alternative: For business apps prefer a soft-delete pattern (deleted_at TIMESTAMP NULL or product_status) so you can restore items and keep historical data for audits.

---

Transactions — for safe multi-step operations

Wrap related operations in a transaction so the DB either commits all changes or none.

<?php $pdo = getPDO(); try { $pdo->beginTransaction();
// Example: create an order and reduce inventory
// insert order
// update product stock
// insert order items

$pdo->commit();


} catch (Exception $e) {
$pdo->rollBack();
// log error, show friendly message
echo 'Operation failed: ' . $e->getMessage();
}
?>

---

Security & common pitfalls

• SQL Injection: Never interpolate user data into SQL strings. Always use prepared statements.
• Input validation: Validate types, length and business rules (e.g., price >= 0).
• Least privilege DB user: The DB user should only have necessary permissions (no global admin rights).
• Error handling: Do not echo raw exceptions in production — log them instead and show a generic error to users.
• Use parameterized ORDER BY carefully: You cannot bind column names with parameters; validate and whitelist sortable columns before concatenating them.

---

Performance tips

• Use LIMIT for pagination and avoid fetching huge datasets at once.
• Add appropriate indexes (e.g., product_price, product_status) for WHERE clauses used frequently.
• Use EXPLAIN in MySQL for expensive queries to identify bottlenecks.
• Cache results where acceptable (Redis, file cache) for read-heavy pages like product catalogs.

---

Pagination & search example

Simple pagination with prepared statements:

<?php $pdo = getPDO();

$page = max(1, (int)($_GET['page'] ?? 1));
$perPage = 20;
$offset = ($page - 1) * $perPage;

$sql = "SELECT * FROM products WHERE product_status = :status ORDER BY created_at DESC LIMIT :limit OFFSET :offset";
$stmt = $pdo->prepare($sql);
// Note: LIMIT and OFFSET need integer binding via bindValue with explicit type
$stmt->bindValue(':status', 1, PDO::PARAM_INT);
$stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();
$products = $stmt->fetchAll();
?>

Search: use fulltext indexes or sanitized LIKE patterns for basic search. For large catalogs, consider a dedicated search engine (Elasticsearch, MeiliSearch).

---

Real-life business examples

1) Ecommerce product management

A small ecommerce admin panel where staff can add, edit, list, and soft-delete products. Use transactions when creating product + images + inventory rows. Validate price & stock before commit. Show friendly messages and log errors for troubleshooting.

2) Inventory and order processing

When a customer places an order you must:

1. Begin transaction
2. Check stock levels (SELECT ... FOR UPDATE to lock row if multiple workers)
3. Insert order and order items
4. Decrease inventory rows
5. Commit or rollback on error

3) Admin audit log

When admin updates a product, write a change log entry (who, when, what changed). Logs are invaluable for audit and debugging — keep them in a separate table or service.

---

Common mistakes and how to avoid them

• Building SQL strings with user input: Always use prepared statements.
• Not using transactions: For multi-step business ops you may leave data inconsistent if you don't use transactions.
• Misusing rowCount for SELECT: rowCount() is not guaranteed to return number of rows for SELECT on all drivers — use count($result) or fetchAll carefully.
• Binding LIMIT/OFFSET incorrectly: Bind them with PDO::PARAM_INT via bindValue.

---

Testing, logging and debugging

Use development and production configuration differences:

• In development: show exceptions, run queries with debug logs, and use a local DB snapshot.
• In production: log the full exception stack to a file or logging system (Sentry, Loggly) and show user-friendly errors.
• Unit-test DB logic where possible (use an in-memory test DB or a disposable Docker DB).

---

SEO & content tips (for CMS editors)

• Provide clear H1/H2 headings (we used them above).
• Write short paragraphs and include code snippets for developers searching for specific solutions.
• Include business scenarios (like inventory and order processing) — those match real search intent and help pages rank.
• Use schema.org markup for tutorials/articles if your CMS supports it.

---

Quick checklist to deploy PDO CRUD safely

• [ ] Reusable PDO connection with exceptions enabled
• [ ] Prepared statements for all queries with user input
• [ ] Input validation & sanitization
• [ ] Transactions for multi-step operations
• [ ] Soft delete if business requires historical data
• [ ] Indexes for common query columns
• [ ] Logging and monitoring in production
• [ ] Tests for critical business flows (orders, inventory)

---

Full example files (based on your lesson snippets)

insert_pdo.php (form + insert + post-insert select):

<?php // insert_pdo.php $pdo = getPDO();

$product_name = $_POST['pr_name'] ?? '';
$product_price = $_POST['price'] ?? 0;
$product_status = 1;

if (!empty($product_name)) {
$sql = "INSERT INTO products (product_name, product_price, product_status) VALUES (:pr_name, :price, :pr_status)";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':pr_name', $product_name, PDO::PARAM_STR);
$stmt->bindParam(':price', $product_price, PDO::PARAM_INT);
$stmt->bindParam(':pr_status', $product_status, PDO::PARAM_INT);
$stmt->execute();

$id = $pdo->lastInsertId();
$q = $pdo->prepare("SELECT * FROM products WHERE product_id = :id");
$q->bindParam(':id', $id, PDO::PARAM_INT);
$q->execute();
$fetch = $q->fetch(PDO::FETCH_ASSOC);
echo '<pre>'; print_r($fetch); echo '</pre>';


}
?>



Product name

Price

Submit

select_pdo.php (select by price):

<?php // select_pdo.php $pdo = getPDO(); $price = 10; $stmt = $pdo->prepare("SELECT * FROM products WHERE product_price > ? AND product_status = ?"); $stmt->execute([$price, 1]); $rows = $stmt->fetchAll(PDO::FETCH_NUM); echo '<pre>'; print_r([count($rows), $rows]); echo '</pre>'; ?>

update_pdo.php (update name):

<?php // update_pdo.php $pdo = getPDO(); $new_name = 'iPhone x1811'; $id = 2; $update = $pdo->prepare("UPDATE products SET product_name = :product_name WHERE product_id = :id"); $update->execute([':product_name' => $new_name, ':id' => $id]); var_dump($update->rowCount()); ?>

delete_pdo.php:

<?php // delete_pdo.php $pdo = getPDO(); $stmt = $pdo->prepare("DELETE FROM products WHERE product_id = ?"); $stmt->execute([13]); echo 'Deleted: ' . $stmt->rowCount(); ?>

---

Conclusion & next steps

Using PDO for CRUD in PHP is a major step toward building secure, maintainable applications. Start by extracting DB logic into functions or a small data layer (DAO or Repository pattern). Then add validation, transactions, and logging. For larger systems, consider ORM (Doctrine, Eloquent) only after understanding raw PDO operations — raw PDO knowledge helps you debug and optimize queries when ORMs leak inefficiencies.

Suggested exercises for this lesson:

1. Build a small admin page to insert, list (with pagination), update, and soft-delete products.
2. Implement an “order placement” flow using transactions and row locks (SELECT ... FOR UPDATE).
3. Add unit tests for the DAO layer using a disposable test DB.