PHP From ZeroModern PHP course

databases storage and caching

PDO vs MySQLi

For new general PHP learning, PDO is usually the better default. For maintaining older MySQL-only projects, MySQLi may be the API already in place.

Quick Comparison

PHP example
<?php

declare(strict_types=1);

$drivers = [
    'PDO' => 'Common interface for several database drivers',
    'MySQLi' => 'MySQL and MariaDB only',
];

foreach ($drivers as $name => $description) {
    echo "{$name}: {$description}" . PHP_EOL;
}

// Prints:
// PDO: Common interface for several database drivers
// MySQLi: MySQL and MariaDB only

PDO Strengths

PDO is a good fit when:

  • The course, team, or project wants one database API.
  • SQLite is used for examples or tests.
  • The application may use MySQL, PostgreSQL, or SQLite.
  • The codebase values named parameters.
  • Framework or library code already expects PDO.
PHP example
<?php

declare(strict_types=1);

$pdoSql = 'SELECT id, email FROM users WHERE email = :email';
$pdoParams = ['email' => 'sam@example.com'];

echo $pdoSql . PHP_EOL;
echo json_encode($pdoParams, JSON_THROW_ON_ERROR) . PHP_EOL;

// Prints:
// SELECT id, email FROM users WHERE email = :email
// {"email":"sam@example.com"}

MySQLi Strengths

MySQLi is a good fit when:

  • The project is definitely MySQL or MariaDB only.
  • Existing code already uses MySQLi consistently.
  • The team needs MySQL-specific APIs exposed by MySQLi.
  • Rewriting to PDO would be unrelated risk.
PHP example
<?php

declare(strict_types=1);

$mysqliSql = 'SELECT id, email FROM users WHERE email = ?';
$bindTypes = 's';

echo $mysqliSql . PHP_EOL;
echo 'bind types=' . $bindTypes . PHP_EOL;

// Prints:
// SELECT id, email FROM users WHERE email = ?
// bind types=s

Practical Decision

Do not mix PDO and MySQLi casually inside the same application layer. It creates duplicated connection handling, error handling, testing patterns, and helper code.

Use the project standard unless there is a strong reason to change. If starting a small learning project, use PDO. If hired into an older MySQLi project, learn enough MySQLi to maintain it safely.

What To Check

Before moving on, make sure you can:

  • Explain that PDO is multi-driver and MySQLi is MySQL-specific.
  • Use prepared statements in either API.
  • Recognise PDO named parameters and MySQLi ? placeholders.
  • Avoid mixing APIs without a reason.
  • Choose PDO for general learning and portability.
  • Maintain MySQLi safely in legacy MySQL/MariaDB codebases.

Practice

Practice: Choose PDO Or MySQLi

Write a short PHP decision helper that recommends PDO or MySQLi for a project scenario.

Requirements

  • Recommend PDO for general learning, SQLite examples, or multiple database engines.
  • Recommend MySQLi for an existing MySQL-only codebase that already uses it.
  • Show a PDO prepared statement shape.
  • Show a MySQLi prepared statement shape.
  • Explain why mixing both APIs should be avoided.
Show solution

This solution turns the comparison into a practical project decision.

PHP example
<?php

declare(strict_types=1);

function recommendDatabaseApi(array $scenario): array
{
    if (($scenario['existing_mysqli_codebase'] ?? false) === true
        && ($scenario['mysql_only'] ?? false) === true) {
        return [
            'api' => 'MySQLi',
            'reason' => 'The project is already MySQL-only and consistently uses MySQLi.',
        ];
    }

    if (($scenario['uses_sqlite_examples'] ?? false) === true
        || ($scenario['multiple_database_engines'] ?? false) === true
        || ($scenario['general_learning'] ?? false) === true) {
        return [
            'api' => 'PDO',
            'reason' => 'PDO gives one prepared-statement API across supported database drivers.',
        ];
    }

    return [
        'api' => 'PDO',
        'reason' => 'PDO is the broader default unless an existing MySQLi codebase sets the standard.',
    ];
}

$pdoShape = [
    'sql' => 'SELECT id, email FROM users WHERE email = :email',
    'parameters' => ['email' => 'sam@example.com'],
];

$mysqliShape = [
    'sql' => 'SELECT id, email FROM users WHERE email = ?',
    'bind_types' => 's',
    'values' => ['sam@example.com'],
];

$examples = [
    recommendDatabaseApi(['general_learning' => true, 'uses_sqlite_examples' => true]),
    recommendDatabaseApi(['existing_mysqli_codebase' => true, 'mysql_only' => true]),
];

foreach ($examples as $example) {
    echo $example['api'] . ': ' . $example['reason'] . PHP_EOL;
}

echo json_encode($pdoShape, JSON_THROW_ON_ERROR) . PHP_EOL;
echo json_encode($mysqliShape, JSON_THROW_ON_ERROR) . PHP_EOL;

// Prints:
// PDO: PDO gives one prepared-statement API across supported database drivers.
// MySQLi: The project is already MySQL-only and consistently uses MySQLi.
// {"sql":"SELECT id, email FROM users WHERE email = :email","parameters":{"email":"sam@example.com"}}
// {"sql":"SELECT id, email FROM users WHERE email = ?","bind_types":"s","values":["sam@example.com"]}

Avoid mixing both APIs inside the same application layer because it duplicates connection setup, error handling, transaction handling, and test patterns.