Pdo Advanced

By the end of this lesson you'll pick the right fetch mode, reuse prepared statements in loops, and wrap multi-step writes in transactions that either fully commit or cleanly roll back — the skills behind every safe, fast database app.

Part of the free Php course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

What You'll Learn in This Lesson

1️⃣ Fetch Modes: ASSOC, OBJ & CLASS

A fetch mode decides the shape of each row PDO hands back. PDO::FETCH_ASSOC gives you an associative array you read by column name ( $row['name'] ) — the everyday choice. PDO::FETCH_OBJ gives a plain object you read with arrow syntax ( $row- > name ). The powerful one is PDO::FETCH_CLASS : it pours each row straight into an object of your class, so the data arrives with real methods attached. That last mode is the seed of every ORM.

Set a default once with $pdo- > setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC) and you won't have to pass the mode on every call. You can still override it per query when one row needs a different shape.

2️⃣ Reusing a Prepared Statement in a Loop

Calling prepare() asks the database to parse and plan the SQL. Do it once outside the loop, then call execute() with fresh values inside the loop — the plan is reused and each value is still safely escaped. Two handy facts after a write: lastInsertId() returns the auto-increment id of the most recent INSERT , and rowCount() tells you how many rows a write touched.

3️⃣ bindValue vs bindParam (and Types)

Both attach a value to a placeholder, but they differ in when the value is read. bindValue() takes a snapshot of the value right now. bindParam() binds a variable by reference , so PDO reads its value at execute() time — change the variable in a loop and the query changes with it. The optional third argument forces a type: PDO::PARAM_INT for integers, PDO::PARAM_STR for strings, PDO::PARAM_BOOL for booleans.

4️⃣ Transactions: All or Nothing

A transaction bundles several writes into one atomic unit: every query commits together, or none of them do. You open it with beginTransaction() , do the work inside a try block, commit() on success, and rollBack() in catch so any failure cleanly undoes everything. This is essential for money transfers, order processing, or any multi-step update where a half-finished result would corrupt your data.

5️⃣ A Safe IN (...) Clause

You can't bind a whole array to one placeholder, and you must never glue user values into the SQL string yourself — that's a classic injection hole. Instead build one ? per value, splice them into the IN (...) , and pass the matching array to execute() . The placeholder count always lines up with the value count, and PDO escapes every value for you.

6️⃣ Your Turn

Now you drive. The script below is almost complete — fill in each ___ using the 👉 hint, then run it and check it against the Output panel. First, fetch a row as an object.

One more, and it's the big one: finish a transaction so the transfer is atomic. Add the line that opens it and the line that makes it permanent.

Common Errors (and the fix)

• A multi-step write leaves orphaned data — you inserted an order and its items without a transaction, and the items failed. Wrap related writes in beginTransaction() … commit() with a rollBack() in catch , so a failure on step two undoes step one too.
• Injection still possible despite "prepared" statements — you left PDO::ATTR_EMULATE_PREPARES on and built the SQL by concatenating values. Emulated prepares quote on the client, not the server. Send real prepared statements with $pdo- > setAttribute(PDO::ATTR_EMULATE_PREPARES, false) and always pass values as parameters, never by string-joining.
• "SQLSTATE... number of bound variables does not match" on an IN (...) query — you hard-coded a fixed set of ? but passed a different-length array, or you tried to bind the array to one placeholder. Generate the placeholders dynamically from the array (see section 5) so the counts always match.
• "Trying to access array offset on value of type object" (or the reverse) — fetch-mode confusion. With FETCH_OBJ you must use $row- > name ; with FETCH_ASSOC you must use $row['name'] . Match the access syntax to the fetch mode you chose.
• "There is no active transaction" on rollBack() — your commit() already closed it, or beginTransaction() never ran. Only call rollBack() while a transaction is open; guard with if ($pdo- > inTransaction()) if a path is uncertain.

Pro Tips

• 💡 Wrap big batches in one transaction. Inserting 1,000+ rows inside a single transaction can be 50–100× faster than committing each row separately.
• 💡 Turn off emulated prepares with PDO::ATTR_EMULATE_PREPARES => false so the server parses your SQL — better security and accurate parameter types.
• 💡 Set a default fetch mode of FETCH_ASSOC in the constructor; it's clearer than the default FETCH_BOTH , which returns every column twice.

📋 Quick Reference — Advanced PDO

Method / Constant

Example

What It Does

beginTransaction()

$pdo- > beginTransaction()

Open a transaction

commit()

$pdo- > commit()

Save all changes permanently

rollBack()

$pdo- > rollBack()

Undo every change in the transaction

bindValue(p, v, type)

bindValue(":age", 28, PDO::PARAM_INT)

Bind a value snapshot with a type

bindParam(p, $var, type)

bindParam(":age", $age, PDO::PARAM_INT)

Bind a variable by reference

lastInsertId()

$pdo- > lastInsertId()

Id of the most recent INSERT

rowCount()

$stmt- > rowCount()

Rows affected by a write

FETCH_ASSOC / OBJ / CLASS

fetch(PDO::FETCH_OBJ)

Choose the row's shape

IN ( ? , ? , ? )

str_repeat("?,", count($v))

One placeholder per value

Frequently Asked Questions

Mini-Challenge: Bulk Insert + Filtered Read

No code is filled in this time — just a brief and an outline. Write it yourself, run it on onecompiler.com/php or your own machine, then check your result against the expected output in the comments. This is exactly the prepare-loop-commit pattern you'll reach for on real data.

🎉 Lesson Complete!

• ✅ Fetch modes set a row's shape: FETCH_ASSOC (array), FETCH_OBJ (object), FETCH_CLASS (your class, with methods)
• ✅ Prepare once, execute many in a loop — reuse the plan, stay injection-safe
• ✅ Transactions with beginTransaction / commit / rollBack in try/catch make multi-step writes all-or-nothing
• ✅ bindValue snapshots a value; bindParam binds a variable by reference; both can force a PDO::PARAM type
• ✅ lastInsertId() and rowCount() report on the last write
• ✅ Build a safe IN (...) with one dynamic placeholder per value
• ✅ Next lesson: Query Builders & ORM Concepts — map database tables to PHP objects

Practice quiz

Continue this course

• Previous: Middleware Architecture
• Next: Query Builders & ORM Concepts

Related lessons

• Databases & PDO