Using databases with PHP

Here is an example of how to use your MySQL (or MariaDB) databases in a PHP script, with PDO, the recommended interface since PHP 7. The old mysql_* functions were removed in PHP 7.0.

The database used is the one described in the article How do I create a database?. We created a livres (books) table in it with 3 fields: id, auteur (author), titre (title).

1. Keep the credentials apart

Create a config.php file outside public_html (for example in your account's home folder), so that nobody can read it from the web:

<?php
return [
    'dsn'      => 'mysql:host=localhost;dbname=myaccount_projet01;charset=utf8mb4',
    'user'     => 'myaccount_user',
    'password' => 'your-password',
];

2. The script

The following script displays the title and author of each book:

<?php
$config = require __DIR__ . '/../config.php';

try {
    $pdo = new PDO($config['dsn'], $config['user'], $config['password'], [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);
} catch (PDOException $e) {
    error_log($e->getMessage());
    exit('Database connection error.');
}

foreach ($pdo->query('SELECT titre, auteur FROM livres ORDER BY titre') as $book) {
    echo 'Title: ' . htmlspecialchars($book['titre']) . ', ';
    echo 'Author: ' . htmlspecialchars($book['auteur']) . "<br>\n";
}

3. With a value from the visitor: a prepared statement

Never put a received value (form, URL) straight into the SQL: use a prepared statement, which protects against SQL injection.

<?php
$query = $pdo->prepare('SELECT titre, auteur FROM livres WHERE auteur = :auteur');
$query->execute(['auteur' => $_GET['auteur'] ?? '']);

foreach ($query as $book) {
    echo htmlspecialchars($book['titre']) . "<br>\n";
}

Good practices

• htmlspecialchars() when displaying, so no HTML gets injected into the page.
• The detailed error message goes to the log (error_log), never on screen.
• charset=utf8mb4 in the DSN, for accents and emojis.
• One MySQL user per site, with only the rights it needs.