CRUD with Prepared Statements

Almost every web app does four things with its data: Create new rows, Read them, Update them and Delete them. Together these are called CRUD. A student portal adds students, lists them, corrects their marks and removes old records; a shop does the same with products.

In this lesson you will do all four with PDO prepared statements, the safe way to put user data into SQL. You will also get the ID of a new row, count and search rows, and group several changes into a transaction. The examples use the students table and the db() function from Connecting to MySQL with PDO.

Why prepared statements?

Never build SQL by gluing user input into a string:

// DANGEROUS: never do this
$sql = "SELECT * FROM students WHERE name = '" . $_GET['name'] . "'";

If someone sends the name x' OR '1'='1, the query becomes ... WHERE name = 'x' OR '1'='1' and returns every row. Worse inputs can delete tables. This attack is called SQL injection, and it is still one of the most common ways websites are broken into.

A prepared statement fixes this by keeping the SQL and the data apart. You send the SQL first, with placeholders like :name where the values will go. Then you send the values separately. The database always treats them as plain data, never as SQL. Sending x' OR '1'='1 to a prepared statement simply looks for a student with that strange name and finds none.

The pattern is always the same three steps: prepare(), execute() with an array of values, then fetch the results if there are any.

Create: INSERT a row

<?php
declare(strict_types=1);

require __DIR__ . '/../src/db.php';

$stmt = db()->prepare('INSERT INTO students (name, city, marks) VALUES (:name, :city, :marks)');
$stmt->execute([
    'name' => 'Isha',
    'city' => 'Lucknow',
    'marks' => 85,
]);

$newId = (int) db()->lastInsertId();
echo "Added Isha with id $newId\n";

Added Isha with id 4

The keys of the array match the placeholder names (the leading colon is optional in the keys). lastInsertId() returns the AUTO_INCREMENT ID that MySQL gave the new row, as a string, so cast it to int. You typically use it to redirect to the new record's page.

Read: SELECT one row or many

To read one row, use fetch(). It returns the row as an array, or false if there is no match, so always check:

$stmt = db()->prepare('SELECT id, name, city, marks FROM students WHERE id = :id');
$stmt->execute(['id' => $newId]);
$student = $stmt->fetch();

if ($student === false) {
    echo "No student with that id\n";
} else {
    echo "{$student['name']} from {$student['city']} scored {$student['marks']}\n";
}

Isha from Lucknow scored 85

To read many rows, use fetchAll(), which returns an array of rows (an empty array if nothing matches):

$minMarks = 80;
$stmt = db()->prepare('SELECT name, marks FROM students WHERE marks >= :min ORDER BY marks DESC');
$stmt->execute(['min' => $minMarks]);
$toppers = $stmt->fetchAll();

foreach ($toppers as $t) {
    echo "{$t['name']}: {$t['marks']}\n";
}
echo count($toppers) . " students scored $minMarks or more\n";

Meera: 93
Aarav: 88
Isha: 85
3 students scored 80 or more

Two more patterns come up all the time:

• Single values: for SELECT COUNT(*) ..., call fetchColumn() to get just that value.
• Searching with LIKE: the % wildcards belong in the value, not the SQL: $stmt->execute(['pattern' => '%' . $search . '%']) with WHERE name LIKE :pattern. See LIKE for the wildcards.

Update: change existing rows

$stmt = db()->prepare('UPDATE students SET marks = :marks WHERE id = :id');
$stmt->execute(['marks' => 90, 'id' => $newId]);

echo $stmt->rowCount() . " row(s) updated\n";

1 row(s) updated

rowCount() tells you how many rows changed. Running the same update again prints 0 row(s) updated, because MySQL only counts rows whose values actually changed. So 0 can mean "no such ID" or "the values were already the same". If you need to know whether the record exists, check that with a SELECT first.

Never forget the WHERE clause. UPDATE students SET marks = 90 without it changes every student in the table.

Delete: remove rows

$stmt = db()->prepare('DELETE FROM students WHERE id = :id');
$stmt->execute(['id' => $newId]);

echo $stmt->rowCount() . " row(s) deleted\n";

1 row(s) deleted

On a website, delete only in response to a POST request (a form button), never a plain link, and protect the form with a CSRF token. You will build exactly that in Project: Contact Book App.

What placeholders cannot do

Placeholders stand for values only. You cannot use one for a table name, a column name or a keyword like ASC. To let users choose the sort order, map their choice through an allow-list that you control:

$sortOptions = [
    'name' => 'name',
    'marks' => 'marks DESC',
    'city' => 'city',
];
$orderBy = $sortOptions[$_GET['sort'] ?? ''] ?? 'name';

$students = db()->query("SELECT name, city, marks FROM students ORDER BY $orderBy")->fetchAll();

Only strings from your own array can reach the SQL. Anything else falls back to name.

For LIMIT and OFFSET, bind the numbers as integers with bindValue() and PDO::PARAM_INT to be safe across all settings, as in $stmt->bindValue('limit', 10, PDO::PARAM_INT); followed by $stmt->execute();.

Transactions: all or nothing

Some actions need several queries that must succeed or fail together. Moving ₹500 from Priya's wallet to Kabir's is two UPDATEs; if the second one fails, the first must be undone. A transaction groups them: beginTransaction() starts it, commit() saves everything, and rollBack() cancels everything.

function transfer(int $fromId, int $toId, int $amount): void
{
    $pdo = db();
    $pdo->beginTransaction();

    try {
        $take = $pdo->prepare('UPDATE wallets SET balance = balance - :amount WHERE id = :id AND balance >= :check');
        $take->execute(['amount' => $amount, 'id' => $fromId, 'check' => $amount]);
        if ($take->rowCount() !== 1) {
            throw new RuntimeException('Not enough money in the wallet.');
        }

        $give = $pdo->prepare('UPDATE wallets SET balance = balance + :amount WHERE id = :id');
        $give->execute(['amount' => $amount, 'id' => $toId]);

        $pdo->commit();
    } catch (Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
}

With Priya at ₹2,000 and Kabir at ₹300, transfer(1, 2, 500) leaves them at ₹1,500 and ₹800. A second call transfer(2, 1, 5000) throws Not enough money in the wallet. and the rollback leaves both balances untouched. Transactions need InnoDB tables, which is the default. The SQL side is explained in Transactions.

Common mistakes

• Putting variables inside the SQL string. Even "safe-looking" values such as IDs from a URL must go through placeholders.
• Using the same placeholder name twice in one query. With real prepared statements (EMULATE_PREPARES off) this fails with SQLSTATE[HY093]: Invalid parameter number. Use two names, like :amount and :check above.
• Passing extra keys to execute(). Every key must match a placeholder, or PDO throws parameter was not defined.
• Not checking fetch() for false. Reading $student['name'] on false gives a warning and an empty value.
• Forgetting WHERE in UPDATE or DELETE. It changes or removes every row.

Key points

• Prepared statements send SQL and data separately, which stops SQL injection.
• The pattern is prepare(), then execute([...]) with named placeholders, then fetch.
• fetch() returns one row or false; fetchAll() returns all rows; fetchColumn() returns one value.
• lastInsertId() gives the new row's ID; rowCount() tells you how many rows changed.
• Placeholders are for values only; choose column names and sort orders from an allow-list.
• Wrap related changes in a transaction with beginTransaction(), commit() and rollBack().

<?php
declare(strict_types=1);

require __DIR__ . '/../src/db.php';

function studentsFromCity(string $city, int $minMarks): array
{
    $stmt = db()->prepare(
        'SELECT name, marks FROM students WHERE city = :city AND marks >= :min ORDER BY marks DESC'
    );
    $stmt->execute(['city' => $city, 'min' => $minMarks]);
    return $stmt->fetchAll();
}

function updateCity(int $id, string $city): bool
{
    $stmt = db()->prepare('UPDATE students SET city = :city WHERE id = :id');
    $stmt->execute(['city' => $city, 'id' => $id]);
    return $stmt->rowCount() === 1;
}

print_r(studentsFromCity('Kochi', 90));
var_dump(updateCity(3, 'Jaipur'));

Array
(
    [0] => Array
        (
            [name] => Meera
            [marks] => 93
        )

)
bool(true)