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.
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.
<?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.
<?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.
<?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.
<?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.
<?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.
<?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.
<?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.
<?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.
<?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.
<?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.
<?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.
<?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.
<?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.
<?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
List all PHP tutorials.