iwantcoding.com
🔥 Daily 👥 Rooms 🏆 Top Log in Sign up

MySQLi Functions

By · Reviewed & last updated · Part of PHP Tutorial

Quick mysqli reference for legacy code or one-off scripts. For new code, PDO is the recommended choice.

Connect

PHP
$mysqli = new mysqli('localhost', 'user', 'pass', 'shop');
if ($mysqli->connect_error) {
    die($mysqli->connect_error);
}
$mysqli->set_charset('utf8mb4');

Query (no parameters)

PHP
$result = $mysqli->query('SELECT id, name FROM customers');

while ($row = $result->fetch_assoc()) {
    echo $row['name'], PHP_EOL;
}

$result->free();

Prepared (with parameters)

PHP
$stmt = $mysqli->prepare('SELECT id, name FROM customers WHERE country = ?');
$stmt->bind_param('s', $country);    // 's' = string

$country = 'AU';
$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    echo $row['name'], PHP_EOL;
}
$stmt->close();

bind_param type chars

CharType
iInteger
dDouble / float
sString
bBlob — sent in packets

Insert + last id

PHP
$stmt = $mysqli->prepare('INSERT INTO customers (name, email) VALUES (?, ?)');
$stmt->bind_param('ss', $name, $email);

$name  = 'Ada';
$email = 'ada@example.com';
$stmt->execute();

echo $mysqli->insert_id;        // new id
echo $stmt->affected_rows;       // 1
$stmt->close();

Transactions

PHP
$mysqli->begin_transaction();
try {
    $mysqli->query('UPDATE accounts SET balance = balance - 100 WHERE id = 1');
    $mysqli->query('UPDATE accounts SET balance = balance + 100 WHERE id = 2');
    $mysqli->commit();
} catch (Throwable $e) {
    $mysqli->rollback();
    throw $e;
}

Useful properties / methods

NameWhat it returns
$mysqli->insert_idLast AUTO_INCREMENT id.
$mysqli->affected_rowsRows touched by last query.
$mysqli->errno / ->errorLast error.
$mysqli->real_escape_string($s)Escape for SQL — last resort.
$mysqli->close()Close connection.
Tip: Enable exceptions globally with mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); — much easier than checking return values everywhere.

Example

Example
<?php
// $mysqli = new mysqli('localhost', 'u', 'p', 'shop');
// $res = $mysqli->query('SELECT * FROM customers');
// while ($row = $res->fetch_assoc()) print_r($row);
echo 'mysqli is the procedural-friendly driver. PDO is the OO one.';
Try it Yourself »

Exercise

Enable mysqli exceptions globally.

(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

Test yourself

Q1. bind_param type for string is…
Q2. Enable mysqli exceptions globally with…
Q3. Last auto-id is…

Discussion

Loading…