Lesson 12 of 12

PHP MySQL with PDO

Files are fine for small amounts of data, as you saw in PHP Include and Files, but real websites keep products, users and orders in a database. In this final lesson of the PHP track you will use PHP PDO to connect to a MySQL or MariaDB database and run SQL queries safely. You should already know basic SQL; if not, work through SQL SELECT, SQL WHERE and SQL INSERT, UPDATE and DELETE first.

What is PHP PDO?

PDO (PHP Data Objects) is PHP’s built-in, database-neutral way to talk to databases. The same methods work with MySQL, MariaDB, PostgreSQL, SQLite and others; only the connection string changes. PDO supports prepared statements, which are the main defence against SQL injection, and it reports problems as exceptions you can catch.

You may meet very old tutorials using functions that start with mysql_, such as mysql_query(). Those were removed in PHP 7 and do not exist any more. The other modern option is the mysqli extension, which works only with MySQL and MariaDB. PHP PDO is a good default for new code.

The example table

The examples use a small books table in a database called bookshop. You can create it with phpMyAdmin or the MySQL command line, using what you learnt in SQL CREATE TABLE and SQL Constraints and Keys.

ColumnTypeNotes
idINT UNSIGNED AUTO_INCREMENTPrimary key
titleVARCHAR(150) NOT NULLBook title
authorVARCHAR(100) NOT NULLAuthor name
priceINT UNSIGNED NOT NULLWhole rupees
stockINT UNSIGNED NOT NULL DEFAULT 0Copies available

Sample rows: (1) Ponniyin Selvan, Kalki, 650, 8; (2) Malgudi Days, R. K. Narayan, 350, 15; (3) Wings of Fire, A. P. J. Abdul Kalam, 299, 0; (4) The Guide, R. K. Narayan, 280, 4.

Connecting to the database

Put the connection in one file and require_once it wherever you need the database. The first argument to new PDO() is the DSN (data source name): the driver, host, database name and character set. Always use charset=utf8mb4 so that every character, including Indian scripts and emoji, is stored correctly.

<?php
declare(strict_types=1);

$dsn  = 'mysql:host=localhost;dbname=bookshop;charset=utf8mb4';
$user = 'bookshop_app';
$pass = 'change-this-password';

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (PDOException $e) {
    error_log('Database connection failed: ' . $e->getMessage());
    http_response_code(500);
    exit('Sorry, the site is having trouble. Please try again later.');
}
Output
Example output: nothing when the connection succeeds; the file only creates $pdo. If the connection fails, the visitor sees: Sorry, the site is having trouble. Please try again later. (The real error message goes to the server's error log.)

The three options are worth understanding:

  • ERRMODE_EXCEPTION makes every database error throw a PDOException. This has been the default since PHP 8.0, but stating it makes your intent clear.
  • FETCH_ASSOC returns each row as an associative array keyed by column name, the “list of records” shape from PHP Arrays.
  • EMULATE_PREPARES => false asks MySQL to prepare statements itself, and returns integer columns as PHP integers.

Watch out: Never show the raw PDOException message to visitors. It can reveal your database host, user name or table structure. Log it with error_log() and show a friendly message. Keep the password out of version control, for example in a config file outside the web root or in an environment variable.

Reading data with a prepared statement

A prepared statement separates the SQL command from the data. You write the query with placeholders – named ones like :maxPrice or question marks ? – then pass the values to execute(). The database treats those values strictly as data, never as SQL, so input such as ' OR 1=1 -- cannot change what the query does. This is how you prevent SQL injection.

<?php
declare(strict_types=1);
require_once __DIR__ . '/db.php';

$maxPrice = 400;   // for example from filter_var($_GET['max'] ?? ...)

$stmt = $pdo->prepare(
    'SELECT title, author, price FROM books WHERE price <= :maxPrice ORDER BY price'
);
$stmt->execute(['maxPrice' => $maxPrice]);
$books = $stmt->fetchAll();

echo "<h2>Books up to Rs. $maxPrice</h2>\n<ul>\n";
foreach ($books as $book) {
    echo '  <li>' . htmlspecialchars($book['title'])
       . ' by ' . htmlspecialchars($book['author'])
       . ' – Rs. ' . $book['price'] . "</li>\n";
}
echo "</ul>\n";
echo count($books) . ' books found.';
Output
Example output (with the sample rows above):
<h2>Books up to Rs. 400</h2>
<ul>
  <li>The Guide by R. K. Narayan – Rs. 280</li>
  <li>Wings of Fire by A. P. J. Abdul Kalam – Rs. 299</li>
  <li>Malgudi Days by R. K. Narayan – Rs. 350</li>
</ul>
3 books found.

In PHP PDO, fetchAll() returns every row as an array. For a single row, use fetch(), which returns false when nothing matches. Even though the data came from your own database, the output is still escaped with htmlspecialchars(): the titles may originally have been typed by users.

Watch out: Never build SQL by joining strings with user input, as in "SELECT * FROM books WHERE author = '" . $_GET['author'] . "'". This is the classic SQL injection hole. Every value from outside your code must go through a placeholder.

Inserting data

Inserting with PHP PDO works the same way: validate the input as in PHP Forms, prepare the INSERT with placeholders and execute it with the values. lastInsertId() returns the AUTO_INCREMENT id of the new row, which is useful for redirecting to the new item’s page.

<?php
declare(strict_types=1);
require_once __DIR__ . '/db.php';

// Pretend this came from a validated POST form (see the Forms lesson)
$_POST = ['title' => "Chemmeen", 'author' => "Thakazhi Sivasankara Pillai", 'price' => '320', 'stock' => '6'];

$title  = trim($_POST['title'] ?? '');
$author = trim($_POST['author'] ?? '');
$price  = filter_var($_POST['price'] ?? '', FILTER_VALIDATE_INT, ['options' => ['min_range' => 1]]);
$stock  = filter_var($_POST['stock'] ?? '', FILTER_VALIDATE_INT, ['options' => ['min_range' => 0]]);

if ($title === '' || $author === '' || $price === false || $stock === false) {
    exit('Please fill in every field correctly.');
}

$stmt = $pdo->prepare(
    'INSERT INTO books (title, author, price, stock) VALUES (?, ?, ?, ?)'
);
$stmt->execute([$title, $author, $price, $stock]);

echo 'Saved "' . htmlspecialchars($title) . '" with id ' . $pdo->lastInsertId();
Output
Example output (after the four sample rows): Saved "Chemmeen" with id 5

Updating and deleting

UPDATE and DELETE statements are prepared and executed in exactly the same way. Afterwards, rowCount() tells you how many rows were affected, so you can tell whether anything actually changed. The function below sells copies only when enough stock exists, and lets the database do the check in a single statement.

<?php
declare(strict_types=1);
require_once __DIR__ . '/db.php';

function sellCopies(PDO $pdo, int $bookId, int $quantity): bool
{
    $stmt = $pdo->prepare(
        'UPDATE books SET stock = stock - :qty WHERE id = :id AND stock >= :minStock'
    );
    $stmt->execute(['qty' => $quantity, 'id' => $bookId, 'minStock' => $quantity]);

    return $stmt->rowCount() === 1;   // 0 rows: no such book or not enough stock
}

echo sellCopies($pdo, 1, 3) ? "Sold 3 copies of book 1\n" : "Could not sell book 1\n";
echo sellCopies($pdo, 3, 1) ? "Sold 1 copy of book 3\n" : "Could not sell book 3\n";

$stmt = $pdo->prepare('SELECT title, stock FROM books WHERE id = ?');
$stmt->execute([1]);
$book = $stmt->fetch();
echo $book === false ? 'Not found' : "{$book['title']} now has {$book['stock']} in stock";
Output
Example output (with the sample rows above):
Sold 3 copies of book 1
Could not sell book 3
Ponniyin Selvan now has 5 in stock

Tip: When several queries must succeed or fail together – for example, reducing stock and creating an order – wrap them in a transaction with $pdo->beginTransaction(), $pdo->commit() and, in a catch block, $pdo->rollBack(). Tables must use the InnoDB engine, the default in MySQL and MariaDB.

When placeholders cannot help

Placeholders work for values only. Table names, column names and keywords such as ASC/DESC cannot be bound. When users choose a sort column, map their choice to a fixed list of allowed names with match and fall back to a safe default for anything else:

<?php
declare(strict_types=1);
require_once __DIR__ . '/db.php';

$_GET = ['sort' => 'price; DROP TABLE books'];   // a malicious request

$orderBy = match ($_GET['sort'] ?? '') {
    'title'  => 'title',
    'price'  => 'price',
    default  => 'id',
};

$rows = $pdo->query("SELECT id, title FROM books ORDER BY $orderBy LIMIT 2")->fetchAll();
foreach ($rows as $row) {
    echo "{$row['id']}: {$row['title']}\n";
}
Output
Example output (with the sample rows above; the malicious value falls back to sorting by id):
1: Ponniyin Selvan
2: Malgudi Days

Because only the strings written inside the match can ever reach the query, the malicious request simply gets the default order. With prepared statements for values and allow-lists for names, you have the two habits that keep a PHP database application safe. You have now covered the whole PHP track; try combining forms, validation, includes and PDO into a small project, such as a guest book or a booking form.

Further reading

Key takeaways

  • PDO is PHP's built-in database layer; the old mysql_* functions no longer exist.
  • Connect with a DSN that includes charset=utf8mb4, use exceptions, and log errors instead of showing them.
  • Use prepare() and execute() with placeholders for every value; never concatenate user input into SQL.
  • fetchAll(), fetch(), lastInsertId() and rowCount() cover most everyday needs.
  • Allow-list column names with match, escape output with htmlspecialchars(), and use transactions for related changes.

Quick quiz

Check what you learned – pick an answer.

1. Which code safely finds a user by the email address typed into a form?

2. What does $stmt->fetch() return when no row matches?

3. Why can't you use a placeholder for the column in ORDER BY :column?

Practice exercise

Using the books table and db.php, write author.php that reads $_GET['author'], finds all books by that author with a prepared statement, and prints each title with its stock. If no books are found, print "No books by this author".

Show a hint

Use WHERE author = :author, fetchAll(), and check whether the result is an empty array. Escape everything you print.

Show the solution
<?php
declare(strict_types=1);
require_once __DIR__ . '/db.php';

$author = trim($_GET['author'] ?? '');

$stmt = $pdo->prepare('SELECT title, stock FROM books WHERE author = :author ORDER BY title');
$stmt->execute(['author' => $author]);
$books = $stmt->fetchAll();

if ($books === []) {
    echo 'No books by this author';
} else {
    echo '<h2>Books by ' . htmlspecialchars($author) . "</h2>n<ul>n";
    foreach ($books as $book) {
        echo '  <li>' . htmlspecialchars($book['title']) . ' (' . $book['stock'] . " in stock)</li>n";
    }
    echo '</ul>';
}
// For ?author=R. K. Narayan the list shows Malgudi Days (15 in stock) and The Guide (4 in stock).

Progress is saved in this browser only.