Lesson 21 • Advanced

Advanced PDO 🗄️

Master transactions, batch inserts, typed bindings, stored procedures, and fetch modes for safe, high-performance database access.

What You'll Learn in This Lesson

• Transactions: ensure all-or-nothing database operations
• Batch inserts: prepare once, execute many times
• Typed parameter binding with PDO::PARAM constants
• Call MySQL stored procedures from PHP
• Choose the right fetch mode for your use case

Note: PHP runs on a server. Our examples simulate PHP logic using JavaScript. To run real PHP, install PHP.

🎯 Real-World Analogy: A database transaction is like a bank wire transfer. Moving $500 from savings to checking requires two operations: subtract from savings AND add to checking. If either fails, BOTH must be undone — you can't lose money in transit! Prepared statements are like pre-printed forms — you fill in the blanks (parameters) but the structure is fixed, so nobody can inject extra instructions.

Transactions: All or Nothing

Transactions wrap multiple queries into a single atomic unit. If any query fails, rollBack() undoes everything. This is critical for financial operations, order processing, and any multi-step database update where partial completion would leave data in an inconsistent state.

Try It: Transactions

Transfer money between accounts with commit and rollback

Try it Yourself »

JavaScript

// PDO Transactions: All or Nothing
console.log("=== Database Transactions ===");
console.log();
console.log("A transaction groups multiple queries into ONE atomic operation.");
console.log("Either ALL queries succeed, or NONE of them apply.");
console.log();

// Simulate a database with transactions
class SimDB {
  constructor() {
    this.accounts = { Alice: 1000, Bob: 500, Charlie: 250 };
    this.log = [];
    this.inTransaction = false;
    this.snapshot = null;
  }
  
  beginTransaction() 
...

Prepared Statements & Stored Procedures

Beyond basic prepared statements, PDO supports typed bindings (PDO::PARAM_INT), batch operations (prepare once, execute many), and stored procedure calls. These features give you fine-grained control over database operations while maintaining security and performance.

Try It: Prepared Statements & Stored Procs

Use named/positional placeholders, batch inserts, and stored procedures

Try it Yourself »

JavaScript

// Prepared Statements & Stored Procedures
console.log("=== Prepared Statements Deep Dive ===");
console.log();
console.log("Prepared statements separate SQL structure from data.");
console.log("Benefits: Security (no injection), Performance (cached plans)");
console.log();

console.log("1️⃣ POSITIONAL PLACEHOLDERS (?)");
console.log("────────────────────────────────");
console.log("  $stmt = $pdo->prepare('SELECT * FROM users WHERE age > ? AND city = ?');");
console.log("  $stmt->execute([25, '
...

⚠️ Common Mistakes

⚠️

Not using transactions for multi-step operations — if you insert an order and order items without a transaction, a failure on the items leaves an orphan order in the database.

⚠️

Forgetting to catch PDOException — set PDO::ATTR_ERRMODE = PDO::ERRMODE_EXCEPTION in your PDO constructor so errors are thrown, not silently ignored.

⚠️

Using FETCH_BOTH (default) — it returns both numeric and associative keys, doubling memory usage. Always set PDO::ATTR_DEFAULT_FETCH_MODE = PDO::FETCH_ASSOC.

💡

Pro Tip: For batch inserts of 1000+ rows, wrap them in a single transaction. This can be 50-100x faster than individual inserts because the database commits once instead of 1000 times.

📋 Quick Reference — Advanced PDO

Method	Purpose
beginTransaction()	Start a transaction
commit()	Save all changes permanently
rollBack()	Undo all changes in transaction
bindValue(:key, val, type)	Bind a value with specific type
fetchAll(PDO::FETCH_CLASS)	Map rows to PHP objects
CALL procedure(:param)	Execute stored procedure

🎉 Lesson Complete!

You've mastered advanced PDO! Next, explore Query Builders and ORM concepts to map database tables to PHP objects.