How to Connect to MySQL from PHP

PHP has two built-in ways to reach MySQL: the database-agnostic PDO layer and the MySQL-specific mysqli extension. For new code, PDO with the pdo_mysql driver is the right default. It gives you a portable API, real prepared statements, and a clean exception model. mysqli is still maintained and exposes a few MySQL-only features, but most applications never need it. The old mysql_* functions were removed in PHP 7.0 years ago; if you find them in a codebase, that code is overdue for a rewrite. This guide covers both supported extensions, the charset that trips everyone up, prepared statements, transactions, SSL, and the errors you will meet. It is written against PHP 8.5 (8.5.7 as of June 2026), but everything except the driver-subclass note works on 8.1+.

Which extension to use

Two extensions ship with PHP:

• PDO with pdo_mysql is the portable, object-oriented choice. Same interface across databases, exceptions on error, and named or positional placeholders. Use this unless you have a specific reason not to.
• mysqli (the functions and classes starting with mysqli) is the MySQL-specific extension. It exposes a few MySQL-only features such as multiple-statement execution and asynchronous queries. Reach for it only when you actually need those features.

Check that the driver is loaded:

php -m | grep mysql
# expect: pdo_mysql  (and/or mysqli)

On Debian/Ubuntu, apt install php-mysql provides both.

Connecting with PDO

The single most common MySQL-from-PHP mistake is leaving the charset out of the DSN. Set it to utf8mb4, not utf8. MySQL's legacy utf8 is a three-byte encoding that cannot store emoji or many CJK characters, and inserting one silently truncates the row or throws an Incorrect string value error. utf8mb4 is the real, full Unicode encoding.

<?php
$dsn = 'mysql:host=127.0.0.1;port=3306;dbname=shop;charset=utf8mb4';
 
try {
    $pdo = new PDO($dsn, 'appuser', 'secret', [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ]);
} catch (PDOException $e) {
    // Do not echo $e->getMessage() to users; it can leak the DSN
    error_log('DB connection failed: ' . $e->getMessage());
    throw $e;
}

Three options are worth setting on every connection:

• ERRMODE_EXCEPTION makes PDO throw on errors instead of returning false. Without it, a failed query looks like success until you check a return value, which almost nobody does consistently.
• FETCH_ASSOC returns rows as associative arrays instead of the default duplicated numeric-and-associative array.
• ATTR_EMULATE_PREPARES => false switches off PDO's client-side statement emulation, which is on by default for MySQL. This is the one default worth changing. See the next section for why.

Use 127.0.0.1 rather than localhost in the DSN unless you specifically want a Unix socket. On many systems localhost makes the MySQL client try the socket and ignore the port, which produces confusing connection errors when the socket path is non-standard.

Emulated vs real prepared statements

For MySQL, PDO emulates prepared statements by default: it interpolates the bound values into the SQL string itself (with correct escaping) and sends one round trip. Setting ATTR_EMULATE_PREPARES => false tells PDO to send the parameterized statement to the server and bind values server-side, the way most people assume prepared statements always work.

The practical differences:

• With emulation off, numeric columns come back typed as integers/floats instead of every value being a string. This alone is reason enough to disable it.
• With emulation off, the server caches the query plan, which helps when you run the same statement in a loop.
• With emulation on, you can run multiple statements in one query() call, which the server protocol does not allow for a single real prepared statement.

Disable emulation unless you have a concrete reason to keep it. Either way, always bind values rather than concatenating them.

Prepared statements

Never interpolate user input into SQL. Use placeholders and let the driver bind them:

$stmt = $pdo->prepare('SELECT id, name FROM products WHERE price > :minPrice');
$stmt->execute(['minPrice' => 100]);
 
foreach ($stmt as $row) {
    echo "{$row['id']}: {$row['name']}\n";
}

PDO supports named placeholders (:minPrice) and positional ones (?). Pick one style per statement; you cannot mix them. Named placeholders are easier to read and reorder once a query has more than two or three parameters.

Getting a generated key

After an INSERT into a table with an AUTO_INCREMENT column, read the value with lastInsertId():

$stmt = $pdo->prepare('INSERT INTO products (name, price) VALUES (:name, :price)');
$stmt->execute(['name' => 'Widget', 'price' => 9.99]);
$newId = $pdo->lastInsertId();

Unlike PostgreSQL, MySQL has no RETURNING clause in stable releases, so lastInsertId() is the standard approach. It returns the ID generated by the most recent insert on that connection, so read it immediately after the execute().

The PDO driver subclasses (PHP 8.4+)

PHP 8.4 added driver-specific PDO subclasses: Pdo\Mysql, Pdo\Pgsql, Pdo\Sqlite, and others. Constructing through Pdo\Mysql::connect() gives you a typed object that exposes MySQL-only helpers such as getWarningCount() as first-class methods:

// PHP 8.4+
$pdo = Pdo\Mysql::connect($dsn, 'appuser', 'secret');

If you only run portable SQL, plain PDO is still fine. The subclass matters when you want MySQL-specific methods without dropping to mysqli.

Transactions

$pdo->beginTransaction();
try {
    $pdo->prepare('UPDATE accounts SET balance = balance - :amt WHERE id = :id')
        ->execute(['amt' => 50, 'id' => 1]);
    $pdo->prepare('UPDATE accounts SET balance = balance + :amt WHERE id = :id')
        ->execute(['amt' => 50, 'id' => 2]);
    $pdo->commit();
} catch (Throwable $e) {
    $pdo->rollBack();
    throw $e;
}

Transactions only work on transactional storage engines. InnoDB, the default since MySQL 5.5, is transactional. The legacy MyISAM engine is not: beginTransaction() and rollBack() silently do nothing on a MyISAM table, so a half-finished multi-row change stays half-finished. Confirm your tables are InnoDB before relying on rollback. Also note that DDL statements such as CREATE TABLE and ALTER TABLE cause an implicit commit, so you cannot roll them back.

SSL/TLS

For connections crossing a network, encrypt and verify the server certificate. With PDO you pass the certificate paths as driver options:

$pdo = new PDO($dsn, 'appuser', 'secret', [
    PDO::ATTR_ERRMODE          => PDO::ERRMODE_EXCEPTION,
    PDO::MYSQL_ATTR_SSL_CA     => '/etc/ssl/certs/mysql-ca.pem',
    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => true,
]);

Setting only MYSQL_ATTR_SSL_CA encrypts the connection and validates the CA. Leaving SSL_VERIFY_SERVER_CERT off means the certificate's hostname is not checked, which leaves you open to a man-in-the-middle on an otherwise encrypted connection. Turn it on for anything public-facing.

A note on authentication

MySQL 8.0 made caching_sha2_password the default authentication plugin. Over a non-TLS connection, the first authentication for a user needs either an encrypted channel or RSA key exchange, which is why some apps that worked against MySQL 5.7 fail to connect to 8.0 with an authentication error until TLS is enabled. MySQL 9.0 removed the older mysql_native_password plugin entirely. The fix is almost always to connect over TLS rather than to downgrade the auth plugin.

Using mysqli directly

If you need a MySQL-only feature, the mysqli object interface is there. Enable exceptions first; otherwise mysqli reports errors through return values, the same trap PDO's ERRMODE_EXCEPTION avoids:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
 
$mysqli = new mysqli('127.0.0.1', 'appuser', 'secret', 'shop', 3306);
$mysqli->set_charset('utf8mb4');
 
$stmt = $mysqli->prepare('SELECT id, name FROM products WHERE price > ?');
$stmt->bind_param('d', $minPrice);  // 'd' = double
$minPrice = 100;
$stmt->execute();
$result = $stmt->get_result();
 
while ($row = $result->fetch_assoc()) {
    echo "{$row['id']}: {$row['name']}\n";
}

mysqli uses ? positional placeholders only, and bind_param requires a type string (i integer, d double, s string, b blob). Set the charset with set_charset('utf8mb4') rather than running a SET NAMES query, because set_charset also updates the client's idea of the encoding for escaping.

Errors you actually hit

For exploring MySQL data and drafting queries before they go into PHP, Mako connects to MySQL with AI-powered autocomplete. Try it free at mako.ai.