Chapter 8
Chapter 8 — MySQL with Apache and PHP
Installing the PHP MySQL extension is straightforward. Using it correctly — with prepared statements, proper error handling, and credentials stored outside the web root — is where most PHP applications fall short. This chapter builds a solid, secure foundation for PHP-MySQL development: the connection setup, the credential storage pattern, and the query techniques you'll use on every project.
What this chapter covers: the three PHP MySQL APIs and why PDO is the right choice. Installing
php-mysql on Ubuntu/Debian. Keeping database credentials out of the web root — the most important security rule. The PDO connection with all three essential options. Prepared statements for SELECT, INSERT, UPDATE, and DELETE. Error handling that logs without leaking. Transaction handling. A complete practical example.
The Three PHP MySQL APIs
PHP has three ways to talk to MySQL. Two of them are wrong choices for new code.
mysql_*
Removed — PHP 7+
- Removed from PHP 7. Dead.
- No prepared statements → SQL injection by default
- Old tutorials still use it. Don't copy them.
MySQLi
OK — MySQL only
- MySQL-specific. Won't work with PostgreSQL or SQLite.
- Prepared statements supported
- OOP and procedural interfaces — inconsistent
- Reasonable choice if you're sure you'll never switch databases
PDO
Recommended
- Works with MySQL, PostgreSQL, SQLite, and more
- Clean, consistent OOP interface
- Named placeholders (
:name) — much more readable than? - Exception-based error handling
- This chapter uses PDO exclusively
Installing the PHP MySQL Extension
# Install php-mysql — this installs BOTH MySQLi and PDO_MySQL drivers
$ sudo apt update && sudo apt install php-mysql
# If you're running a specific PHP version (e.g. 8.2), the package is version-pinned
$ sudo apt install php8.2-mysql
# ALWAYS restart Apache after installing PHP extensions
$ sudo systemctl restart apache2
# Verify both drivers loaded
$ php -m | grep -i mysql
mysqli
mysqlnd
PDO
pdo_mysql
# Check which PDO drivers are available
$ php -r "print_r(PDO::getAvailableDrivers());"
Array
(
[0] => mysql
[1] => sqlite
)
Restart is not optional: PHP extensions are loaded at Apache startup. Installing
php-mysql without restarting Apache leaves the old PHP process running without the driver. The result is a cryptic "could not find driver" PDOException even though the package is installed. sudo systemctl restart apache2 every time you install or remove a PHP extension.
Credentials Outside the Web Root
This is the single most important security rule in PHP-MySQL development. Your database password must never be inside a directory that Apache can serve.
Why the web root is dangerous for credentials
If your db.php is inside /var/www/html/, any of these can expose the password:
A missing or misconfigured
.htaccess / Apache rule could cause PHP files to be served as plain text instead of executed. A misconfigured PHP install. A malicious include path. Apache directory listing with the wrong permissions. A future accidental git push to a public repository. A LFI (local file inclusion) vulnerability in another file on the same server.
The directory layout that protects credentials
/var/www/
├── config/ ← ABOVE the web root. Apache cannot serve this.
│ └── db.php ← database credentials live here
│
└── html/ ← Apache DocumentRoot (/var/www/html)
├── db_WRONG.php ← NEVER put credentials here
├── index.php ← your application
├── books.php
└── api/
└── search.php
Apache's DocumentRoot is /var/www/html — it serves everything inside that directory. Anything in /var/www/ but above /var/www/html/ is unreachable from the web, but still accessible to your PHP scripts with require.
# Create the config directory above the web root
$ sudo mkdir -p /var/www/config
# Restrict permissions: www-data can read, nobody else can
$ sudo chown root:www-data /var/www/config
$ sudo chmod 750 /var/www/config
The credentials file — db.php
# /var/www/config/db.php
$ sudo nano /var/www/config/db.php
<?php
// Abort if this file is somehow reached via HTTP
// (belt-and-suspenders: the file should not be web-accessible at all)
if (php_sapi_name() === 'cli' || defined('STDIN')) {
// running from command line — OK
} elseif (!defined('APP_ROOT')) {
// not called from within the app
http_response_code(403);
exit('Forbidden');
}
return [
'host' => 'localhost', // socket connection — fastest
'dbname' => 'bookshop',
'charset' => 'utf8mb4', // always utf8mb4 — full Unicode + emoji
'username' => 'bookshop_app',
'password' => 'StrongPassw0rd!',
];
# Set file permissions: readable only by root and www-data
$ sudo chown root:www-data /var/www/config/db.php
$ sudo chmod 640 /var/www/config/db.php
# Verify Apache (www-data) can read it but others cannot
$ sudo -u www-data cat /var/www/config/db.php # should succeed
$ cat /var/www/config/db.php # your user — should succeed as it's in www-data group
Multiple sites on the same server? Give each site its own config directory:
/var/www/site1/config/, /var/www/site2/config/. Each PHP process only needs to read its own site's credentials, not all sites' credentials.
The PDO Connection
DSN — the data source name
The DSN is a string that describes the database connection. For MySQL it looks like this:
# DSN format
mysql:host=localhost;dbname=bookshop;charset=utf8mb4
# host=localhost → Unix socket connection (faster, same machine)
# host=127.0.0.1 → TCP connection (use only if you have a specific reason)
# charset=utf8mb4 → send charset in DSN, not as a SET NAMES query (safer)
The three essential PDO options
ATTR_ERRMODE
→ ERRMODE_EXCEPTION
→ ERRMODE_EXCEPTION
Always set this. Without it, PDO silently returns
false on errors — and most PHP code doesn't check for false. With this option, any database error throws a PDOException which your try/catch can handle. The default (ERRMODE_SILENT) is dangerous.ATTR_DEFAULT_FETCH_MODE
→ FETCH_ASSOC
→ FETCH_ASSOC
Set this to avoid surprises. By default PDO returns both numeric and associative keys (
$row[0] and $row['title']). FETCH_ASSOC returns named keys only — cleaner, smaller arrays, no accidental use of column position.ATTR_EMULATE_PREPARES
→ false
→ false
Use real prepared statements. When true (the default!), PDO emulates prepared statements by doing string substitution in PHP, then sending the final SQL to MySQL. When false, MySQL handles the prepared statement — you get proper type safety and the protection is enforced at the database level, not in PHP. Always set to
false.The connection factory — db_connect.php
<?php
// /var/www/html/includes/db_connect.php
// This file is inside the web root but contains NO credentials.
// It reads them from the config file above the web root.
define('APP_ROOT', true); // allows db.php to verify it's called from within the app
function get_db(): PDO
{
static $pdo = null;
if ($pdo !== null) {
return $pdo; // reuse existing connection within the same request
}
$cfg = require '/var/www/config/db.php'; // above the web root
$dsn = sprintf(
'mysql:host=%s;dbname=%s;charset=%s',
$cfg['host'],
$cfg['dbname'],
$cfg['charset']
);
$pdo = new PDO($dsn, $cfg['username'], $cfg['password'], [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
]);
return $pdo;
}
Why the
static variable: PHP creates a new process (or reuses a worker) per HTTP request. Within a single request, static $pdo ensures get_db() connects once and returns the same connection object on every subsequent call — no reconnection overhead. Between requests the connection is closed naturally. You don't need a connection pool in most PHP setups because PHP-FPM handles this at the process level.
Prepared Statements — Not Optional
A prepared statement separates the SQL structure from the data. MySQL receives the query template first, compiles it, then receives the values separately. The values are never interpreted as SQL — SQL injection is structurally impossible.
Never build SQL by string concatenation with user input:
$sql = "SELECT * FROM books WHERE title = '" . $_GET['q'] . "'"; — this is SQL injection. If $_GET['q'] is ' OR '1'='1, the WHERE clause becomes always-true and returns every row. If it's '; DROP TABLE books; --, it drops the table. Always use prepared statements for any value that comes from outside your code: user input, URL parameters, form data, API responses, file contents.
SELECT — single row
<?php
// Get one book by ID
require_once __DIR__ . '/includes/db_connect.php';
$book_id = (int) $_GET['id']; // cast to int before using (extra safety layer)
$pdo = get_db();
$stmt = $pdo->prepare('SELECT id, title, author, price FROM books WHERE id = :id');
$stmt->execute([':id' => $book_id]);
$book = $stmt->fetch(); // fetch() returns one row as array, or false if no match
if ($book === false) {
// No row found
http_response_code(404);
echo 'Book not found';
exit;
}
// $book is now ['id' => 42, 'title' => 'Dune', 'author' => 'Herbert', 'price' => '9.99']
echo htmlspecialchars($book['title']); // always escape output for HTML
SELECT — multiple rows
<?php
// Search books by author — user input from a search form
$search = trim($_GET['author'] ?? '');
$stmt = $pdo->prepare(
'SELECT id, title, author, price
FROM books
WHERE author LIKE :author
ORDER BY title
LIMIT 50'
);
$stmt->execute([':author' => '%' . $search . '%']);
$books = $stmt->fetchAll(); // returns array of all matching rows (empty array if none)
foreach ($books as $book) {
echo htmlspecialchars($book['title']) . ' by ' . htmlspecialchars($book['author']);
echo ' — £' . number_format($book['price'], 2) . "\n";
}
// Note: the LIKE wildcard % is safe to concatenate here because it's not user input
// — the user's actual text goes through the prepared statement placeholder :author
INSERT
<?php
// Add a new book
$stmt = $pdo->prepare(
'INSERT INTO books (title, author, price, isbn) VALUES (:title, :author, :price, :isbn)'
);
$stmt->execute([
':title' => $_POST['title'],
':author' => $_POST['author'],
':price' => (float) $_POST['price'],
':isbn' => $_POST['isbn'],
]);
$new_id = (int) $pdo->lastInsertId(); // the AUTO_INCREMENT id of the new row
echo "Created book #$new_id";
UPDATE and DELETE
<?php
// Update a book's price
$stmt = $pdo->prepare('UPDATE books SET price = :price WHERE id = :id');
$stmt->execute([':price' => (float) $_POST['price'], ':id' => (int) $_POST['id']]);
$rows_changed = $stmt->rowCount(); // number of rows actually modified
if ($rows_changed === 0) {
echo 'No book found with that ID, or price was already the same';
}
// Delete a book
$stmt = $pdo->prepare('DELETE FROM books WHERE id = :id');
$stmt->execute([':id' => (int) $_POST['id']]);
echo $stmt->rowCount() . ' book(s) deleted';
Reusing a prepared statement in a loop
<?php
// Inserting many rows efficiently — prepare ONCE, execute MANY TIMES
$books_to_import = [
['title' => 'Foundation', 'author' => 'Asimov', 'price' => 8.99],
['title' => 'Neuromancer', 'author' => 'Gibson', 'price' => 7.99],
['title' => 'Hyperion', 'author' => 'Simmons', 'price' => 9.99],
];
$stmt = $pdo->prepare('INSERT INTO books (title, author, price) VALUES (:title, :author, :price)');
foreach ($books_to_import as $book) {
$stmt->execute($book); // PHP matches array keys to named placeholders automatically
}
Error Handling — Log, Don't Leak
With ERRMODE_EXCEPTION set, any database error throws a PDOException. The exception's getMessage() contains the SQL error — useful for debugging, but dangerous to display to users.
Never display PDOException messages to users: a PDOException message can contain the table name, column name, failing query, and sometimes partial data. Attackers look for these. A caught exception that echoes
$e->getMessage() directly to the page is a reconnaissance gift.
<?php
// ── The correct pattern: log the real error, show a generic message ──
function get_book(int $id): ?array
{
try {
$pdo = get_db();
$stmt = $pdo->prepare('SELECT * FROM books WHERE id = :id');
$stmt->execute([':id' => $id]);
$row = $stmt->fetch();
return $row === false ? null : $row;
} catch (PDOException $e) {
// Log the full error — visible only in the server log, never to the user
error_log('DB error in get_book(): ' . $e->getMessage());
// Re-throw a generic exception — your error handler shows a friendly page
throw new RuntimeException('A database error occurred. Please try again later.');
}
}
// At the top level (e.g. index.php) — catch the generic exception for the user
try {
$book = get_book(42);
} catch (RuntimeException $e) {
// Show a safe, generic message — no database details
echo '<p class="error">' . htmlspecialchars($e->getMessage()) . '</p>';
}
# PHP logs database errors to the Apache error log by default
# Watch it in real time during development
$ sudo tail -f /var/log/apache2/error.log
[Mon Jun 15 14:30:01.123456 2026] [php:error] [pid 12345] [client 127.0.0.1:42841]
PHP Fatal error: Uncaught PDOException: SQLSTATE[HY000] [1045]
Access denied for user 'bookshop_app'@'localhost'
DB error in get_book(): SQLSTATE[42S02]: Base table or view not found: 1146 Table 'bookshop.bookz' doesn't exist
# Full error in the log — no user ever sees this
Transaction Handling
When an operation involves multiple writes that must all succeed or all fail together — creating an order and deducting stock, for example — wrap them in a transaction.
<?php
// Place an order: insert order row + decrement stock — both must succeed
function place_order(int $book_id, int $customer_id, int $qty): int
{
$pdo = get_db();
try {
$pdo->beginTransaction();
// Step 1: Check stock (lock the row so nobody else can race us)
$stmt = $pdo->prepare(
'SELECT stock FROM books WHERE id = :id FOR UPDATE'
);
$stmt->execute([':id' => $book_id]);
$row = $stmt->fetch();
if (!$row || $row['stock'] < $qty) {
$pdo->rollBack();
throw new RuntimeException('Insufficient stock');
}
// Step 2: Insert order row
$stmt = $pdo->prepare(
'INSERT INTO orders (book_id, customer_id, quantity, created_at)
VALUES (:book_id, :customer_id, :qty, NOW())'
);
$stmt->execute([
':book_id' => $book_id,
':customer_id' => $customer_id,
':qty' => $qty,
]);
$order_id = (int) $pdo->lastInsertId();
// Step 3: Decrement stock
$stmt = $pdo->prepare(
'UPDATE books SET stock = stock - :qty WHERE id = :id'
);
$stmt->execute([':qty' => $qty, ':id' => $book_id]);
$pdo->commit(); // all steps succeeded — write to disk
return $order_id;
} catch (PDOException $e) {
$pdo->rollBack(); // any DB error → undo all steps in the transaction
error_log('place_order() failed: ' . $e->getMessage());
throw new RuntimeException('Could not place order. Please try again.');
}
}
Always call rollBack() in the catch block. If a
PDOException is thrown mid-transaction and you don't roll back, InnoDB holds the locks until the connection closes (end of the PHP request) — potentially seconds. During that time every other query touching those rows is blocked. The rollBack() releases the locks immediately.
Practical Example — User Login
The classic PHP-MySQL pattern: check a submitted username/password against the database. This example shows the correct approach: no SQL injection, no timing attacks, no credential leaks.
<?php
// login.php — verify username and password
require_once __DIR__ . '/includes/db_connect.php';
function check_login(string $username, string $password): ?array
{
try {
$pdo = get_db();
$stmt = $pdo->prepare(
'SELECT id, username, password_hash, role
FROM users
WHERE username = :username
LIMIT 1'
);
$stmt->execute([':username' => $username]);
$user = $stmt->fetch();
if ($user === false) {
// Username not found — but don't say that.
// Run the hash anyway to avoid timing attacks that reveal valid usernames
password_verify($password, '$2y$10$fakehashhere000000000000000000000000000000000000000000');
return null;
}
if (!password_verify($password, $user['password_hash'])) {
return null; // wrong password
}
// Upgrade hash if bcrypt cost factor has been increased since account creation
if (password_needs_rehash($user['password_hash'], PASSWORD_DEFAULT)) {
$new_hash = password_hash($password, PASSWORD_DEFAULT);
$upd = $pdo->prepare('UPDATE users SET password_hash = :hash WHERE id = :id');
$upd->execute([':hash' => $new_hash, ':id' => $user['id']]);
}
// Don't return the password hash to the caller
unset($user['password_hash']);
return $user;
} catch (PDOException $e) {
error_log('Login DB error: ' . $e->getMessage());
throw new RuntimeException('Login temporarily unavailable.');
}
}
// In your form handler
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
$user = check_login(
trim($_POST['username'] ?? ''),
$_POST['password'] ?? ''
);
if ($user) {
session_regenerate_id(true); // prevent session fixation
$_SESSION['user_id'] = $user['id'];
$_SESSION['username'] = $user['username'];
header('Location: /dashboard.php');
} else {
$error = 'Invalid username or password'; // same message for both cases
}
}
utf8mb4 — The Only Correct Character Set
MySQL's "utf8" is not real UTF-8. MySQL's
utf8 character set only stores 3-byte Unicode characters — it cannot store emoji, some Chinese characters, or other 4-byte code points. The correct character set is utf8mb4 (utf-8 with 4-byte support). Always use utf8mb4 in your DSN, on your database, and on your tables. Setting it in the PDO DSN ensures every string sent over the connection is interpreted correctly — no SET NAMES query needed.
# Verify your database and tables use utf8mb4
mysql> SELECT schema_name, default_character_set_name
-> FROM information_schema.schemata
-> WHERE schema_name = 'bookshop';
+-------------+----------------------------+
| schema_name | default_character_set_name |
+-------------+----------------------------+
| bookshop | utf8mb4 |
+-------------+----------------------------+
# Fix an existing database (does not convert existing table data)
mysql> ALTER DATABASE bookshop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# Fix an existing table (converts data in place)
mysql> ALTER TABLE books CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Troubleshooting
PDOException: "could not find driver"
The
pdo_mysql extension isn't loaded by the running Apache PHP. Steps: 1) Install: sudo apt install php-mysql. 2) Restart Apache: sudo systemctl restart apache2 — this is the most commonly skipped step. 3) Verify: run php -m | grep pdo_mysql from the shell, then also check via a phpinfo() page in the browser (the CLI and Apache PHP may have different configs). 4) If running PHP-FPM: restart php-fpm as well as Apache.
PDOException: "Access denied for user 'app'@'localhost'"
Either the wrong password, or a host mismatch. MySQL treats
localhost (Unix socket) and 127.0.0.1 (TCP) as different hosts. If you created the user as 'app'@'localhost' but your DSN uses host=127.0.0.1, MySQL sees 'app'@'127.0.0.1' — a different user that doesn't exist. Fix: keep host=localhost in the DSN (uses the socket, faster) and create the user as 'app'@'localhost'. Or change the DSN to 127.0.0.1 and create the user as 'app'@'127.0.0.1'. They must match.
PDOException: "No such file or directory" (SQLSTATE[HY000] [2002])
MySQL's Unix socket file can't be found.
host=localhost in PDO means "use the socket", but the socket path in php.ini may differ from MySQL's actual socket path. Check MySQL's socket: mysql -e "SHOW VARIABLES LIKE 'socket';" (typically /var/run/mysqld/mysqld.sock). Check PHP's configured path: php -i | grep pdo_mysql.default_socket. If they differ, either add unix_socket=/var/run/mysqld/mysqld.sock to your DSN, or set host=127.0.0.1 to switch to TCP.
Blank white page with no error
PHP is catching or swallowing the exception silently, or
display_errors is off (which is correct for production, but makes debugging hard). During development: check the Apache error log immediately — sudo tail -f /var/log/apache2/error.log. All PHP errors and uncaught exceptions appear there. Temporarily you can also set ini_set('display_errors', '1'); at the top of the script, but remove it before deploying.
Credentials visible in process list (ps aux) or shell history
Never pass database passwords on the command line. If you ran
php -r "new PDO(..., 'password')" during testing, that password is visible in ps aux while running and stored in shell history. For scripts that need the password: use the credentials file pattern, not environment variables set on the command line. Check shell history: history | grep -i pass — delete any lines containing passwords with history -d <line_number>, or clear the whole history with history -c.
Quick Reference — Chapter 8
| Pattern | The correct approach |
|---|---|
| Install extension | sudo apt install php-mysql && sudo systemctl restart apache2 |
| Verify drivers | php -m | grep -i mysql — expect mysqli, pdo_mysql |
| Credentials location | Above the web root: /var/www/config/db.php (not inside /var/www/html/) |
| Credentials file permissions | chown root:www-data + chmod 640 |
| DSN for local MySQL | mysql:host=localhost;dbname=mydb;charset=utf8mb4 |
| Required PDO options | ERRMODE_EXCEPTION + FETCH_ASSOC + EMULATE_PREPARES=false |
| User input in queries | Always via prepared statement placeholders — never string concatenation |
| Fetch one row | $stmt->fetch() — returns array or false |
| Fetch all rows | $stmt->fetchAll() — returns array of arrays (empty if none) |
| Last inserted ID | $pdo->lastInsertId() after INSERT |
| Rows affected | $stmt->rowCount() after UPDATE / DELETE |
| Error handling | catch PDOException → error_log($e->getMessage()) → throw generic RuntimeException |
| Transaction | beginTransaction() → execute steps → commit() or rollBack() in catch |
| Character set | Always utf8mb4 — never MySQL's utf8 |
| Password hashing | password_hash($pass, PASSWORD_DEFAULT) to store, password_verify() to check |