03.2 Connecting to MariaDB or MySQL using PDO

01 | THREE PARTS OF THE SYSTEM

Use PHP 8.1 or later for these examples. Create a project folder with public and private subfolders. Start the local server from the project root with php -S 127.0.0.1:8000 -t public , then open the requested .php page through HTTP.

The public folder is the document root: files the browser can request. The private folder is a sibling, not a child of public. PHP can read it through filesystem paths, but it is not directly served by this configuration.

The examples are for local single-user practice. Database pages require a running MariaDB or MySQL server and the PDO MySQL driver. PHP’s built-in web server does not install or start the database.

The browser does not receive your database password. PHP uses it to open the connection on the server side, then chooses what response to build.

02 | CHECK THE LOCAL PREREQUISITES

Use a teacher-prepared local MariaDB/MySQL server or follow the official installation guidance for your operating system. Record the host, port and administrator access details. Installing PHP alone does not supply these.

Check the PHP driver in the VS Code terminal:

php -r "print_r(PDO::getAvailableDrivers());"

The list must include mysql. If PDO itself or the mysql driver is absent, install or enable the matching extension for the PHP runtime actually running your server. Restart that development server after changing its extension configuration.

The PDO MySQL driver can connect to the MariaDB server used in this example. A mysql: DSN is expected; it is not a typo requiring a mariadb: prefix.

03 | CREATE A FRESH PRACTICE DATABASE

Open your database administration client, connect as an administrator and run setup.sql once in a fresh local environment. A school may provide an SQL editor or phpMyAdmin; use its SQL execution interface, not a .php file in the browser.

Replace the password placeholder with a local practice password before running the account lines. This is not a website-user password or a shared default to deploy.

setup.sql

-- Run once as a database administrator in a fresh local practice database.
CREATE DATABASE club_practice CHARACTER SET utf8mb4;
USE club_practice;
CREATE TABLE Clubs (
    club_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    club_name VARCHAR(100) NOT NULL UNIQUE,
    capacity SMALLINT UNSIGNED NOT NULL,
    CONSTRAINT capacity_range CHECK (capacity BETWEEN 1 AND 200)
) ENGINE=InnoDB;
INSERT INTO Clubs (club_name, capacity)
VALUES ('Robotics', 12), ('Art & Design', 10), ('Coding', 16);

-- Replace the placeholder before running these lines.
-- The host matches the local TCP connection in config.php.
CREATE USER 'club_app'@'127.0.0.1' IDENTIFIED BY 'REPLACE_WITH_YOUR_LOCAL_PASSWORD';
GRANT SELECT, INSERT, UPDATE, DELETE ON club_practice.Clubs
TO 'club_app'@'127.0.0.1';

Use an InnoDB table. The example targets current MariaDB/MySQL versions with enforced CHECK constraints. The application will also validate capacity rather than relying on browser controls.

04 | SEPARATE ADMINISTRATION FROM APPLICATION ACCESS

The administrator creates the schema and account. The application account gets only SELECT, INSERT, UPDATE and DELETE on the one practice table; it does not need to create users or drop databases.

In MariaDB/MySQL, user accounts include a host component. Our setup account and configuration use 127.0.0.1 for a local TCP connection. A mismatched host, authentication setup or password can prevent access.

For a school-managed or hosted server, use the exact assigned details and permitted account-creation route. Do not change another shared database’s access rules to match this tutorial.

05 | KEEP THE CONFIGURATION PRIVATE

private/config.example.php

<?php
// Copy to config.php in this private folder. Set your local practice values.
return [
    'host' => '127.0.0.1',
    'port' => 3306,
    'database' => 'club_practice',
    'username' => 'club_app',
    'password' => 'REPLACE_WITH_YOUR_LOCAL_PASSWORD',
];

Copy this file to private/config.php and fill in the real local values. Do not commit that filled file to a shared source repository, publish it as a download or place it in public.

The example file contains placeholders only. The finished server configuration must preserve the public-only document root; merely naming a folder private does not configure a web server’s access.

06 | OPEN A PDO CONNECTION

private/database.php

<?php
function openDatabase(): PDO
{
    $path = __DIR__ . '/config.php';
    if (!is_file($path)) {
        throw new RuntimeException('Create private/config.php from the example.');
    }
    $config = require $path;
    if (!is_array($config)) {
        throw new RuntimeException('Invalid configuration.');
    }
    $dsn = 'mysql:host=' . $config['host'] . ';port=' . $config['port']
         . ';dbname=' . $config['database'] . ';charset=utf8mb4';
    return new PDO($dsn, $config['username'], $config['password'], [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]);
}

PDO is an object-based database interface. new PDO opens a connection using the DSN, username, password and options. The function returns that connection for a page to use.

The DSN names the driver, server address, port, database and utf8mb4 character set. Use the correct text encoding for the connection and HTML output; encoding does not replace validation or parameter binding.

07 | UNDERSTAND THE OPTIONS

ERRMODE_EXCEPTION makes database failures raise PDOException so the page can handle them. FETCH_ASSOC returns rows with column-name keys, similar to the associative arrays you already know.

EMULATE_PREPARES=false asks the MySQL driver to use native prepared statements for supported queries. The following lesson explains why values belong in parameters.

PDO does not make SQL identical across products. Connection strings, schema syntax and supported features still depend on the database.

08 | REQUEST A COMPLETE CONNECTION CHECK

All pages share public/helpers.php for HTML text and ordinary quoted-attribute output.

public/helpers.php

<?php
function escapeHtml(string $text): string
{
    return htmlspecialchars($text, ENT_QUOTES, 'UTF-8');
}

public/connection.php

<?php
require_once __DIR__ . '/helpers.php';
require_once dirname(__DIR__) . '/private/database.php';
$message = '';
$rows = [];
try {
    $pdo = openDatabase();
    $rows = $pdo->query('SELECT club_id, club_name, capacity FROM Clubs ORDER BY club_id')->fetchAll();
    $message = 'Connection and table read succeeded.';
} catch (PDOException | RuntimeException $exception) {
    error_log($exception->getMessage());
    http_response_code(500);
    $message = 'Database unavailable. Check the local setup and server log.';
}
?>
<!DOCTYPE html>
<html lang="en">
<head><meta charset="UTF-8"><title>PDO connection check</title></head>
<body>
<h1>PDO connection check</h1>
<p><?= escapeHtml($message) ?></p>
<ul>
    <?php foreach ($rows as $club): ?>
        <li><?= escapeHtml($club['club_name']) ?>: <?= (int) $club['capacity'] ?> places</li>
    <?php endforeach; ?>
</ul>
</body>
</html>

Open http://127.0.0.1:8000/connection.php. Expect a connection-and-read message and the three seeded clubs. query is appropriate here because the SQL is a fixed string with no supplied values.

fetchAll returns an array of result rows. The foreach outputs each club’s name as escaped text and the known numeric capacity as an integer.

09 | TROUBLESHOOT THE CORRECT LAYER

The public response avoids printing the raw exception, which can reveal internal details. The server log is for the developer. Never echo the configuration to debug a connection.

10 | INDEPENDENT PRACTICE

Next, send a supplied search value through a prepared statement rather than constructing SQL text from it.

KEEP LEARNING

Previous page 03.3 Prepared statements and safe database queries

Official references: JSON decoding · PDO connections · Prepared statements · MariaDB grants