🔌 PHP and MySQL Integration

How PHP Talks to MySQL

PHP is the scripting layer that generates dynamic pages; MySQL is where the data lives. On every request, your PHP code opens a connection to the MySQL server, sends one or more SQL queries, receives a result set, and turns those rows into HTML, JSON, or whatever the page needs.

💡 The restaurant analogy. MySQL is the kitchen and pantry, where all the ingredients (data) are stored and prepared. PHP is the waitstaff: it takes the customer's request, carries the "order" (an SQL query) to the kitchen, and brings the finished plate (the result set) back to the table. The database connection is the service door between dining room and kitchen, and the connection credentials are the staff badge that unlocks it.

MySQLi vs PDO

Both are safe, modern, and support prepared statements. The choice usually comes down to whether you might switch databases later.

Confirm the extensions are loaded

<?php
// A quick self-check you can drop into any script
echo extension_loaded('mysqli')    ? "MySQLi ready\n"    : "MySQLi missing\n";
echo extension_loaded('pdo_mysql') ? "PDO MySQL ready\n" : "PDO MySQL missing\n";

On Ubuntu, install both with sudo apt install php-mysql then restart your web server. On Windows stacks (XAMPP/WAMP) uncomment extension=mysqli and extension=pdo_mysql in php.ini.

Opening a Connection

PDO (recommended)

<?php
$host = 'localhost';
$db   = 'myapp_db';
$user = 'webuser';
$pass = 'ChangeMe_Str0ng!';
$dsn  = "mysql:host=$host;dbname=$db;charset=utf8mb4";

// Sensible defaults for every connection
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,  // throw on error
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,        // associative rows
    PDO::ATTR_EMULATE_PREPARES   => false,                   // use real prepared statements
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    echo "Connected with PDO";
} catch (PDOException $e) {
    // Log the detail, show the user nothing sensitive
    error_log($e->getMessage());
    exit('Database connection failed.');
}

MySQLi (object-oriented)

<?php
// Make MySQLi throw exceptions instead of warnings (default since PHP 8.1)
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    $mysqli = new mysqli('localhost', 'webuser', 'ChangeMe_Str0ng!', 'myapp_db');
    $mysqli->set_charset('utf8mb4');
    echo "Connected with MySQLi";
} catch (mysqli_sql_exception $e) {
    error_log($e->getMessage());
    exit('Database connection failed.');
}

Running Queries & Fetching Results

For a query with no user input, you can use the simple query() method. (The moment any value comes from a user, switch to prepared statements — the whole focus of the next lesson.)

PDO — fetch row by row

<?php
$stmt = $pdo->query('SELECT id, username, email FROM users LIMIT 5');

foreach ($stmt as $row) {           // PDOStatement is iterable
    echo "{$row['username']} — {$row['email']}\n";
}

PDO — fetch everything at once

<?php
$users = $pdo->query('SELECT id, username FROM users')->fetchAll();

foreach ($users as $user) {
    echo $user['username'] . "\n";
}

MySQLi — object-oriented fetch

<?php
$result = $mysqli->query('SELECT id, username, email FROM users LIMIT 5');

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "{$row['username']} — {$row['email']}\n";
    }
}
$result->free();   // release the result set

Insert, Update & Delete

The four data operations — Create, Read, Update, Delete — are the backbone of nearly every application. Here they are with PDO prepared statements, which safely bind user data into the query.

<?php
// CREATE — insert and get the new id
$sql = 'INSERT INTO users (username, email, password_hash, created_at)
        VALUES (:username, :email, :password, NOW())';
$stmt = $pdo->prepare($sql);
$stmt->execute([
    ':username' => 'johndoe',
    ':email'    => 'john@example.com',
    ':password' => password_hash('secret123', PASSWORD_DEFAULT),
]);
$newId = $pdo->lastInsertId();
echo "Created user #$newId\n";

// READ — one record
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = :id');
$stmt->execute([':id' => $newId]);
$user = $stmt->fetch();
echo "{$user['username']} ({$user['email']})\n";

// UPDATE — rowCount() reports how many rows changed
$stmt = $pdo->prepare('UPDATE users SET email = :email WHERE id = :id');
$stmt->execute([':email' => 'john.doe@example.com', ':id' => $newId]);
echo "Updated {$stmt->rowCount()} row(s)\n";

// DELETE
$stmt = $pdo->prepare('DELETE FROM users WHERE id = :id');
$stmt->execute([':id' => $newId]);
echo "Deleted {$stmt->rowCount()} row(s)\n";

Created user #42
johndoe (john@example.com)
Updated 1 row(s)
Deleted 1 row(s)

Centralizing & Securing Connections

Copy-pasting connection code into every file is a maintenance and security nightmare. Instead, wrap it in one class and load credentials from outside your source code.

A reusable database class

<?php
// Database.php
class Database
{
    private ?PDO $conn = null;

    public function connect(): PDO
    {
        if ($this->conn !== null) {
            return $this->conn;          // reuse an open connection
        }

        // Credentials come from the environment, not the code
        $host = getenv('DB_HOST') ?: 'localhost';
        $name = getenv('DB_NAME');
        $user = getenv('DB_USER');
        $pass = getenv('DB_PASS');

        $dsn = "mysql:host=$host;dbname=$name;charset=utf8mb4";

        $this->conn = new PDO($dsn, $user, $pass, [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES   => false,
        ]);

        return $this->conn;
    }
}

Using it

<?php
require 'Database.php';

$pdo = (new Database())->connect();
$stmt = $pdo->query('SELECT username FROM users LIMIT 5');
foreach ($stmt as $row) {
    echo $row['username'] . "\n";
}

Keep secrets out of the repo

Store credentials in a .env file that is listed in .gitignore, and load it with the popular vlucas/phpdotenv package:

# .env  (NEVER commit this file)
DB_HOST=localhost
DB_NAME=myapp_db
DB_USER=webuser
DB_PASS=ChangeMe_Str0ng!

<?php
require 'vendor/autoload.php';
$dotenv = Dotenv\Dotenv::createImmutable(__DIR__);
$dotenv->load();   // now getenv('DB_PASS') and $_ENV['DB_PASS'] work

Handling Errors

With PDO::ERRMODE_EXCEPTION (or MySQLi's report mode) set, any database failure throws an exception you can catch. The golden rule: log the detail, show the user nothing sensitive.

<?php
try {
    $stmt = $pdo->prepare('SELECT * FROM users WHERE id = :id');
    $stmt->execute([':id' => $id]);
    $user = $stmt->fetch();

    if (!$user) {
        echo 'User not found.';
    }
} catch (PDOException $e) {
    // Full detail goes to the server log
    error_log('DB error: ' . $e->getMessage() . ' in ' . $e->getFile() . ':' . $e->getLine());

    // The user gets a friendly, generic message
    http_response_code(500);
    echo 'Sorry, a system error occurred. Please try again later.';
}

Hands-on Exercise

<?php
require 'Database.php';   // the class from the lesson

try {
    $pdo = (new Database())->connect();

    // CREATE
    $insert = $pdo->prepare(
        'INSERT INTO users (username, email, password_hash, created_at)
         VALUES (:u, :e, :p, NOW())'
    );
    foreach ([['ada','ada@ex.com'], ['grace','grace@ex.com']] as [$u, $e]) {
        $insert->execute([
            ':u' => $u,
            ':e' => $e,
            ':p' => password_hash('secret123', PASSWORD_DEFAULT),
        ]);
        echo "Inserted #{$pdo->lastInsertId()}\n";
    }

    // READ
    foreach ($pdo->query('SELECT id, username, email FROM users') as $row) {
        echo "{$row['id']}: {$row['username']} ({$row['email']})\n";
    }

    // UPDATE
    $upd = $pdo->prepare('UPDATE users SET email = :e WHERE username = :u');
    $upd->execute([':e' => 'ada.lovelace@ex.com', ':u' => 'ada']);
    echo "Updated {$upd->rowCount()} row(s)\n";

    // DELETE
    $del = $pdo->prepare('DELETE FROM users WHERE username = :u');
    $del->execute([':u' => 'grace']);
    echo "Deleted {$del->rowCount()} row(s)\n";

} catch (PDOException $e) {
    error_log($e->getMessage());
    echo 'A database error occurred.';
}

Summary & Quiz

📚 Further Reading

• PHP Manual — PDO
• PHP Manual — MySQLi
• PHP The Right Way — Databases

🚀 What's Next?

You can now connect and run CRUD from PHP. But the moment user input touches your queries, you face the web's most infamous vulnerability. Next, Prepared Statements and Security shows you how to defeat SQL injection and build a hardened data layer.