Connecting PHP to MySQL

PHP has two main extensions for talking to MySQL.

Both are fine. This course uses PDO: it is the more flexible one, and most frameworks use it.

The connection details

To connect, PHP needs four things:

On local servers such as XAMPP, the user is often root with an empty password. Never do that on a live site. Create a separate user with a strong password and only the rights it needs.

A first connection with PDO

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

<?php
declare(strict_types=1);

$dsn = "mysql:host=127.0.0.1;dbname=shop;charset=utf8mb4";

try {
    $pdo = new PDO($dsn, "shop_user", "a-strong-password", [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]);

    echo "Connected to MySQL.";
} catch (PDOException $e) {
    error_log($e->getMessage());
    exit("Could not connect to the database.");
}

1

Connected to MySQL.

What the pieces mean:

• $dsn (data source name): where and what to connect to. It starts with the driver name (mysql:).
• charset=utf8mb4 makes the connection use full UTF-8.
• The options array sets safe defaults. The next two lessons explain each one.
• try/catch handles a failed connection. The message goes to the log. The visitor sees a short, harmless sentence.

The same with mysqli (for comparison)

1
2
3
4
5
6
7

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$mysqli = new mysqli("127.0.0.1", "shop_user", "a-strong-password", "shop");
$mysqli->set_charset("utf8mb4");

echo "Connected with mysqli.";

Is the extension installed?

1
2

<?php
var_dump(extension_loaded("pdo_mysql") || extension_loaded("pdo_sqlite"));

1

bool(true)

If pdo_mysql is missing, enable extension=pdo_mysql in php.ini and restart the web server.

Common connection errors

Do not hide the password in plain view

Never put real credentials in a file that is inside the public web folder or in Git. Read them from environment variables or a config file outside the web root. Database Connections shows how.