Ebooks

PHP mysqli tutorial

PHP mysqli tutorial shows how to program MySQL in PHP with mysqli extension.

PHP tutorial is a comprehensive tutorial on PHP. PHP PDO tutorial shows how to program databases in PHP with PDO.

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.

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.

There are also ORM solutions for working with MySQL in PHP such as Doctrine or Eloquent.

PHP mysqli version

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

version.php
<?php

$con = new mysqli("localhost", "dbuser", "s$cret", "mydb");

if ($con->connect_errno) {

    printf("connection failed: %s\n", $con->connect_error());
    exit();
}

$res = $con->query("SELECT VERSION()");

if ($res) {

    $row = $res->fetch_row();
    echo $row[0];
}

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

The example prints the version of MySQL.

$con = new mysqli("localhost", "root", "andrea", "mydb");

A connection to the database is created. The mysqli class takes the hostname, username, password, and database name as arguments.

if ($con->connect_errno) {

    printf("connection failed: %s\n", $con->connect_error());
    exit();
}

The connect_errno contains the error code value if the connection attempt failed. It has zero if no error occurred. The connect_error() method returns the string description of the last connect error.

$res = $con->query("SELECT VERSION()");

The query() method performs a query on the database. The SELECT VERSION() statement returns the version of MySQL.

if ($res) {

    $row = $res->fetch_row();
    echo $row[0];
}

The fetch_row() returns a result row as an enumerated array. Our result contains only one value.

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

In the end, we release the resources.

$ php version.php
10.1.36-MariaDB

This is a sample output.

The mysqli driver also supports procedural style of programming.

version2.php
<?php

$con = mysqli_connect("localhost", "dbuser", "s$cret", "mydb");

if (mysqli_connect_errno()) {

    printf("connection failed: %s\n", mysqli_connect_error());
    exit();
}

$query = "SELECT VERSION()";

$res = mysqli_query($con, $query);

if ($res) {

    $row = mysqli_fetch_row($res);
    echo $row[0];
}

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

The example returns the version of MySQL with procedural functions.

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

$host = "localhost";
$user = "dbuser";
$passwd = "s$cret";
$db = "mydb";

function execute_query($query, $con)
{
    $res = $con->query($query);

    if (!$res) {

        echo "failed to execute query: $query\n";
    } else {
        echo "Query: $query executed\n";
    }

    if (is_object($res)) {

        $res->close();
    }
}

$con = new mysqli($host, $user, $passwd, $db);

if ($con->connect_errno) {

    printf("connection failed: %s\n", $con->connect_error());
    exit();
}

$query = "DROP TABLE IF EXISTS cars";
execute_query($query, $con);

$query = "CREATE TABLE cars(id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255), price INT)";
execute_query($query, $con);

$query = "INSERT INTO cars(name, price) VALUES('Audi', 52642)";
execute_query($query, $con);

$query = "INSERT INTO cars(name, price) VALUES('Mercedes', 57127)";
execute_query($query, $con);

$query = "INSERT INTO cars(name, price) VALUES('Skoda', 9000)";
execute_query($query, $con);

$query = "INSERT INTO cars(name, price) VALUES('Volvo', 29000)";
execute_query($query, $con);

$query = "INSERT INTO cars(name, price) VALUES('Bentley', 350000)";
execute_query($query, $con);

$query = "INSERT INTO cars(name, price) VALUES('Citroen', 21000)";
execute_query($query, $con);

$query = "INSERT INTO cars(name, price) VALUES('Hummer', 41400)";
execute_query($query, $con);

$query = "INSERT INTO cars(name, price) VALUES('Volkswagen', 21600)";
execute_query($query, $con);

$con->close();

The example creates the cars table with eight rows.

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.

prepared_statement.php
<?php

$host = "localhost";
$user = "dbuser";
$passwd = "s$cret";
$db = "mydb";

$con = new mysqli($host, $user, $passwd, $db);

if ($con->connect_errno) {

    printf("connection failed: %s\n", $con->connect_error());
    exit();
}

$id = 3;

$query = "SELECT id, name, price FROM cars WHERE id = ?";

if ($stmt = $con->prepare($query)) {

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

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

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

    $stmt->close();
} else {

    echo "failed to fetch data\n";
}

$con->close();

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

$query = "SELECT id, name, price FROM cars WHERE id = ?";

When we write prepared statements, we use placeholders instead of directly writing the values into the statements. Prepared statements are faster and guard against SQL injection attacks. The ? is a placeholder, which will be filled later. 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 type; it is integer in our case.

$stmt->execute();

The statement is executed.

$stmt->bind_result($row_id, $name, $price);

The bind_result() binds the returned values to the specified variables.

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

We print the variables to the terminal.

$con->close();

We close the statement.

$ php prepared_statement.php
3 Skoda 9000

This is the output.

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_rows.php
<?php

$host = "localhost";
$user = "dbuser";
$passwd = "s$cret";
$db = "mydb";

$con = new mysqli($host, $user, $passwd, $db);

if ($con->connect_errno) {

    printf("connection failed: %s\n", $con->connect_error());
    exit();
}

$query = "SELECT * FROM cars";

if ($res = $con->query($query)) {

    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->close();
} else {

    echo "failed to fetch data\n";
}

$con->close();

The example returns all rows from the cars table.

$query = "SELECT * FROM cars";

This SELECT query selects all rows from the table.

if ($res = $con->query($query)) {

We execute the SELECT query with the query() method.

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

The number of returned rows is stored in the num_rows attribute.

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

With the fetch_row() in a while loop, we fetch all rows from the table.

$ php fetch_rows.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

This is the output.

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 or NULL if there are no more rows in result set.

fetch_rows2.php
<?php

$host = "localhost";
$user = "dbuser";
$passwd = "s$cret";
$db = "mydb";

$con = new mysqli($host, $user, $passwd, $db);

if ($con->connect_errno) {

    printf("connection failed: %s\n", $con->connect_error());
    exit();
}

$query = "SELECT * FROM cars";

if ($res = $con->query($query)) {

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

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

    $res->close();
} else {

    echo "failed to fetch data\n";
}

$con->close();

The example returns all rows from the cars table.

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

When we use fetch_assoc(), we refer to the columns via array notation.

PHP mysqli fetch_object

The fetch_object() returns an object with string properties that correspond to the fetched row or NULL if there are no more rows in resultset.

fetch_rows3.php
<?php

$host = "localhost";
$user = "dbuser";
$passwd = "s$cret";
$db = "mydb";

$con = new mysqli($host, $user, $passwd, $db);

if ($con->connect_errno) {

    printf("connection failed: %s\n", $con->connect_error());
    exit();
}

$query = "SELECT * FROM cars";

if ($res = $con->query($query)) {

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

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

    $res->close();
} else {

    echo "failed to fetch data\n";
}

$con->close();

The example returns all rows from the cars table.

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

When we use fetch_object(), we refer to the columns via object access notation.

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

$host = "localhost";
$user = "dbuser";
$passwd = "s$cret";
$db = "mydb";

$con = new mysqli($host, $user, $passwd, $db);

if ($con->connect_errno) {

    printf("connection failed: %s\n", $con->connect_error());
    exit();
}

$query = "SELECT * FROM cars";

if ($res = $con->query($query)) {

    $num_rows = $res->num_rows;
    $num_fields = $res->field_count;

    printf("Select query returned %d rows.\n", $num_rows);
    printf("Select query returned %d columns.\n", $num_fields);

    $fields = $res->fetch_fields();

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

        for ($i = 0; $i < $num_fields; $i++) {

            echo $fields[$i]->name . ": " . $row[$i] . "\n";
        }

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

    $res->close();
} else {

    echo "failed to fetch data\n";
}

$con->close();

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

$num_rows = $res->num_rows;
$num_fields = $res->field_count;

The num_rows attribute 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. These are the column names.

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

    for ($i = 0; $i < $num_fields; $i++) {

        echo $fields[$i]->name . ": " . $row[$i] . "\n";
    }

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

We show the column names and the data.

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

This is the output.

This was PHP mysqli tutorial. List all PHP tutorials.