ZetCode

PHP mysqli

last modified November 14, 2026

PHP mysqli tutorial shows how to program MySQL in PHP with the mysqli extension. The examples were run with PHP 8.5 and MySQL 8.4.

MySQL

MySQL is a leading open source database management system. It is a multiuser, multithreaded database management system. MySQL is especially popular on the web. It is one of the parts of the very popular LAMP platform: Linux, Apache, MySQL, and PHP. Currently MySQL is owned by Oracle. MySQL database is available on most important OS platforms. It runs on BSD Unix, Linux, Windows, or Mac OS. The MariaDB server is a compatible fork of MySQL; the examples work with it too.

PHP mysqli

The MySQLi Extension (MySQL Improved) is a relational database driver used in the PHP scripting language to provide an interface with MySQL databases. It provides both object oriented and procedural APIs. Other ways to interact with MySQL are: PDO and ORM solutions.

The pdo_mysql PHP Data Objects module is a database abstraction layer for PHP applications. This module is beneficial if we write portable database PHP scripts. The PHP PDO tutorial covers it. There are also ORM solutions for working with MySQL in PHP such as Doctrine or Eloquent.

On Debian and Ubuntu, the php-mysql package installs both the mysqli and pdo_mysql modules. It enables the extension in the PHP configuration. With the official Docker PHP image, the extension is installed with docker-php-ext-install mysqli.

$ sudo apt install php-mysql

Error reporting in mysqli

Since PHP 8.1, mysqli throws a mysqli_sql_exception on errors by default; the default report mode is MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT. In older versions, the functions returned false and the programmers had to check connect_errno or $con->error after every call. The examples below rely on exceptions and do not need such checks. The report mode can be changed with the mysqli_report function.

Database setup

We create the mydb database and the dbuser user with the passwd password. The user has all privileges on the mydb database only. The commands are run by the MySQL administrator.

setup.sql
CREATE DATABASE mydb CHARACTER SET utf8mb4;
CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'passwd';
GRANT ALL PRIVILEGES ON mydb.* TO 'dbuser'@'localhost';

The CREATE DATABASE statement creates the database; we use the utf8mb4 character set, which supports all Unicode characters. The CREATE USER statement creates the user, and GRANT allows it to work with the database. We run the script with the mysql command line tool and verify the privileges.

$ mysql -u root -p < setup.sql
$ mysql -u root -p -e "SHOW GRANTS FOR 'dbuser'@'localhost'"
Grants for dbuser@localhost
GRANT USAGE ON *.* TO `dbuser`@`localhost`
GRANT ALL PRIVILEGES ON `mydb`.* TO `dbuser`@`localhost`

PHP mysqli version

In the following example, we determine the version of the MySQL database.

version.php
<?php

declare(strict_types=1);

try {
    $con = new mysqli('localhost', 'dbuser', 'passwd', 'mydb');
} catch (mysqli_sql_exception $e) {
    echo 'Connection failed: ', $e->getMessage(), "\n";
    exit(1);
}

$res = $con->query('SELECT VERSION()');
$row = $res->fetch_row();

echo $row[0], "\n";

$res->free();
$con->close();

The example prints the version of MySQL.

$con = new mysqli('localhost', 'dbuser', 'passwd', 'mydb');

A connection to the database is created. The mysqli class takes the hostname, username, password, and database name as arguments. If the connection fails, a mysqli_sql_exception is thrown. With localhost, mysqli connects through a Unix socket; with an IP address such as 127.0.0.1, it uses TCP.

$res = $con->query('SELECT VERSION()');
$row = $res->fetch_row();

The query method performs a query on the database. The SELECT VERSION() statement returns the version of MySQL. The fetch_row returns a result row as an enumerated array. Our result contains only one value.

$res->free();
$con->close();

In the end, we release the resources.

$ php version.php
8.4.11

PHP mysqli procedural style

The mysqli driver also supports procedural style of programming. The functions have the mysqli_ prefix and take the connection or the result object as the first argument.

version_procedural.php
<?php

declare(strict_types=1);

$con = mysqli_connect('localhost', 'dbuser', 'passwd', 'mydb');

$res = mysqli_query($con, 'SELECT VERSION()');
$row = mysqli_fetch_row($res);

echo $row[0], "\n";

mysqli_free_result($res);
mysqli_close($con);

The example returns the version of MySQL with procedural functions. The rest of the tutorial uses the object-oriented style, which is more common in modern code.

$ php version_procedural.php
8.4.11

PHP mysqli exceptions

We can catch the mysqli_sql_exception to react to errors. It carries the MySQL error code and message.

connect_error.php
<?php

declare(strict_types=1);

try {
    $con = new mysqli('localhost', 'dbuser', 'wrong-password', 'mydb');
} catch (mysqli_sql_exception $e) {
    printf("Connection failed (%d): %s\n", $e->getCode(), $e->getMessage());
}

$con = new mysqli('localhost', 'dbuser', 'passwd', 'mydb');

try {
    $con->query('SELEC * FROM cars');
} catch (mysqli_sql_exception $e) {
    printf("Query failed (%d): %s\n", $e->getCode(), $e->getMessage());
}

$con->close();

The first connection uses a wrong password; the connection error 1045 is thrown from the constructor. The second error is a syntax error in the SQL statement: the code is 1064. In production code, we should log these messages and not show them to the users, because they can reveal details about the database.

$ php connect_error.php
Connection failed (1045): Access denied for user 'dbuser'@'localhost' (using password: YES)
Query failed (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELEC * FROM cars' at line 1

PHP mysqli create table

The following example creates a new database table. A table is created with the CREATE TABLE statement. Rows are added to the table with the INSERT INTO statements.

create_table.php
<?php

declare(strict_types=1);

$con = new mysqli('localhost', 'dbuser', 'passwd', 'mydb');

$con->query('DROP TABLE IF EXISTS cars');
$con->query('CREATE TABLE cars(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    price INT
) ENGINE=InnoDB');

$cars = [
    ['Audi', 52642], ['Mercedes', 57127], ['Skoda', 9000], ['Volvo', 29000],
    ['Bentley', 350000], ['Citroen', 21000], ['Hummer', 41400], ['Volkswagen', 21600],
];

$stmt = $con->prepare('INSERT INTO cars(name, price) VALUES (?, ?)');

foreach ($cars as [$name, $price]) {
    $stmt->execute([$name, $price]);
    echo "Inserted $name with id {$con->insert_id}, affected rows: {$stmt->affected_rows}\n";
}

$stmt->close();
$con->close();

The example creates the cars table with eight rows.

$stmt = $con->prepare('INSERT INTO cars(name, price) VALUES (?, ?)');

foreach ($cars as [$name, $price]) {
    $stmt->execute([$name, $price]);
    echo "Inserted $name with id {$con->insert_id}, affected rows: {$stmt->affected_rows}\n";
}

The statement is prepared once and executed for each car. Passing an array to execute (PHP 8.1 and later) binds the values to the placeholders; all of them are bound as strings, which MySQL converts to the column types. The insert_id property of the connection holds the value generated for the AUTO_INCREMENT column by the last insert, and affected_rows holds the number of inserted rows.

$ php create_table.php
Inserted Audi with id 1, affected rows: 1
Inserted Mercedes with id 2, affected rows: 1
Inserted Skoda with id 3, affected rows: 1
Inserted Volvo with id 4, affected rows: 1
Inserted Bentley with id 5, affected rows: 1
Inserted Citroen with id 6, affected rows: 1
Inserted Hummer with id 7, affected rows: 1
Inserted Volkswagen with id 8, affected rows: 1

PHP mysqli prepared statements

When we write prepared statements, we use placeholders instead of directly writing the values into the statements. Prepared statements increase security and performance. In mysqli, the prepare function prepares an SQL statement for execution.

bind_param and bind_result

The first example uses bind_param to bind the input and bind_result to bind the output columns to variables.

prepared_statement.php
<?php

declare(strict_types=1);

$con = new mysqli('localhost', 'dbuser', 'passwd', 'mydb');

$id = 3;

$stmt = $con->prepare('SELECT id, name, price FROM cars WHERE id = ?');
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->bind_result($rowId, $name, $price);
$stmt->fetch();

echo "$rowId $name $price\n";

$stmt->close();
$con->close();

The example selects a specific row from the table. It uses a prepared statement.

$stmt = $con->prepare('SELECT id, name, price FROM cars WHERE id = ?');

The ? is a placeholder, which will be filled later. Prepared statements guard against SQL injection attacks, because the values never become part of the SQL text. In our case we have one value: an integer id.

$stmt->bind_param('i', $id);

The value of the $id variable is bound to the placeholder with the bind_param method. The first parameter specifies the variable types: i is integer, d is double, s is string, and b is blob.

$stmt->execute();
$stmt->bind_result($rowId, $name, $price);
$stmt->fetch();

The statement is executed. The bind_result binds the returned columns to the specified variables, and fetch fills them with the values of the next row.

$ php prepared_statement.php
3 Skoda 9000

execute with an array and get_result

In modern code, it is shorter to pass the parameters to execute and to read the rows with get_result, which returns the same kind of result object as query. The execute_query method (PHP 8.2) does the whole sequence of prepare, bind, execute, and get result in one call.

prepared_result.php
<?php

declare(strict_types=1);

$con = new mysqli('localhost', 'dbuser', 'passwd', 'mydb');

$stmt = $con->prepare('SELECT id, name, price FROM cars WHERE price BETWEEN ? AND ? ORDER BY price');
$stmt->execute([20000, 60000]);

$res = $stmt->get_result();

while ($row = $res->fetch_assoc()) {
    printf("%d %-10s %6d\n", $row['id'], $row['name'], $row['price']);
}

echo "---\n";

$res = $con->execute_query('SELECT name FROM cars WHERE name LIKE ?', ['V%']);

foreach ($res as $row) {
    echo $row['name'], "\n";
}

$con->close();

The first query selects cars with a price in the given range. Both values in the array are bound as strings, and MySQL compares them as numbers. The second query uses execute_query with a LIKE pattern; the percent sign is part of the bound value, not of the SQL statement. The result object is iterable, so we can use foreach.

$ php prepared_result.php
6 Citroen     21000
8 Volkswagen  21600
4 Volvo       29000
7 Hummer      41400
1 Audi        52642
2 Mercedes    57127
---
Volvo
Volkswagen

PHP mysqli fetch_row

The fetch_row method fetches one row of data from the result set and returns it as an enumerated array. Each column is stored in an array offset starting from 0. Each subsequent call to this function will return the next row within the result set, or null if there are no more rows.

fetch_row.php
<?php

declare(strict_types=1);

$con = new mysqli('localhost', 'dbuser', 'passwd', 'mydb');

$res = $con->query('SELECT * FROM cars');

printf("Select query returned %d rows.\n", $res->num_rows);

while ($row = $res->fetch_row()) {
    printf("%s %s %s\n", $row[0], $row[1], $row[2]);
}

$res->free();
$con->close();

The example returns all rows from the cars table. The number of returned rows is stored in the num_rows property of the result. With fetch_row in a while loop, we fetch all rows from the table.

$ php fetch_row.php
Select query returned 8 rows.
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
6 Citroen 21000
7 Hummer 41400
8 Volkswagen 21600

PHP mysqli fetch_assoc

The fetch_assoc returns an associative array of strings representing the fetched row in the result set. Each key in the array represents the name of one of the result set's columns. It returns null if there are no more rows in the result set.

fetch_assoc.php
<?php

declare(strict_types=1);

$con = new mysqli('localhost', 'dbuser', 'passwd', 'mydb');

$res = $con->query('SELECT * FROM cars WHERE id <= 3');

while ($row = $res->fetch_assoc()) {
    printf("%s %s %s\n", $row['id'], $row['name'], $row['price']);
}

$res->free();
$con->close();

When we use fetch_assoc, we refer to the columns via array notation. The code does not depend on the order of the columns, which makes it more robust than fetch_row.

$ php fetch_assoc.php
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000

PHP mysqli fetch_object

The fetch_object returns an object with properties that correspond to the fetched row or null if there are no more rows in the result set. By default it creates stdClass objects; we can pass a class name to get objects of our own class.

fetch_object.php
<?php

declare(strict_types=1);

class Car
{
    public int $id;
    public string $name;
    public int $price;
}

$con = new mysqli('localhost', 'dbuser', 'passwd', 'mydb');

$res = $con->query('SELECT * FROM cars WHERE id <= 3');

while ($car = $res->fetch_object()) {
    printf("%s %s %s\n", $car->id, $car->name, $car->price);
}

$res = $con->query('SELECT * FROM cars WHERE id > 6');

while ($car = $res->fetch_object(Car::class)) {
    printf("%s: %s costs %d (%s)\n", $car::class, $car->name, $car->price, gettype($car->price));
}

$res->free();
$con->close();

The first loop refers to the columns via object access notation. In the second loop, fetch_object(Car::class) creates Car objects and sets their properties. The properties are typed, so the string values from the database are converted to integers.

$ php fetch_object.php
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
Car: Hummer costs 41400 (integer)
Car: Volkswagen costs 21600 (integer)

PHP mysqli fetch_all

The fetch_all method returns all rows as an array. With the MYSQLI_ASSOC mode, each row is an associative array. The fetch_column method (PHP 8.1) returns a single column of the next row, which is handy for aggregate queries.

fetch_all.php
<?php

declare(strict_types=1);

$con = new mysqli('localhost', 'dbuser', 'passwd', 'mydb');

$res = $con->query('SELECT name, price FROM cars ORDER BY price DESC LIMIT 3');
$rows = $res->fetch_all(MYSQLI_ASSOC);

print_r($rows);

$count = $con->query('SELECT COUNT(*) FROM cars')->fetch_column();
echo "There are $count cars\n";

$con->close();

The example fetches the three most expensive cars into an array and prints it. Then it reads the number of rows in the table with fetch_column. Use fetch_all only when the result is small; for large tables, fetch the rows one at a time.

$ php fetch_all.php
Array
(
    [0] => Array
        (
            [name] => Bentley
            [price] => 350000
        )

    [1] => Array
        (
            [name] => Mercedes
            [price] => 57127
        )

    [2] => Array
        (
            [name] => Audi
            [price] => 52642
        )

)
There are 8 cars

PHP mysqli data types

Rows returned by query use the text protocol, so all values are strings. Prepared statements use the binary protocol, and integer and float columns come back as PHP int and float.

types.php
<?php

declare(strict_types=1);

$con = new mysqli('localhost', 'dbuser', 'passwd', 'mydb');

$res = $con->query('SELECT id, price FROM cars WHERE id = 1');
var_dump($res->fetch_assoc());

$stmt = $con->prepare('SELECT id, price FROM cars WHERE id = ?');
$stmt->execute([1]);
var_dump($stmt->get_result()->fetch_assoc());

$con->close();

$con = mysqli_init();
$con->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, true);
$con->real_connect('localhost', 'dbuser', 'passwd', 'mydb');

$res = $con->query('SELECT id, price FROM cars WHERE id = 1');
var_dump($res->fetch_assoc());

$con->close();

The first var_dump prints strings, the second one integers. The third connection is created with mysqli_init and real_connect, so that we can set the MYSQLI_OPT_INT_AND_FLOAT_NATIVE option before connecting; with it, even the plain query returns native types.

$ php types.php
array(2) {
  ["id"]=>
  string(1) "1"
  ["price"]=>
  string(5) "52642"
}
array(2) {
  ["id"]=>
  int(1)
  ["price"]=>
  int(52642)
}
array(2) {
  ["id"]=>
  int(1)
  ["price"]=>
  int(52642)
}

PHP mysqli column names

The next example prints column names with the data from the database table. We refer to column names as meta data.

column_names.php
<?php

declare(strict_types=1);

$con = new mysqli('localhost', 'dbuser', 'passwd', 'mydb');

$res = $con->query('SELECT * FROM cars LIMIT 3');

printf("Select query returned %d rows.\n", $res->num_rows);
printf("Select query returned %d columns.\n", $res->field_count);

$fields = $res->fetch_fields();

while ($row = $res->fetch_row()) {

    foreach ($fields as $i => $field) {
        echo $field->name . ': ' . $row[$i] . "\n";
    }

    echo "*******************************\n";
}

$res->free();
$con->close();

The example prints three rows of the cars table with the column headers.

printf("Select query returned %d rows.\n", $res->num_rows);
printf("Select query returned %d columns.\n", $res->field_count);

The num_rows property returns the number of rows in the result. The field_count returns the number of fields in the result.

$fields = $res->fetch_fields();

The fetch_fields method returns an array of objects representing the fields in a result set. Each object has the name of the column and other properties such as the type and the maximum length.

$ php column_names.php
Select query returned 3 rows.
Select query returned 3 columns.
id: 1
name: Audi
price: 52642
*******************************
id: 2
name: Mercedes
price: 57127
*******************************
id: 3
name: Skoda
price: 9000
*******************************

PHP mysqli and SQL injection

SQL injection happens when user input becomes part of the SQL text. The example compares three ways of building a query with a malicious value.

injection.php
<?php

declare(strict_types=1);

$con = new mysqli('localhost', 'dbuser', 'passwd', 'mydb');

$input = "x' OR '1'='1";

$sql = "SELECT name FROM cars WHERE name = '$input'";
echo $sql, "\n";
echo 'Concatenated query returned ', $con->query($sql)->num_rows, " rows\n";

$escaped = $con->real_escape_string($input);
$sql = "SELECT name FROM cars WHERE name = '$escaped'";
echo $sql, "\n";
echo 'Escaped query returned ', $con->query($sql)->num_rows, " rows\n";

$res = $con->execute_query('SELECT name FROM cars WHERE name = ?', [$input]);
echo 'Prepared query returned ', $res->num_rows, " rows\n";

$con->close();

The value x' OR '1'='1 closes the string literal and adds a condition that is always true. When we concatenate it into the query, the query returns all rows. The real_escape_string method escapes the quotes, and the query returns nothing, but it depends on the connection character set and is easy to forget. A prepared statement keeps the SQL and the values separate and is the recommended solution.

$ php injection.php
SELECT name FROM cars WHERE name = 'x' OR '1'='1'
Concatenated query returned 8 rows
SELECT name FROM cars WHERE name = 'x\' OR \'1\'=\'1'
Escaped query returned 0 rows
Prepared query returned 0 rows

PHP mysqli transactions

A transaction is an atomic unit of database operations. The effects of all the SQL statements in a transaction can be either all committed to the database or all rolled back. Transactions need a transactional storage engine; the default MySQL engine InnoDB supports them.

transaction.php
<?php

declare(strict_types=1);

$con = new mysqli('localhost', 'dbuser', 'passwd', 'mydb');

function countCars(mysqli $con): int
{
    return (int) $con->query('SELECT COUNT(*) FROM cars')->fetch_column();
}

echo 'Cars before: ', countCars($con), "\n";

try {
    $con->begin_transaction();
    $con->query("INSERT INTO cars(name, price) VALUES ('Fiat', 8000)");
    $con->query("INSERT INTO cars(name, price) VALUES (NULL, 9000)");
    $con->commit();
} catch (mysqli_sql_exception $e) {
    $con->rollback();
    echo 'Rolled back: ', $e->getMessage(), "\n";
}

echo 'Cars after the failed transaction: ', countCars($con), "\n";

$con->begin_transaction();
$con->query("INSERT INTO cars(name, price) VALUES ('Fiat', 8000)");
$con->query("INSERT INTO cars(name, price) VALUES ('Seat', 9000)");
$con->commit();

echo 'Cars after the successful transaction: ', countCars($con), "\n";

$con->close();

The begin_transaction starts a transaction, commit makes the changes permanent and rollback reverts them. In the first transaction, the second insert violates the NOT NULL constraint of the name column. The exception is caught and we roll the transaction back; the first insert is reverted, and the table still has eight rows. The second transaction succeeds and adds two cars.

$ php transaction.php
Cars before: 8
Rolled back: Column 'name' cannot be null
Cars after the failed transaction: 8
Cars after the successful transaction: 10

Source

MySQL Improved Extension - PHP manual

mysqli report mode - PHP manual

This was PHP mysqli tutorial. We have connected to MySQL, handled errors with exceptions, executed queries and prepared statements, fetched data in several ways, and used transactions. See also the PHP PDO and PHP SQLite tutorials.

Author

I am Jan Bodnar, a programmer and technical writer. I have been writing programming tutorials at ZetCode since 2007 and have published eight e-books. See about the author.

List all PHP tutorials.