databases storage and caching

MySQLi Overview

MySQLi is PHP's MySQL-specific database extension. It can connect to MySQL or MariaDB, run prepared statements, fetch rows, and use transactions.

PDO is usually the broader default for new teaching because it supports multiple database engines through one API. MySQLi still matters because many older PHP projects and tutorials use it, and junior developers may need to maintain that code.

Connection Shape

PHP example

<?php

declare(strict_types=1);

$host = '127.0.0.1';
$database = 'app';
$user = 'app_user';

echo "mysqli connects to {$host} for database {$database} as {$user}" . PHP_EOL;

// Prints:
// mysqli connects to 127.0.0.1 for database app as app_user

In real code, a MySQLi connection may be created with new mysqli($host, $user, $password, $database). Credentials should come from configuration, not hard-coded source files.

Prepared Statement Shape

MySQLi prepared statements bind variables by type. The type string uses letters such as s for string and i for integer.

PHP example

<?php

declare(strict_types=1);

$sql = 'SELECT id, email FROM users WHERE email = ? LIMIT 1';
$types = 's';
$email = 'sam@example.com';

echo $sql . PHP_EOL;
echo 'bind types=' . $types . ', email=' . $email . PHP_EOL;

// Prints:
// SELECT id, email FROM users WHERE email = ? LIMIT 1
// bind types=s, email=sam@example.com

Conceptually, real MySQLi code prepares the SQL, binds variables, executes, and fetches results. Do not concatenate user values into SQL.

Procedural And Object Styles

MySQLi supports object-oriented and procedural styles. Mixing both in one file makes code harder to read.

Object style:

PHP example

<?php

declare(strict_types=1);

$style = '$mysqli->prepare($sql)';

echo $style . PHP_EOL;

// Prints:
// $mysqli->prepare($sql)

Procedural style uses functions such as mysqli_prepare($connection, $sql). Choose the style used by the existing codebase when maintaining old projects.

Transactions And Errors

MySQLi can use transactions with begin_transaction(), commit(), and rollback(). Enable exception reporting in modern code so errors are not silently ignored.

PHP example

<?php

declare(strict_types=1);

$steps = ['begin_transaction', 'execute statements', 'commit or rollback'];

echo implode(' -> ', $steps) . PHP_EOL;

// Prints:
// begin_transaction -> execute statements -> commit or rollback

What To Check

Before moving on, make sure you can:

Explain that MySQLi is MySQL/MariaDB-specific.
Recognise object and procedural styles.
Understand MySQLi prepared statement type strings.
Use prepared statements rather than concatenation.
Know that PDO is usually more portable.
Follow the style already used in a legacy codebase.

Practice

Practice: Recognise MySQLi Shapes

Write a short PHP example that models a MySQLi prepared statement flow.

Requirements

Show connection values without hard-coding a password.
Show SQL with ? placeholders.
Show a MySQLi bind type string.
Explain what s and i mean.
Include transaction step names.
Mention that MySQLi is MySQL/MariaDB-specific.

Show solution

This example is intentionally connection-free so it can run anywhere while still showing the MySQLi concepts.

PHP example

<?php

declare(strict_types=1);

$connection = [
    'host' => '127.0.0.1',
    'database' => 'app',
    'user' => 'app_user',
    'password_source' => 'environment',
];

$sql = 'SELECT id, email FROM users WHERE email = ? AND status = ? LIMIT 1';
$bindTypes = 'ss';
$transactionSteps = ['begin_transaction', 'execute prepared statements', 'commit or rollback'];

echo $connection['host'] . '/' . $connection['database'] . PHP_EOL;
echo $sql . PHP_EOL;
echo 'bind types=' . $bindTypes . PHP_EOL;
echo implode(' -> ', $transactionSteps) . PHP_EOL;

// Prints:
// 127.0.0.1/app
// SELECT id, email FROM users WHERE email = ? AND status = ? LIMIT 1
// bind types=ss
// begin_transaction -> execute prepared statements -> commit or rollback

In a MySQLi type string, s means string and i means integer. MySQLi is useful when maintaining MySQL/MariaDB-specific code, while PDO is usually the more portable default.