Skip to content
Tutorials Center
My learning

Topics

Lesson 65 of 75 ยท Using MySQL from Code

MySQL from PHP with PDO

Connect PHP to MySQL with PDO: a utf8mb4 DSN, prepared statements, error modes, fetch modes, transactions and pooling notes, with runnable SQLite stand-ins.

PHP talks to MySQL through PDO (PHP Data Objects), a single interface for many databases. If you learn PDO once, the same code runs against MySQL, PostgreSQL or SQLite with only a connection string changed. This lesson shows the safe way to connect, the settings every project should set, and the patterns for queries and transactions.

The site's PHP runner has pdo_sqlite but no MySQL server and no pdo_mysql. So the connection code is shown as read-only, and every runnable example uses PDO with an in-memory SQLite database. PDO's methods behave the same way, so the patterns carry over unchanged. The differences (placeholders for lists, LAST_INSERT_ID, charset) are pointed out where they matter. For the language basics, see the PHP tutorial.

Connecting

A PDO connection needs a DSN (data source name), a user and a password:

PHP
<?php
$dsn = 'mysql:host=127.0.0.1;port=3306;dbname=shop;charset=utf8mb4';

$pdo = new PDO($dsn, 'shop_app', getenv('DB_PASSWORD'), [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
]);

Every piece matters:

  • charset=utf8mb4 sets the connection's character set, so emoji and every language survive the round trip. Without it, the server's default is used, which may differ. See Character Sets and Collations.
  • Use 127.0.0.1 rather than localhost if you want TCP; with localhost the driver may use a Unix socket. A unix_socket= DSN option selects one explicitly.
  • Read the password from the environment or a secrets file, never from source control.
  • ERRMODE_EXCEPTION makes failures throw PDOException. The old default silently returned false or only raised a warning, which hides bugs. Always set it.
  • FETCH_ASSOC returns rows as arrays keyed by column name. Without it you get each column twice (by name and by number).
  • EMULATE_PREPARES => false asks MySQL itself to prepare statements, so values are sent separately from the SQL text, and integers come back as PHP integers in modern versions. With emulation on, PDO inserts escaped values into the text before sending it.

Since PHP 8.4 you can also call PDO::connect() to get a driver-specific subclass such as Pdo\Mysql, which exposes MySQL-only constants and methods. The classic new PDO(...) still works everywhere.

Wrap the connection in a function or service so that your whole application shares one object per request. PHP-FPM creates a new connection for each request unless you use persistent connections (PDO::ATTR_PERSISTENT), which reuse connections but can leave session state behind (open transactions, temporary tables, SET variables). Most applications are best off with ordinary connections plus a connection limit that matches the server's max_connections, and use a proxy such as ProxySQL when thousands of workers are involved. See High Availability.

Prepared statements

Never build SQL by joining user input into a string. A prepared statement sends the SQL shape first and the values separately, so a value can never change the meaning of the query. This is the defense against SQL injection; Securing MySQL, shows what goes wrong without it.

PHP
$stmt = $pdo->prepare('SELECT id, title, price FROM books WHERE price <= :max AND title LIKE :q');
$stmt->execute(['max' => 30, 'q' => '%query%']);
$rows = $stmt->fetchAll();

Use named placeholders (:max) or question marks (?) for values. Placeholders cannot stand in for table names, column names or ORDER BY directions. For those, check the input against an allow-list in PHP and put the checked word into the SQL yourself.

Here is a runnable version. It prints rows from an in-memory database, and the second query shows that a hostile string is treated as plain data:

PHP Prepared statements with PDO

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

Two books are listed, and the hostile string matches zero rows, because it is compared as one long title and is never parsed as SQL.

For IN lists, build one placeholder per value:

PHP
$ids = [3, 7, 9];
$marks = implode(',', array_fill(0, count($ids), '?'));
$stmt = $pdo->prepare("SELECT * FROM books WHERE id IN ($marks)");
$stmt->execute($ids);

Fetch modes

fetch() and fetchAll() can shape rows in several ways:

Mode Result
PDO::FETCH_ASSOC ['id' => 1, 'title' => '...']
PDO::FETCH_NUM [1, '...']
PDO::FETCH_OBJ A stdClass with properties
PDO::FETCH_CLASS An instance of your own class
PDO::FETCH_COLUMN A flat list of one column
PDO::FETCH_KEY_PAIR [id => title] from a two-column query

For big result sets, loop over the statement (foreach ($stmt as $row)) instead of calling fetchAll(), so rows are read one at a time. With MySQL, PDO fetches the whole result into PHP memory by default (a buffered query). Set PDO::MYSQL_ATTR_USE_BUFFERED_QUERY to false to stream rows, but then you must finish reading before running another query on that connection.

Writing data and getting the new id

After an INSERT into a table with AUTO_INCREMENT, ask PDO for the id:

PHP
$stmt = $pdo->prepare('INSERT INTO books (title, price) VALUES (?, ?)');
$stmt->execute(['New Book', 19.0]);
$newId = (int) $pdo->lastInsertId();
$changed = $stmt->rowCount();

lastInsertId() calls MySQL's LAST_INSERT_ID(), which is per connection, so other sessions cannot disturb it. For UPDATE, MySQL's rowCount() returns the number of rows that actually changed, not the number that matched, unless you connect with PDO::MYSQL_ATTR_FOUND_ROWS => true. AUTO_INCREMENT, UUIDs and ID Strategies goes deeper.

Transactions

Group related changes so that they all happen or none do. The pattern is: begin, do the work, commit, and roll back on any exception.

PHP A transfer in a PDO transaction

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The first transfer succeeds (70 and 50). The second would push Ben below zero, so the CHECK constraint throws, the code rolls back, and the balances stay as they were. MySQL's CHECK constraints are enforced since 8.0.16; see Keys and Constraints.

A few MySQL-specific rules:

  • DDL statements such as CREATE TABLE and ALTER TABLE commit implicitly, so you cannot roll them back. PDO::inTransaction() can then report an unexpected state.
  • Keep transactions short. Do not call a remote API or wait for a user while one is open.
  • Under contention, MySQL may abort one transaction with a deadlock (error 1213) or a lock wait timeout (1205). Catch these and retry the whole transaction a few times. See Locking, Gap Locks and Deadlocks.
  • With MySQL, the exception's errorInfo[1] holds the driver error number, which is how you tell a duplicate key (1062) from a deadlock.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why set PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION?

    Without it, a failed query may return false or only raise a warning, which hides bugs. Exceptions make errors impossible to miss.
  2. 2. Which part of a query can a placeholder safely replace?

    Placeholders carry values only. Identifiers and keywords must be chosen from an allow-list in PHP.
  3. 3. What does charset=utf8mb4 in the DSN control?

    It sets the character set for communication with the server, so that all Unicode text, including emoji, round-trips correctly.
  4. 4. A transaction fails with a deadlock error. What is the usual correct response?

    MySQL rolls back the victim transaction. Your code should roll back, then run the whole unit of work again.

Summary

  • Connect with a DSN including charset=utf8mb4, and read secrets from the environment.
  • Always set ERRMODE_EXCEPTION, a default fetch mode and EMULATE_PREPARES => false.
  • Use prepared statements with placeholders for every value; allow-list identifiers.
  • Use beginTransaction, commit and rollBack, keep transactions short, and retry on deadlocks.
  • DDL commits implicitly, rowCount() on MySQL counts changed rows, and persistent connections carry session state.

Next: MySQL from Python and Node.js.