Database Integration (MySQL)

Reviewed & published by Brayan K

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.

Part of the free PHP course at LearnCodingFast — hands-on lessons with worked examples and the output they print, plus practice exercises and a quick quiz.

What You'll Learn in This Lesson

• Connect to MySQL with a PDO DSN and the right options
• Choose between query() and prepare()/execute()
• Use named (:name) and positional (?) placeholders safely
• Insert, update and delete rows, and read lastInsertId()
• Fetch results with fetch(), fetchAll() and FETCH_ASSOC
• Wrap multiple writes in a transaction and roll back on failure

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.

<?php
// PDO is PHP's recommended database layer. It speaks 12+ databases with one
// API and supports prepared statements (your shield against SQL injection).

// The DSN ("Data Source Name") is a single string describing WHAT to connect to:
//   driver : host : database name : charset
$dsn = 'mysql:host=localhost;dbname=my_app;charset=utf8mb4';

// new PDO(dsn, username, password, options) opens the connection.
$pdo = new PDO($dsn, 'app_user', 'secret', [
    // Throw an exception the moment anything goes wrong (see below — do this always).
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    // Return each row as an associative array: ['name' => 'Alice'] not [0 => 'Alice'].
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    // Use the database's REAL prepared statements, not PHP-side emulation.
    PDO::ATTR_EMULATE_PREPARES   => false,
]);

echo "Connected to MySQL.\n";   // we only reach this line if the connection worked
?>

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.

<?php
// Two ways to run SQL. The rule is simple:
//   - NO user input?  -> query() is fine (one trip, less typing).
//   - ANY user input? -> prepare() + execute() with placeholders.

// query() runs a fixed string and returns a result set straight away.
// fetchAll() pulls EVERY matching row into one array.
$rows = $pdo->query('SELECT id, name, role FROM users ORDER BY id')->fetchAll();
foreach ($rows as $u) {
    // FETCH_ASSOC means each row is an array keyed by column name.
    echo "  #{$u['id']} {$u['name']} [{$u['role']}]\n";
}

// prepare() + execute() is for queries that contain VALUES from outside.
// The ? is a positional placeholder — a slot the value drops into safely.
$id = 2;                                              // pretend this came from a URL
$stmt = $pdo->prepare('SELECT name FROM users WHERE id = ?');
$stmt->execute([$id]);                               // value supplied here, separately
$one = $stmt->fetch();                               // fetch() returns ONE row (or false)
echo "User {$id} is {$one['name']}\n";
?>

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.

<?php
// Placeholders come in two flavours. Pick one style per query (don't mix).

// 1) POSITIONAL placeholders ( ? ) — bind by ORDER. Pass a plain array.
$stmt = $pdo->prepare('INSERT INTO users (name, email, role) VALUES (?, ?, ?)');
$stmt->execute(['Diana Prince', '[email protected]', 'user']);

// lastInsertId() gives you the auto-increment id the database just generated.
$newId = (int) $pdo->lastInsertId();
echo "Inserted user #{$newId}\n";

// 2) NAMED placeholders ( :name ) — bind by NAME. Pass a keyed array.
// Clearer for long queries because the value can't end up in the wrong slot.
$stmt = $pdo->prepare(
    'SELECT id, name FROM users WHERE role = :role AND name LIKE :search'
);
$stmt->execute([
    'role'   => 'user',
    'search' => 'D%',                 // names starting with D
]);
foreach ($stmt->fetchAll() as $row) {
    echo "  match: #{$row['id']} {$row['name']}\n";
}
?>

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.

<?php
// UPDATE and DELETE work exactly like SELECT/INSERT: prepare, then execute
// with the values bound as parameters. rowCount() tells you how many rows
// were actually changed — useful for "did that record exist?" checks.

// UPDATE — change Bob's role.
$stmt = $pdo->prepare('UPDATE users SET role = :role WHERE id = :id');
$stmt->execute(['role' => 'moderator', 'id' => 2]);
echo "Updated {$stmt->rowCount()} row(s)\n";   // 1

// DELETE — remove the user we inserted earlier.
$stmt = $pdo->prepare('DELETE FROM users WHERE id = ?');
$stmt->execute([4]);
echo "Deleted {$stmt->rowCount()} row(s)\n";    // 1

// rowCount() is 0 when nothing matched — no error, just no rows touched.
$stmt = $pdo->prepare('DELETE FROM users WHERE id = ?');
$stmt->execute([999]);
echo "Deleted {$stmt->rowCount()} row(s)\n";    // 0
?>

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.

<?php
// === Why string interpolation is dangerous (SQL injection) ===

// BAD — never build SQL by gluing user input into the string:
//   $sql = "SELECT * FROM users WHERE name = '$name'";   // DO NOT DO THIS
//
// If an attacker sends $name as:   ' OR 1=1 --
// the query becomes:
//   SELECT * FROM users WHERE name = '' OR 1=1 -- '
// ...which matches EVERY row. The input changed the query's logic.

// GOOD — bind the input as a parameter. PDO sends the SQL and the data on
// SEPARATE channels, so user input is always treated as a value, never as code.
$name = "' OR 1=1 --";                 // a hostile value...
$stmt = $pdo->prepare('SELECT id, name FROM users WHERE name = :name');
$stmt->execute(['name' => $name]);     // ...is searched for literally, and matches nothing
echo "Rows found: " . count($stmt->fetchAll()) . "\n";   // 0 — attack defused

// Bonus: a legitimate apostrophe is handled for free, no escaping needed.
$stmt->execute(['name' => "O'Brien"]);
echo "Looked up O'Brien safely.\n";
?>

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.

<?php
// 🎯 YOUR TURN — read ONE user safely by id, then print their name.
// The connection $pdo already exists. Fill in each ___ using the 👉 hints.

$id = 3;   // imagine this arrived from a form or URL

// 1) prepare a SELECT that finds the row WHERE id matches a placeholder
$stmt = $pdo->prepare('SELECT name FROM users WHERE id = ___');   // 👉 use a  ?  placeholder

// 2) run it, passing $id as the value (positional → plain array)
$stmt->___([$id]);                 // 👉 the method that runs a prepared statement

// 3) grab the single matching row
$row = $stmt->___();               // 👉 the method that returns ONE row

echo "Name: {$row['name']}\n";

// ✅ Expected output (when id 3 is "Carol"):
//    Name: Carol
?>

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

<?php
// 🎯 YOUR TURN — insert a user with NAMED placeholders, then echo the new id.
// Fill in each ___ using the 👉 hints.

$stmt = $pdo->prepare(
    'INSERT INTO users (name, email) VALUES (___, ___)'   // 👉 use named placeholders :name and :email
);

// Run it, binding each name to its value (named → keyed array)
$stmt->execute([
    '___' => 'Eve Adams',          // 👉 the key must match the :name placeholder (no colon here)
    '___' => '[email protected]',    // 👉 the key must match the :email placeholder
]);

// Ask the database for the id it just generated
$id = (int) $pdo->___();           // 👉 the method that returns the last inserted id
echo "Created user #{$id}\n";

// ✅ Expected output (example):
//    Created user #5
?>

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.)

<?php
// === Transactions: all-or-nothing ===
// A transaction groups several writes so they either ALL succeed or ALL undo.
// The classic case is moving money: you must never debit one account without
// crediting the other.

try {
    $pdo->beginTransaction();          // open the transaction

    // Take 100 from account 1...
    $debit = $pdo->prepare('UPDATE accounts SET balance = balance - :amt WHERE id = :id');
    $debit->execute(['amt' => 100, 'id' => 1]);

    // ...and give 100 to account 2.
    $credit = $pdo->prepare('UPDATE accounts SET balance = balance + :amt WHERE id = :id');
    $credit->execute(['amt' => 100, 'id' => 2]);

    $pdo->commit();                    // both updates land together, atomically
    echo "Transfer committed.\n";
} catch (Throwable $e) {
    $pdo->rollBack();                  // ANY failure? undo everything as if nothing ran
    echo "Transfer rolled back: " . $e->getMessage() . "\n";
}
?>

Common Errors (and the fix)

• You built SQL by concatenating input ("... WHERE name = '$name'") — this is the #1 cause of data breaches. Never put a variable straight into the SQL string. Replace it with a ? or :name placeholder and pass the value to execute().
• A query quietly does nothing and you get no error — you forgot ERRMODE_EXCEPTION, so PDO failed silently. Set PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION when you connect so problems throw and you can see them.
• "Trying to access array offset on value of type bool" — fetch() returned false because no row matched, and you indexed into it anyway. Check the result first: if ($row) { ... } before reading $row['name'].
• "$row->name" gives an error, or "$row['name']" is empty — you used the wrong fetch mode. With FETCH_ASSOC rows are arrays ($row['name']); for object access ($row->name) you'd need FETCH_OBJ. Pick one and stay consistent.
• "SQLSTATE[HY093]: Invalid parameter number" — your placeholders and bound values don't line up: a named key doesn't match its :placeholder, or the number of ? marks differs from the array length. Count them and make the keys match exactly (no colon in the array keys).

Pro Tips

• 💡 Default to PDO for new projects. It works with 12+ databases through one API, so moving from MySQL to PostgreSQL barely touches your code — and its named placeholders are clearer than MySQLi's.
• 💡 Never echo a raw database error to users. Catch the exception, log the real message server-side with error_log(), and show a generic "Something went wrong" — error details can leak your schema.
• 💡 Keep credentials out of your code. Read the DSN, username and password from environment variables or a config file that's excluded from version control, not hard-coded in the script.

📋 Quick Reference — PDO

Mini-Challenge: Rename a User

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.

<?php
// 🎯 MINI-CHALLENGE: a tiny "rename a user" script.
// No code is filled in — work from the steps below, then run it.
// Assume $pdo is already connected.
//
// 1. Set $id = 2 and $newName = "Robert".
// 2. prepare() an UPDATE that sets name = a placeholder WHERE id = a placeholder.
//    (named placeholders :name and :id are clearest)
// 3. execute() it, binding $newName and $id.
// 4. echo how many rows changed using $stmt->rowCount().
// 5. SELECT the same row back and echo the stored name to confirm the change.
//
// Remember: every value that comes from a variable goes through a placeholder,
// never straight into the SQL string.
//
// ✅ Expected output (example):
//    Updated 1 row(s)
//    Name is now: Robert

// your code here
?>

🎉 Lesson Complete!

• ✅ PDO connects via a DSN string; set ERRMODE_EXCEPTION, FETCH_ASSOC and EMULATE_PREPARES => false
• ✅ Use query() for fixed SQL, prepare()/execute() the instant any value comes from outside
• ✅ Placeholders — positional ? or named :name — keep data and SQL separate and stop injection
• ✅ fetch() returns one row, fetchAll() returns all; lastInsertId() and rowCount() report on writes
• ✅ A transaction (beginTransaction → commit/rollBack) makes multiple writes all-or-nothing

Practice quiz

Continue this course

• Previous: Checkpoint: Data & Types
• Next: Security Best Practices — Prevent SQL injection, XSS, and CSRF in PHP web applications
• Quick reference: PHP cheat sheet › Database (PDO)
• From the blog: Building Your First REST API with PHP

Frequently asked questions

What is the difference between query() and prepare()/execute()?

query() runs a fixed SQL string in one step and is fine only when the query contains no outside input. prepare()/execute() is a two-step process: prepare() sends the SQL with placeholders, then execute() supplies the values separately. Use prepare()/execute() any time a value comes from a user, a form, a URL, or any source you don't fully control — it is what makes the query safe from SQL injection.

What is a prepared statement and how does it stop SQL injection?

A prepared statement sends the query's structure and its data to the database on two separate channels. The SQL (with ? or :name placeholders) is parsed first, then the values are attached afterwards as pure data. Because the values arrive after the query is already understood, they can never change its meaning — so an input like ' OR 1=1 -- is searched for literally instead of being executed as SQL.

When should I use named (:name) versus positional (?) placeholders?

Both are equally safe; it is a readability choice. Positional ? placeholders bind by order and pair with a plain array — quick for short queries. Named :name placeholders bind by name and pair with a keyed array — clearer for long queries because a value can't accidentally land in the wrong slot. Pick one style per query and don't mix the two in a single statement.

Why should I set PDO::ERRMODE_EXCEPTION?

By default older PDO setups can fail silently, leaving you debugging a query that quietly did nothing. Setting PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION makes PDO throw a PDOException the moment anything goes wrong, so you can catch it, log the real message server-side, and show the user a generic error. Modern PHP (8.0+) uses exception mode by default, but setting it explicitly keeps your code clear and portable.

When do I need a transaction?

Use a transaction whenever two or more writes must succeed together or not at all. The textbook case is transferring money: debiting one account and crediting another must both happen, never just one. Call beginTransaction(), run the queries, then commit() to save them atomically — or rollBack() inside a catch block to undo everything if any step fails.

Related lessons

• Object-Oriented PHP