MySQLi

Besides PDO, PHP has a second database extension — MySQLi. It works only with MySQL and MariaDB, but almost everyone has met it: a huge number of existing sites are written with it. You need to understand both ways to read other people's code.

The examples in this lesson need a MySQL server, so they cannot be run here.

The old mysql_ functions

Old tutorials mention mysql_connect() and mysql_query(). These functions no longer exist: they were removed from PHP in version 7. Their modern replacement is MySQLi, where the "i" stands for "improved".

Connecting

The mysqli object takes the server address, the user name, the password and the database name:

<?php
 
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
 
$db = new mysqli('localhost', 'root', 'secret', 'shop');
$db->set_charset('utf8mb4');

The first line turns on exceptions for errors. The utf8mb4 encoding is needed to store non-English text and emoji correctly.

A simple query

The query() method runs a query without parameters. The rows of the result are read with fetch_assoc():

<?php
 
$db = new mysqli('localhost', 'root', 'secret', 'shop');
 
$result = $db->query('SELECT id, title FROM products ORDER BY id');
 
while ($row = $result->fetch_assoc()) {
    echo $row['id'], ': ', $row['title'], "\n";
}

fetch_all(MYSQLI_ASSOC) returns all the rows at once as an array.

Prepared statements

User data is passed only through a prepared statement. In MySQLi the values are given types: s is a string, i an integer, d a float:

<?php
 
$db = new mysqli('localhost', 'root', 'secret', 'shop');
 
$statement = $db->prepare('SELECT title FROM products WHERE price < ? AND category = ?');
$statement->bind_param('is', $maxPrice, $category);
 
$maxPrice = 300;
$category = 'drinks';
$statement->execute();
 
$rows = $statement->get_result()->fetch_all(MYSQLI_ASSOC);

Since PHP 8.1 the values can be passed straight to execute(), as in PDO: $statement->execute([300, 'drinks']).

Adding a record

<?php
 
$db = new mysqli('localhost', 'root', 'secret', 'shop');
 
$statement = $db->prepare('INSERT INTO products (title, price) VALUES (?, ?)');
$statement->execute(['Tea', 250]);
 
echo $db->insert_id;       // the identifier of the new row
echo $db->affected_rows;   // the number of affected rows

The procedural style

Every MySQLi method has a twin function. This style is most common in older code:

<?php
 
$db = mysqli_connect('localhost', 'root', 'secret', 'shop');
 
$result = mysqli_query($db, 'SELECT title FROM products');
 
while ($row = mysqli_fetch_assoc($result)) {
    echo $row['title'], "\n";
}
 
mysqli_close($db);

Both styles do the same thing; new code uses the object-oriented one.

MySQLi or PDO

A new project usually picks PDO: the code is not tied to one database, and the parameters are handier. MySQLi fits when a project is already written with it or needs MySQL-specific features.

Common mistake

The most dangerous habit from old code is putting variables straight into the query text. It opens the door to SQL injection:

<?php
 
$db = new mysqli('localhost', 'root', 'secret', 'shop');
$name = $_GET['name'] ?? '';
 
// Dangerous: $db->query("SELECT * FROM users WHERE name = '$name'");
 
$statement = $db->prepare('SELECT * FROM users WHERE name = ?');
$statement->execute([$name]);

The mysqli_real_escape_string() function, once used to "defuse" values, does not protect in every case. Use prepared statements.