Database

By the end of this lesson you'll connect PHP to MySQL with PDO, run safe queries with prepared statements, read and write rows, group writes into transactions, and shut the door on SQL injection — the skills behind every real database-driven 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️⃣ Connecting with PDO

PDO (PHP Data Objects) is the modern, recommended way to talk to a database. You connect by creating a new PDO(...) object, passing a DSN — a "Data Source Name", one string that names the driver, host, database and charset. Always pass three options up front: ERRMODE_EXCEPTION so problems throw instead of failing quietly, a default fetch mode of FETCH_ASSOC so rows come back keyed by column name, and EMULATE_PREPARES => false so you get the database's real prepared statements.

2️⃣ query() vs prepare()/execute()

There are two ways to send SQL, and choosing correctly is the whole game. query() runs a fixed string in one step — fine when the SQL contains no outside input. The moment a query includes a value you didn't write yourself (from a form, a URL, anywhere), you must switch to prepare() followed by execute() : prepare() sends the query shape with placeholders , and execute() supplies the values separately. fetchAll() returns every row; fetch() returns just one.

3️⃣ Placeholders & Inserting Rows

Placeholders come in two interchangeable styles. Positional ? placeholders bind by order and pair with a plain array. Named :name placeholders bind by name and pair with a keyed array — clearer for longer queries because a value can't slide into the wrong slot. After an INSERT , lastInsertId() hands you the auto-increment id the database just generated. Pick one placeholder style per query; never mix them in a single statement.

4️⃣ Updating & Deleting

UPDATE and DELETE follow the exact same pattern: prepare() with placeholders, then execute() with the values. Afterwards, rowCount() tells you how many rows actually changed — a handy way to answer "did that record even exist?". A count of 0 is not an error; it just means nothing matched your WHERE clause.

5️⃣ SQL Injection (and how placeholders stop it)

SQL injection is when an attacker types something that changes your query's meaning rather than just supplying a value. It happens whenever you glue user input straight into a SQL string. Prepared statements stop it cold: PDO sends the SQL structure and the data on separate channels , so the value arrives after the query is already understood and can never be executed as code. As a bonus, awkward characters like apostrophes are handled for free.

Now you try. The script below reads one user safely by id — fill in each ___ using the 👉 hint, then check it against the Output panel.

One more — this time write an INSERT using named placeholders and read back the new id.

6️⃣ Transactions: All or Nothing

Sometimes several writes must succeed together — like moving money between two accounts, where debiting one without crediting the other would lose cash. A transaction groups them: call beginTransaction() , run your queries, then commit() to save them all at once. Wrap it in try/catch and call rollBack() if anything throws, so a half-finished operation is undone as though it never happened. (Because you set ERRMODE_EXCEPTION earlier, a failed query does throw — which is what triggers the rollback.)

📋 Quick Reference — PDO

No code is filled in this time — just a brief and an outline. Write it yourself against your own MySQL database, then check your result against the expected output in the comments. Every value here must travel through a placeholder, never into the SQL string directly.

Practice quiz

What does the PDO DSN string describe?

  • The username and password only
  • The SQL query to run
  • What to connect to: driver, host, database name, charset
  • The fetch mode for results

Answer: What to connect to: driver, host, database name, charset. The DSN ('Data Source Name') is one string like 'mysql:host=localhost;dbname=my_app;charset=utf8mb4' naming the driver, host, database and charset.

Which PDO attribute makes problems throw an exception instead of failing silently?

  • PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
  • PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
  • PDO::ATTR_EMULATE_PREPARES => true
  • PDO::ATTR_PERSISTENT => true

Answer: PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION. Setting ERRMODE to ERRMODE_EXCEPTION makes PDO throw a PDOException the moment anything goes wrong so you can catch and log it.

When must you use prepare()/execute() instead of query()?

  • Only for SELECT statements
  • Never — query() is always safe
  • Only when connecting to PostgreSQL
  • Whenever the query contains a value from outside (form, URL, user)

Answer: Whenever the query contains a value from outside (form, URL, user). query() runs a fixed string; the instant a value comes from outside you must use prepare() with placeholders to stay safe from injection.

How does a prepared statement stop SQL injection?

  • It escapes input by adding backslashes to the SQL string
  • It sends the query structure and the data on separate channels, so input can't change the query's meaning
  • It blocks the words DROP and DELETE
  • It encrypts the database

Answer: It sends the query structure and the data on separate channels, so input can't change the query's meaning. The SQL with placeholders is parsed first, then values attach as pure data — so ' OR 1=1 -- is searched for literally, never executed.

What does fetch() return when no row matches?

  • false

Answer: false. fetch() returns one row or false; indexing into false ('array offset on bool') is a common bug — check the result first.

What does FETCH_ASSOC make each row look like?

  • An object with properties, e.g. $row->name

FETCH_ASSOC returns rows keyed by column name; for $row->name object access you'd use FETCH_OBJ instead.

What does lastInsertId() return?

  • The number of rows changed
  • The auto-increment id the database just generated for an INSERT
  • The id you passed in
  • The primary key of the last SELECT

Answer: The auto-increment id the database just generated for an INSERT. After an INSERT, lastInsertId() hands back the auto-increment id the database created.

What does rowCount() of 0 after an UPDATE or DELETE mean?

  • The query failed
  • The table was dropped
  • The connection was lost
  • No rows matched the WHERE clause — not an error

Answer: No rows matched the WHERE clause — not an error. rowCount() reports how many rows changed; 0 simply means nothing matched, which is not an error.

When do you need a database transaction?

  • For every single SELECT
  • When two or more writes must all succeed together or all undo
  • Only when deleting rows
  • Whenever you connect to the database

Answer: When two or more writes must all succeed together or all undo. Transactions make multiple writes all-or-nothing — the classic case is a money transfer where you must never debit without crediting.

Which method undoes every write in a transaction if a step fails?

  • commit()
  • beginTransaction()
  • rollBack()
  • execute()

Answer: rollBack(). Inside a catch block, rollBack() undoes everything as if nothing ran; commit() instead makes all the writes permanent together.