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.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

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.

📋 Quick Reference — Advanced PDO

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.

Practice quiz

Which fetch mode hydrates each row into an object of your own class, complete with its methods?

  • PDO::FETCH_ASSOC
  • PDO::FETCH_OBJ
  • PDO::FETCH_CLASS
  • PDO::FETCH_BOTH

Answer: PDO::FETCH_CLASS. FETCH_CLASS pours each row into an instance of your class, so the data arrives with real methods attached — the seed of every ORM.

What does PDO::FETCH_ASSOC return for each row?

  • A plain object you read with arrow syntax
  • An associative array keyed by column name
  • An array with both numeric and string keys
  • An instance of your own class

Answer: An associative array keyed by column name. FETCH_ASSOC gives an associative array you read by column name, e.g. $row['name'] — the everyday choice.

Why prepare a statement once and call execute() inside the loop?

  • It makes the SQL case-insensitive
  • The database parses/plans the SQL once and reuses it, and values stay injection-safe
  • It automatically opens a transaction
  • It converts the rows to JSON

Answer: The database parses/plans the SQL once and reuses it, and values stay injection-safe. prepare() asks the database to parse and plan the SQL; do it once outside the loop and only execute() inside to reuse the plan.

What is the key difference between bindValue() and bindParam()?

  • bindValue is for integers, bindParam is for strings
  • bindValue takes a snapshot now; bindParam binds a variable by reference, read at execute() time
  • bindParam is unsafe against SQL injection
  • They are identical aliases

Answer: bindValue takes a snapshot now; bindParam binds a variable by reference, read at execute() time. bindValue snapshots the value immediately; bindParam binds the variable by reference, so PDO reads its current value when execute() runs.

In a transaction, which method permanently saves every queued change?

  • commit()
  • rollBack()
  • beginTransaction()
  • lastInsertId()

Answer: commit(). commit() makes all the changes in the transaction permanent; rollBack() undoes them all on failure.

Inside a try/catch transaction, where does rollBack() belong?

  • Before beginTransaction()
  • In the catch block, to undo everything on any failure
  • Right after commit()
  • Inside the SQL string

Answer: In the catch block, to undo everything on any failure. You commit() on the happy path and call rollBack() in catch so any failure cleanly undoes the whole transaction.

Which method returns the auto-increment id of the most recent INSERT?

  • rowCount()
  • lastInsertId()
  • fetchColumn()
  • errorInfo()

Answer: lastInsertId(). lastInsertId() returns the auto-increment id of the most recent INSERT; rowCount() reports how many rows a write affected.

How do you safely build a WHERE name IN (...) clause for a variable-length array in PDO?

  • Bind the whole array to a single placeholder
  • Concatenate the values into the SQL string
  • Generate one ? per value with rtrim(str_repeat('?,', count($v)), ',') and pass the array to execute()
  • Use FETCH_COLUMN to expand the array

Answer: Generate one ? per value with rtrim(str_repeat('?,', count($v)), ',') and pass the array to execute(). You cannot bind an array to one placeholder; build one ? per value so the placeholder count always matches the value count.

Why does setting PDO::ATTR_EMULATE_PREPARES to false improve safety?

  • It disables transactions
  • It makes the database server parse real prepared statements instead of quoting on the client
  • It speeds up FETCH_ASSOC
  • It caches the connection

Answer: It makes the database server parse real prepared statements instead of quoting on the client. Emulated prepares quote on the client; turning them off sends real prepared statements parsed by the server — better security and accurate types.

Which fetch mode is best avoided because it returns every column twice and wastes memory?

  • PDO::FETCH_BOTH
  • PDO::FETCH_ASSOC
  • PDO::FETCH_OBJ
  • PDO::FETCH_COLUMN

Answer: PDO::FETCH_BOTH. FETCH_BOTH (the default) returns each column under both a numeric and a string key, doubling memory; set FETCH_ASSOC instead.