Skip to content

12. Using the MySQL DBMS

We will now write PHP scripts using a MySQL database:

Image

In the architecture above, the PHP script [1] does not communicate directly with the DBMS (Database Management System) [3]. Instead, it communicates with an intermediary known as the DBMS driver [2]. PHP provides a standard interface for these drivers, the [PDO] interface (PHP Data Objects). This interface is implemented by different drivers tailored to each DBMS: one driver for the MySQL DBMS, another for the PostgreSQL DBMS… To switch to a different DBMS, you switch drivers:

Image

The PDO driver isolates the PHP script [1] from the DBMS [3, 6]. Since these drivers implement a standard interface, we can expect that the PHP script [1] will remain unchanged when switching from the MySQL DBMS [3] to the PostgreSQL DBMS [6]. In reality, this ideal does not exist. In fact, to communicate with the DBMS, the PHP script sends SQL commands (Structured Query Language). This language is implemented by all DBMSs but is incomplete. Consequently, the DBMSs have added proprietary commands to it. This is a primary cause of incompatibility among DBMSs. Furthermore, the data types that can be used in databases may differ from one DBMS to another. For example, PostgreSQL supports a much wider range of data types than MySQL. This is a second cause of incompatibility. Another cause is the handling of automatic primary keys (generated by the DBMS): virtually every DBMS has its own policy. Etc… There are numerous causes of incompatibility.

If you want to avoid rewriting the PHP script [1] when transitioning from MySQL [3] to PostgreSQL [6], we generally need to insert a new layer between the PHP script [1] and the PDO drivers [2, 5], a layer whose purpose is to resolve incompatibilities between the two DBMSs. However, in the simple cases we’ll encounter, this additional layer won’t be necessary.

We will now use the MySQL DBMS. Its version 8.4 is included in the [Laragon] package that we installed at the beginning of this course.

If the reader is new to the concepts of databases and the SQL language, they can refer to the document [http://sergetahe.com/cours-tutoriels-de-programmation/cours-tutoriel-sql-avec-le-sgbd-firebird/]. This document uses the Firebird DBMS rather than MySQL, but it covers the fundamentals of databases and the SQL language. The MySQL 8.4 documentation is also available at [https://dev.mysql.com/doc/refman/8.4/en/].

The scripts for this chapter are in the [scripts-console/mysql] folder:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
mysql
├── creation.txt
├── dbpersons.sql
├── mysql-01.php
├── mysql-02.php
├── mysql-03.php
├── mysql-04.php
├── mysql-05.php
├── mysql-06.php
├── mysql-07.php
├── pdo-drivers.php
├── sql.txt
└── sql2.txt

12.1. Creating a Database

We will now show you how to create a database and a MySQL user using the [Laragon] tool.

Once launched, [Laragon] is managed from its main window and menu (right-click in the window or on its icon in the taskbar):

  • The [Start All] button starts the Apache web server and the MySQL DBMS. The [Laragon] window then indicates that each is active, along with its listening port: 80 for Apache, 3306 for MySQL;
  • the [Database] button launches the [HeidiSQL] tool, included with [Laragon], which allows you to manage MySQL databases graphically. You can also install the web tool [phpMyAdmin] (Tools / Quick Add menu), which is then accessible via URL and [http://localhost/phpmyadmin];
  • the [Terminal] button opens a command window where the [Laragon] tools (PHP, Composer, MySQL, etc.) can be used directly.

The PHP 7 course created the database and its user using the mouse with [phpMyAdmin]. Here, we prefer to write the corresponding SQL commands in a text file, called a SQL script. Such a script can be reviewed, corrected, stored along with the application code, and re-run at will to recreate the database exactly as it was. The [dbpersons.sql] script is as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
-- creation of the [dbpersons] database and of its user [admpersons]
-- to be executed by the administrator [root] of the MySQL DBMS:
--   mysql -u root < dbpersons.sql

-- the database, with the [utf8mb4] character set (all Unicode characters)
CREATE DATABASE IF NOT EXISTS dbpersons CHARACTER SET utf8mb4;

-- the user [admpersons] with password [nobody], who connects from the local machine
CREATE USER IF NOT EXISTS 'admpersons'@'localhost' IDENTIFIED BY 'nobody';

-- he has all rights on the [dbpersons] database and only on it
GRANT ALL PRIVILEGES ON dbpersons.* TO 'admpersons'@'localhost';

Comments

  • line 1: In SQL, a line beginning with two dashes is a comment;
  • Line 6: Creates the [dbpersons] database if it does not already exist. We will create a table of people in it. The character set [utf8mb4] allows you to store all Unicode characters (accented letters, as well as Asian characters, emojis, etc.). This is the default character set for MySQL 8. The name [utf8], used in the PHP 7 course, refers to an older, incomplete character set (three bytes per character at most) for MySQL, which is now deprecated;
  • Line 9: Creation of user [admpersons] with password [nobody]. A MySQL user is identified by their name and the machine from which they are logging in—in this case, [localhost], the local machine. The password [nobody] is very weak (easy to guess). In a production environment, you should choose a strong password;
  • Line 12: The user [admpersons] is granted full privileges on all tables ([dbpersons.*]) in the database [dbpersons], and no privileges on the other databases.

This script must be run by the administrator of the DBMS. In the [Laragon] installation, this is the user [root], who has no password. Open the [Laragon] terminal in the [scripts-console/mysql] folder and run the script using the [mysql] command-line client:

1
> mysql -u root < dbpersons.sql
  • The [-u root] option specifies the username used to log in to the DBMS. Since [root] has no password, it is not necessary to specify one (option [-p]);
  • the symbol [<] redirects the contents of the file [dbpersons.sql] to the input of the client [mysql], which executes the SQL commands one after another. When everything goes smoothly, nothing is displayed.

We could just as easily have opened the [dbpersons.sql] file in [HeidiSQL] (File menu / Load a SQL file) or in the [SQL] tab of [phpMyAdmin] and run it from there. Verify the permissions for the [admpersons] user by logging in as that user. The [-e] option allows you to pass a SQL command directly to the [mysql] client; the [show grants] command displays the permissions of the logged-in user:

1
2
3
4
5
6
7
8
> mysql -u admpersons -pnobody -e "show grants"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------------------------------------------------------------+
| Grants for admpersons@localhost                                   |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `admpersons`@`localhost`                    |
| GRANT ALL PRIVILEGES ON `dbpersons`.* TO `admpersons`@`localhost` |
+-------------------------------------------------------------------+
  • Line 2: The [mysql] client indicates that a password entered on the command line ([-pnobody], with no space after [-p]) can be seen by other users on the machine. With the [-p] option alone, the password is entered via the keyboard without being displayed;
  • Line 7: The user [admpersons] has full privileges on the database [dbpersons].

Now we have:

  • a MySQL database [dbpersons];
  • a user named [admpersons/nobody] who has full access to this database.

We will write PHP scripts to work with the database. PHP provides various libraries for managing databases. We will use the [PDO] library (PHP Data Objects), which is inserted between the PHP code and the DBMS:

Image

The [PDO] library allows the PHP script to be independent of the exact nature of the DBMS being used. Thus, as shown above, the MySQL DBMS can be replaced by the PostgreSQL DBMS with minimal impact on the PHP script code. Each PDO driver is an extension of PHP that must be enabled. The following [pdo-drivers.php] script displays the available drivers:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
<?php

// strict respect of the declared types of function parameters and results
declare(strict_types=1);

// the PDO drivers available in this PHP installation
print "Available PDO drivers: " . implode(", ", PDO::getAvailableDrivers()) . "\n";
// the corresponding PHP extensions
foreach (["pdo_mysql", "pdo_pgsql", "pdo_sqlite"] as $extension) {
    print "extension [$extension]: " . (extension_loaded($extension) ? "loaded" : "not loaded") . "\n";
}
  • line 7: The static method [getAvailableDrivers] of the class [PDO] returns the list of available PDO drivers;
  • Line 10: The function [extension_loaded] indicates whether an extension of PHP is loaded. The PDO drivers for MySQL, PostgreSQL, and SQLite are the extensions [pdo_mysql], [pdo_pgsql], and [pdo_sqlite].

Results:

1
2
3
4
5
> php pdo-drivers.php
Available PDO drivers: mysql, pgsql, sqlite
extension [pdo_mysql]: loaded
extension [pdo_pgsql]: loaded
extension [pdo_sqlite]: loaded

In this installation, all three drivers are active. Only the [mysql] driver is needed for this chapter. If a driver is missing, activate it in the [Laragon] menu: PHP / Extensions, then click on the desired extension, for example, [pdo_mysql]. Another way to enable an extension is to directly edit the [php.ini] file that configures PHP (menu PHP / php.ini from [Laragon]). An extension is enabled there by a line in the following format:

1
extension=pdo_mysql

It is disabled when the line is commented out with a semicolon ([;extension=pdo_pgsql]). After modifying the [php.ini] file, you must restart the [Laragon] services ([Reload] button) for Apache to apply the changes. The console scripts, on the other hand, re-read the [php.ini] file each time they run.

12.2. Connecting to a MySQL database

To connect to a DBMS, you must create a [PDO] object. You can use the constructor of the [PDO] class or, starting with PHP 8.4, its static method [connect]. Both accept the same parameters:

1
2
$connection = new PDO(string $dsn, ?string $username = null, ?string $password = null, ?array $options = null);
$connection = PDO::connect(string $dsn, ?string $username = null, ?string $password = null, ?array $options = null);

The parameters have the following meanings:

Parameter
Meaning
[$dsn]
(Data Source Name) a string specifying the nature of the DBMS and its location on the network. The string ["mysql:host=localhost"] indicates that this is a MySQL DBMS running on the local machine. This string may include other parameters, separated by semicolons, such as the listening port of the DBMS, the name of the database to which you want to connect, and the character set used for communication: ["mysql:host=localhost;port=3306;dbname=dbpersons;charset=utf8mb4"]
[$username]
the username of the user logging in
[$password]
their password
[$options]
an array of options for the DBMS driver

Only the first parameter is required. The object created in this way will then serve as the basis for all operations performed on the database to which you have connected. If the object cannot be created, a [PDOException] exception is thrown.

Here is an example of a [mysql-01.php] connection:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<?php

// strict respect of the declared types of function parameters and results
declare(strict_types=1);

// connection to a local MySQL database
// the identity of the user is (admpersons, nobody)
const ID = "admpersons";
const PWD = "nobody";
const HOST = "localhost";

try {
    // connection
    $connection = PDO::connect("mysql:host=" . HOST, ID, PWD);
    print "Successful connection\n";
    // the class of the object obtained
    print "The connection is an object of type [" . $connection::class . "]\n";
    // closing the connection
    $connection = null;
} catch (PDOException $ex) {
    print "Error: " . $ex->getMessage() . "\n";
    exit(1);
}

Comments

  • lines 8–10: the connection parameters;
  • line 14: the connection to the DBMS. The [connect] method is used here with the following parameters:
    • a string specifying the type of DBMS and its location. The string ["mysql:host=localhost"] indicates that this is a MySQL DBMS running on the local machine. The port was not specified; port 3306 is therefore used by default. The database name is not specified either. A connection will then be established to the MySQL DBMS, and a specific database can be selected later;
    • a user ID;
    • its password;
  • Line 17: [$connection::class] is the class name of the object [$connection]. Starting with PHP 8.4, each PDO driver has its own class, derived from the class [PDO]: [Pdo\Mysql] for MySQL, [Pdo\Pgsql] for PostgreSQL, [Pdo\Sqlite] for SQLite. The [PDO::connect] method creates an object of the class corresponding to DSN. With [new PDO(...)], we would obtain an object of the [PDO] class itself, which works the same way but does not provide access to driver-specific methods. Since we will only use the methods common to all drivers, both notations are acceptable;
  • line 19: The connection is closed by removing the last reference to the PDO object created initially. This happens automatically at the end of the script;
  • line 20: The connection to a DBMS may fail. In this case, a [PDOException] exception is thrown. This exception is derived from the PHP exception [RuntimeException];
  • Line 21: The exception’s error message is displayed;
  • line 22: the script is terminated with a non-zero exit code, which signals the failure to any calling program.

Results:

1
2
3
> php mysql-01.php
Successful connection
The connection is an object of type [Pdo\Mysql]

Let’s rerun the script by setting the constant [PWD] to an incorrect password. The result is as follows:

1
2
> php mysql-01.php
Error: SQLSTATE[HY000] [1045] Access denied for user 'admpersons'@'localhost' (using password: YES)

The message contains three pieces of information: the code SQLSTATE [HY000], a standardized error code common to all DBMSs (HY000 = general error), the MySQL-specific error code [1045], and the MySQL message.

12.3. Creating a Table

The script [mysql-02.php] demonstrates how to create a table in a database:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
<?php

// strict respect of the declared types of function parameters and results
declare(strict_types=1);

// identity of the database
const DSN = "mysql:host=localhost;dbname=dbpersons;charset=utf8mb4";
// credentials of the user
const ID = "admpersons";
const PWD = "nobody";

try {
    // connection to the MySQL database
    $connection = PDO::connect(DSN, ID, PWD);
    // deleting the [persons] table if it exists
    $sql = "drop table if exists persons";
    $connection->exec($sql);
    // creating the [persons] table
    $sql = "create table persons (first_name varchar(30) not null, last_name varchar(30) not null, "
        . "age integer not null, primary key(last_name, first_name))";
    $connection->exec($sql);
    print "The [persons] table has been created\n";
} catch (PDOException $ex) {
    // error display
    print "Error: " . $ex->getMessage() . "\n";
} finally {
    // we disconnect if need be
    $connection = null;
}
// end
print "Done\n";

Comments

  • Line 7: This time, DSN refers to the database [dbpersons] ([dbname=dbpersons]). The parameter [charset=utf8mb4] tells the DBMS that the character strings exchanged with it are encoded in UTF-8, the encoding used in our scripts (PHP). This ensures that accented characters are correctly stored in the database and correctly retrieved;
  • line 14: connect to the database. This is always the first step. The result of the connection is a [PDO] object through which database operations will be performed;
  • Line 16: The SQL command [drop table] deletes the table [persons] from the database [dbpersons]. The clause [if exists] prevents an error if the table [persons] does not exist;
  • Line 17: Execution of the previous SQL command on the database [dbpersons] using the method [PDO::exec]. This execution may trigger a [PDOException], which will be intercepted by the [catch] clause;
  • Lines 19–20: This SQL command creates a table named [persons]. A table contains rows and columns. The columns form what is called the table structure. The rows form the table’s content. A database can contain one or more tables. The table [persons] will have three columns here:
    • first_name: a person’s first name as a string of up to 30 characters;
    • last_name: the last name of the same person as a string of up to 30 characters;
    • age: the person’s age as an integer;
    • The [not null] attribute on a column requires that the column have a value. Failing to provide one results in a [PDOException] error;
    • [primary key(last_name, first_name)] defines a primary key for the table [persons]. A primary key has a unique value for each row in the table. Here, the primary key is obtained by concatenating the columns [last_name] and [first_name] from the row. This constraint ensures that the table cannot contain two people with the same first and last names—that is, two people with the same full name. Creating a person with the same name as another in the table triggers a [PDOException];
  • lines 23–25: if a [PDOException] error occurs, the associated error message is displayed;
  • lines 26–28: the [finally] clause is executed in all cases, whether an exception occurs or not, to close the connection to the database.

In the event of an error, a SQL operation performed with a [PDO] object may either return a specific value (often the Boolean [false]) without reporting anything, or throw an exception. This behavior is controlled by the [PDO::ATTR_ERRMODE] attribute of the connection. In PHP 7, the default mode was the first one: errors were silent, and the PHP 7 procedure requested the second mode using the following statement:

1
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Starting with PHP 8.0, exception mode is the default: any DBMS error triggers a [PDOException]. This instruction is therefore no longer necessary, and we will no longer include it. This is also why we wrote [drop table if exists]: in PHP 7, the [drop table persons] command on a nonexistent table failed silently; in PHP 8, it would throw an exception.

Results:

1
2
3
> php mysql-02.php
The [persons] table has been created
Done

If the script runs without errors, you can verify the existence of the table and its structure using [HeidiSQL], [phpMyAdmin], or, more simply, the [mysql] client. The SQL command [describe] displays the structure of a table:

1
2
3
4
5
6
7
8
9
> mysql -u admpersons -pnobody dbpersons -e "describe persons"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| first_name | varchar(30) | NO   | PRI | NULL    |       |
| last_name  | varchar(30) | NO   | PRI | NULL    |       |
| age        | int         | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
  • Lines 6–8: the three columns of the table, along with their types (Type column);
  • Null column: none of the three columns can be empty;
  • Key column: the columns [first_name] and [last_name] are part of the primary key (PRI).

The SQL query [show index] displays the table’s indexes. An index allows you to find rows in the table with a specific index value faster than if you were to sequentially scan through the table’s rows. The primary key is always part of the indexes, but an index may not be a primary key:

1
2
3
4
5
6
7
8
> mysql -u admpersons -pnobody dbpersons -e "show index from persons"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| persons |          0 | PRIMARY  |            1 | last_name   | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| persons |          0 | PRIMARY  |            2 | first_name  | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
  • The index here is the primary key (column Key_name = PRIMARY);
  • it consists of the [last_name, first_name] columns from each row, in that order (columns Seq_in_index and Column_name).

Now, let’s see what happens if we introduce errors in the database name, the username, and the password, respectively.

If we enter a nonexistent database name ([dbname=dbpersons2]):

1
2
3
> php mysql-02.php
Error: SQLSTATE[HY000] [1044] Access denied for user 'admpersons'@'localhost' to database 'dbpersons2'
Done

If we enter a nonexistent username:

1
2
3
> php mysql-02.php
Error: SQLSTATE[HY000] [1045] Access denied for user 'admpersons2'@'localhost' (using password: YES)
Done

If you enter an incorrect password:

1
2
3
> php mysql-02.php
Error: SQLSTATE[HY000] [1045] Access denied for user 'admpersons'@'localhost' (using password: YES)
Done

Note that MySQL does not indicate whether the username or password is incorrect: this is a security measure designed to give no clues to anyone trying to guess the credentials. Similarly, the user [admpersons] does not learn whether the database [dbpersons2] exists or not: they only know that they cannot access it.

12.4. Filling a Table

We’re going to write a PHP script that executes the SQL commands found in the following text file, [creation.txt]:

1
2
3
4
5
6
7
8
9
drop table if exists persons
create table persons (first_name varchar(30) not null, last_name varchar(30) not null, age integer not null, primary key (last_name,first_name))
insert into persons (first_name, last_name, age) values('Paul','Langevin',48)
insert into persons (first_name, last_name, age) values ('Sylvie','Lefur',70)
insert into persons (first_name, last_name, age) values ('Sylvie','Lefur',70)
insert into persons (first_name, last_name, age) values ('Pierre','Nicazou',35)
insert into persons (first_name, last_name, age) values ('Géraldine','Colou',26)
insert into persons (first_name, last_name, age) values ('Paulette','Girond',56)
insert into persons (first_name, last_name, age) values ('Paulette','Girond',56)

Comments

  • The SQL language (Structured Query Language) is not case-sensitive (upper- and lowercase letters) for SQL commands;
  • Line 1: Delete the table [persons] if it exists;
  • Line 2: Create the table [persons] with three columns (first_name, last_name, age) and the primary key (last_name, first_name);
  • Lines 3–9: Insert 7 rows into the table [persons];
  • line 5: this insert statement should fail because it attempts the same insertion as the previous line. The primary key constraint should prevent this insertion: there cannot be two people with the same first and last names;
  • line 9: this insert statement should also fail for the same reason;
  • line 7: the first name in [Géraldine] contains an accented character. The file [creation.txt], like all files in the course, is encoded as UTF-8: this is the default encoding for [VS Code], as displayed in its status bar. The PHP 7 course added the MySQL-specific command to the file to instruct the DBMS on the encoding of the received characters. This is no longer necessary: the encoding is set once and for all by the [charset=utf8mb4] parameter of DSN.

The PHP script responsible for executing the SQL commands in this text file is as follows: [mysql-03.php]:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
<?php

// strict respect of the declared types of function parameters and results
declare(strict_types=1);

// identity of the database
const DSN = "mysql:host=localhost;dbname=dbpersons;charset=utf8mb4";
// credentials of the user
const ID = "admpersons";
const PWD = "nobody";
// identity of the text file of the SQL commands to execute
const SQL_COMMANDS_FILENAME = __DIR__ . "/creation.txt";

// opening the connection to the MySQL database
try {
    $connection = PDO::connect(DSN, ID, PWD);
} catch (PDOException $ex) {
    // error display
    print "Error: " . $ex->getMessage() . "\n";
    exit(1);
}
// execution of the file of SQL statements
$errors = executeCommands($connection, SQL_COMMANDS_FILENAME, tracking: true, stop: false);
// closing the connection
$connection = null;
// display of the errors
printf("\n-----------------------\nThere were %d error(s)\n", count($errors));
foreach ($errors as $error) {
    print "$error\n";
}
// it's over
print "Done\n";

// ---------------------------------------------------------------------------------
function executeCommands(PDO $connection, string $sqlFileName, bool $tracking = false, bool $stop = true): array
{
    // uses the connection $connection
    // executes the SQL commands contained in the text file $sqlFileName
    // this file is a file of SQL commands to execute, one per line
    // if $tracking is true, each execution of an SQL statement is displayed with its success or failure
    // if $stop is true, the function stops at the 1st error encountered, otherwise it executes all the SQL commands
    // the function returns the array of error messages (empty if there was no error)

    // we put the lines of the file into an array, without the end-of-line marks or the empty lines
    $queries = is_readable($sqlFileName)
        ? file($sqlFileName, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES)
        : false;
    // error?
    if ($queries === false) {
        return ["The file [$sqlFileName] does not exist or could not be read"];
    }
    // we execute the queries one by one - no errors at the start
    $errors = [];
    foreach ($queries as $query) {
        // we remove the spaces at the beginning and end of the line
        $query = trim($query);
        // empty query?
        if ($query === "") {
            // we move on to the next query
            continue;
        }
        try {
            // execution of the query - an exception is thrown in case of error
            $connection->exec($query);
            // screen tracking or not?
            if ($tracking) {
                print "$query: Successful execution\n";
            }
        } catch (PDOException $ex) {
            // an error occurred
            addError($errors, $query, $ex->getMessage(), $tracking);
            // do we stop?
            if ($stop) {
                break;
            }
        }
    }
    // result
    return $errors;
}

function addError(array &$errors, string $query, string $msg, bool $tracking): void
{
    // we add an error message
    $msg = "$query: Error ($msg)";
    $errors[] = $msg;
    // screen tracking or not?
    if ($tracking) {
        print "$msg\n";
    }
}

Comments

  • The function [executeCommands] (lines 35–80) is responsible for executing the SQL commands that it finds in the text file [$sqlFileName] (parameter 2). To execute them, it uses the open connection [$connection] (parameter 1) with the MySQL server. The third parameter, [$tracking], is a Boolean that controls screen output: when set to [true], the executed SQL command is displayed on the screen along with whether it succeeded or failed; otherwise, the execution of the SQL command is silent. The fourth parameter, [$stop], controls what to do when a SQL command fails: If [true], it specifies that the execution of the SQL commands must stop; otherwise, execution continues. The function [executeCommands] returns an array of error messages; this array is empty if no errors occurred.;
  • line 12: the path to the [creation.txt] file is constructed from the constant [__DIR__], which is the script’s directory. This ensures the script can locate its file regardless of the directory from which it is run;
  • lines 15–20: A connection is opened to the MySQL database [dbpersons]. If the connection fails, an error message is displayed and the script terminates;
  • line 23: The function [executeCommands] is called to execute the SQL command file. An open connection is passed to it. The last two arguments are passed by name ([tracking: true, stop: false]): these are named arguments (PHP 8.0). The call [executeCommands($connection, SQL_COMMANDS_FILENAME, true, false)] would be equivalent but less readable: without looking at the function signature, it is unclear what [true] and [false] mean;
  • line 25: the connection is closed;
  • lines 27–30: the errors returned by the function [executeCommands] are displayed;
  • Lines 45–47: The SQL commands are placed in the array [$queries] using the [file] function. The [FILE_IGNORE_NEW_LINES] option removes the end-of-line character from each line read, while the [FILE_SKIP_EMPTY_LINES] option ignores empty lines. Both options are combined by the [|] operator (binary OR). If the file is unreadable, [file] is not called—which would issue a warning—and [$queries] is equivalent to [false];
  • lines 49–51: if the SQL command file could not be read, an error array containing a single message is returned;
  • line 53: errors are accumulated in the array [$errors];
  • line 54: we loop through all queries;
  • line 56: the function [trim] removes the spaces preceding and following the text of the SQL command. By “spaces,” we mean the space character, the tab character \t, the carriage return \r, the line feed \n, etc. A line containing only spaces thus becomes empty: it is ignored (line 60);
  • line 64: the SQL command is sent to the MySQL server. The method [PDO::exec] throws an exception if execution fails. If execution succeeds, the SQL command is displayed on the screen if the parameter [$tracking] is set to [true];
  • line 71: If an error occurs, the error message is added to the error array;
  • lines 73–75: if the parameter [$stop] is set to [true], the loop is exited using the statement [break];
  • line 79: Once all SQL commands have been executed, the error array [$errors] is returned.

The function [addError] (lines 82–91) adds an error to the error array [$errors]. It takes 4 parameters:

  • the parameter [$errors] is passed by reference ([&$errors]). This is because we want to modify the array passed as a parameter, not a copy of it;
  • the parameter [$query] is the SQL text from the command that failed;
  • The parameter [$msg] is the error message associated with the failed command;
  • the Boolean [$tracking] indicates whether the error message should be displayed ([true]) or not ([false]) on the console.

Note that the functions [executeCommands] and [addError] are defined after the code that uses them. This is possible because PHP identifies all functions defined at the top level of a script before beginning to execute it.

Screen output:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
> php mysql-03.php
drop table if exists persons: Successful execution
create table persons (first_name varchar(30) not null, last_name varchar(30) not null, age integer not null, primary key (last_name,first_name)): Successful execution
insert into persons (first_name, last_name, age) values('Paul','Langevin',48): Successful execution
insert into persons (first_name, last_name, age) values ('Sylvie','Lefur',70): Successful execution
insert into persons (first_name, last_name, age) values ('Sylvie','Lefur',70): Error (SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Lefur-Sylvie' for key 'persons.PRIMARY')
insert into persons (first_name, last_name, age) values ('Pierre','Nicazou',35): Successful execution
insert into persons (first_name, last_name, age) values ('Géraldine','Colou',26): Successful execution
insert into persons (first_name, last_name, age) values ('Paulette','Girond',56): Successful execution
insert into persons (first_name, last_name, age) values ('Paulette','Girond',56): Error (SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Girond-Paulette' for key 'persons.PRIMARY')

-----------------------
There were 2 error(s)
insert into persons (first_name, last_name, age) values ('Sylvie','Lefur',70): Error (SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Lefur-Sylvie' for key 'persons.PRIMARY')
insert into persons (first_name, last_name, age) values ('Paulette','Girond',56): Error (SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Girond-Paulette' for key 'persons.PRIMARY')
Done
  • Lines 6 and 10: Both insertions of homonyms failed: the primary key 'Lefur-Sylvie' (and 'Girond-Paulette', respectively) already exists (Duplicate entry). The code SQLSTATE [23000] indicates a violation of an integrity constraint; the code [1062] is the MySQL error code;
  • Lines 12–16: the error summary.

The insertions made are visible using client [mysql]:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
> mysql -u admpersons -pnobody dbpersons -e "select * from persons"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+-----------+-----+
| first_name | last_name | age |
+------------+-----------+-----+
| Géraldine  | Colou     |  26 |
| Paulette   | Girond    |  56 |
| Paul       | Langevin  |  48 |
| Sylvie     | Lefur     |  70 |
| Pierre     | Nicazou   |  35 |
+------------+-----------+-----+

Note that the rows are returned in the order of the primary key (last name, first name), which is not the order of the insertions. In SQL, the order of the rows returned by a [select] is guaranteed only if specified using a [order by] clause.

12.5. Executing any SQL commands

The following script demonstrates the execution of SQL commands from the following text file, [sql.txt]:

1
2
3
4
5
6
7
8
9
select * from persons
select last_name,first_name from persons order by last_name asc, first_name desc
select * from persons where age between 20 and 40 order by age desc, last_name asc, first_name asc
insert into persons values('Josette','Bruneau',46)
update persons set age=47 where last_name='Bruneau'
select * from persons where last_name='Bruneau'
delete from persons where last_name='Bruneau'
select * from persons where last_name='Bruneau'
xselect * from persons where last_name='Bruneau'

Among these SQL commands, there is the [select] command, which returns results from the database; the [insert], [update], [delete], which modify the database without returning any results, and finally erroneous commands such as the last one ([xselect]). The script [mysql-04.php] is based on the script [mysql-03.php], with the name of the SQL command file ([__DIR__ . "/sql.txt"]) changed. Only the function [executeCommands] is modified, and a function [displayInfo] is added:

 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
function executeCommands(PDO $connection, string $sqlFileName, bool $tracking = false, bool $stop = true): array
{
    // uses the connection $connection
    // executes the SQL commands contained in the text file $sqlFileName
    // this file is a file of SQL commands to execute, one per line
    // if $tracking is true, each execution of an SQL statement is displayed with its success or failure
    // if $stop is true, the function stops at the 1st error encountered, otherwise it executes all the SQL commands
    // the function returns the array of error messages (empty if there was no error)

    // we put the lines of the file into an array, without the end-of-line marks or the empty lines
    $queries = is_readable($sqlFileName)
        ? file($sqlFileName, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES)
        : false;
    // error?
    if ($queries === false) {
        return ["The file [$sqlFileName] does not exist or could not be read"];
    }
    // we execute the queries one by one - no errors at the start
    $errors = [];
    foreach ($queries as $query) {
        // we remove the spaces at the beginning and end of the line
        $query = trim($query);
        // empty query?
        if ($query === "") {
            // we move on to the next query
            continue;
        }
        // the name of the SQL command: the first word of the query, in lowercase
        $command = strtolower(explode(" ", $query, 2)[0]);
        try {
            // execution of the query - an exception is thrown in case of error
            // a SELECT statement returns rows, the other statements a number of modified rows
            $result = $command === "select"
                ? $connection->query($query, PDO::FETCH_NUM)
                : $connection->exec($query);
            // screen tracking or not?
            if ($tracking) {
                print "[$query]: Successful execution\n";
            }
            // we display the result of the execution
            displayInfo($command, $result);
        } catch (PDOException $ex) {
            // an error occurred
            addError($errors, $query, $ex->getMessage(), $tracking);
            // do we stop?
            if ($stop) {
                break;
            }
        }
    }
    // result
    return $errors;
}

function addError(array &$errors, string $query, string $msg, bool $tracking): void
{
    // we add an error message
    $msg = "[$query]: Error ($msg)";
    $errors[] = $msg;
    // screen tracking or not?
    if ($tracking) {
        print "$msg\n";
    }
}

// ---------------------------------------------------------------------------------
function displayInfo(string $command, PDOStatement|int $result): void
{
    // displays the result $result of an SQL query
    if ($result instanceof PDOStatement) {
        // case of the select: we display the names of the columns
        $columns = [];
        for ($i = 0; $i < $result->columnCount(); $i++) {
            $columns[] = $result->getColumnMeta($i)["name"];
        }
        $title = implode(",", $columns);
        print "$title\n";
        // separator line of the same length as the title
        print str_repeat("-", mb_strlen($title)) . "\n";
        // the rows of the result: each one is an array of the column values
        foreach ($result as $row) {
            print implode(",", $row) . "\n";
        }
        return;
    }
    // case of the insert, update, delete statements: $result is the number of modified rows
    if (in_array($command, ["insert", "update", "delete"], true)) {
        print " $result row(s) has (have) been modified\n";
    }
}

Comments

  • Lines 35–87: The [executeCommands] function has been slightly modified: the [select] command is not executed in the same way as the other SQL commands. This command is the only one that returns a table as a result—that is, a set of rows and columns from the database;
  • Line 63: We isolate the first word of the SQL command. The function [explode] splits the query at spaces into at most 2 parts (3rd parameter); the first segment ([[0]]) is the command name, converted to lowercase by the function [strtolower]. The PHP 7 course used a regular expression;
  • lines 67–69: if the SQL command is [select], the [PDO::query] method is used; otherwise, the [PDO::exec] method is used to execute the SQL command. In both cases, if execution fails, an exception is thrown and caught by the [catch] clause. If execution succeeds, its result is displayed by the [displayInfo] function (line 75):
    • The method [query] returns an object of type [PDOStatement], which provides access to the rows returned by [select]. Its second parameter, [PDO::FETCH_NUM], specifies that each row must be rendered as an array indexed by column numbers 0, 1, 2, …;
    • the method [exec] returns an integer: the number of rows modified by the SQL command;
  • Lines 101–124: The function [displayInfo] displays information about the result of executing a SQL command. The parameter [$result] has the type [PDOStatement|int]: this is a union type (PHP 8.0), which accepts either a [PDOStatement] object or an integer;
  • line 104: the case for [select] is handled. The [instanceof] operator checks the type of the [$result] object;
  • line 107: the method [PDOStatement::columnCount] returns the number of columns in the result table of [select];
  • line 108: the [PDOStatement::getColumnMeta] method returns an array containing information about column No. [$i] in the result table of [select]. In this array, the value associated with the 'name' key is the column name. The column names are collected in the array [$columns];
  • line 110: the function [implode] concatenates the column names, separating them with commas;
  • line 113: the function [str_repeat] is used to construct a separator line that is the same length as the title. The function [mb_strlen] counts the characters in a UTF-8 string; the function [strlen] counts the bytes, and an accented character occupies two bytes;
  • Lines 115–117: An object of type [PDOStatement] can be iterated over using a [foreach] loop. At each iteration, the resulting element is a row from the [select] result table, in the form of an array containing the values of the row’s various columns. These values are displayed separated by commas;
  • Lines 121–123: The result of executing a command [insert], [update], or [delete] is the number of lines modified by the command. The PHP 7 course used a [switch] statement here, one of whose clauses was mistakenly written as [case "delete";] with a semicolon instead of a colon. PHP tolerated this; as of PHP 8.5, this syntax is deprecated.

Screen output:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
> php mysql-04.php
[select * from persons]: Successful execution
first_name,last_name,age
------------------------
Géraldine,Colou,26
Paulette,Girond,56
Paul,Langevin,48
Sylvie,Lefur,70
Pierre,Nicazou,35
[select last_name,first_name from persons order by last_name asc, first_name desc]: Successful execution
last_name,first_name
--------------------
Colou,Géraldine
Girond,Paulette
Langevin,Paul
Lefur,Sylvie
Nicazou,Pierre
[select * from persons where age between 20 and 40 order by age desc, last_name asc, first_name asc]: Successful execution
first_name,last_name,age
------------------------
Pierre,Nicazou,35
Géraldine,Colou,26
[insert into persons values('Josette','Bruneau',46)]: Successful execution
 1 row(s) has (have) been modified
[update persons set age=47 where last_name='Bruneau']: Successful execution
 1 row(s) has (have) been modified
[select * from persons where last_name='Bruneau']: Successful execution
first_name,last_name,age
------------------------
Josette,Bruneau,47
[delete from persons where last_name='Bruneau']: Successful execution
 1 row(s) has (have) been modified
[select * from persons where last_name='Bruneau']: Successful execution
first_name,last_name,age
------------------------
[xselect * from persons where last_name='Bruneau']: Error (SQLSTATE[42000]: Syntax error or access violation: 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 'xselect * from persons where last_name='Bruneau'' at line 1)

-----------------------
There were 1 error(s)
[xselect * from persons where last_name='Bruneau']: Error (SQLSTATE[42000]: Syntax error or access violation: 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 'xselect * from persons where last_name='Bruneau'' at line 1)
Done
  • lines 33–35: the last [select] returns no rows: the person [Bruneau] was deleted by the previous command [delete];
  • Line 36: The command [xselect] does not exist. The code SQLSTATE [42000] indicates a syntax error or an access violation; the MySQL code [1064] indicates a syntax error.

12.6. Using pre-prepared SQL commands

12.6.1. Example 1

Let’s examine the following script: [mysql-05.php]:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
<?php

// strict respect of the declared types of function parameters and results
declare(strict_types=1);

// identity of the database
const DSN = "mysql:host=localhost;dbname=dbpersons;charset=utf8mb4";
// credentials of the user
const ID = "admpersons";
const PWD = "nobody";

try {
    // connection to the MySQL database
    $connection = PDO::connect(DSN, ID, PWD);
    // we empty the persons table
    $connection->exec("delete from persons");
    // a list of persons
    $persons = [
        ["last_name" => "Langevin", "first_name" => "Paul", "age" => 47],
        ["last_name" => "Lefur", "first_name" => "Sylvie", "age" => 28],
    ];
    // we are going to put these persons into the database with a prepared SQL statement
    $statement = $connection->prepare("insert into persons (last_name, first_name, age) values (:last_name, :first_name, :age)");
    foreach ($persons as $person) {
        // the values of the parameters :last_name, :first_name, :age are taken from the associative array
        $statement->execute($person);
        print "{$statement->rowCount()} row inserted: {$person["first_name"]} {$person["last_name"]}\n";
    }
    // a third person, with the [bindValue] method
    $statement->bindValue("last_name", "Nicazou");
    $statement->bindValue("first_name", "Pierre");
    $statement->bindValue("age", 35, PDO::PARAM_INT);
    $statement->execute();
    print "{$statement->rowCount()} row inserted: Pierre Nicazou\n";
} catch (PDOException $ex) {
    // error display
    print "Error: " . $ex->getMessage() . "\n";
} finally {
    // closing the connection
    $connection = null;
}
// it's over
print "Done\n";

Comments

Here, we are interested in the lines that insert people into the people table of the [dbpersons] database.

  • Line 16: The SQL command [delete] without the [where] clause deletes all rows from the table;
  • Line 23: A parameterized SQL command is “prepared.” The parameters are preceded by a colon: [:last_name], [:first_name], [:age]. To “prepare” a SQL command, use the [PDO::prepare] method. The result is an object of type [PDOStatement]. “Preparation” is not an execution: nothing is modified in the database;
  • Line 26: Execution of the prepared statement using the [PDOStatement::execute] method. To do this, you must assign values to the parameters [:last_name], [:first_name], and [:age]. There are several ways to do this. Here, we use an associative array whose keys are the names of the prepared statement’s parameters, which we pass to the [PDOStatement::execute] method;
  • line 27: the method [PDOStatement::rowCount] returns the number of rows modified by the last execution of the prepared statement. In the string enclosed in quotation marks, the method call is enclosed in curly braces [{...}] so that PHP can evaluate it. The same applies to the array element [{$person["first_name"]}];
  • lines 30–33: Another way to do this is to assign a value to the parameters using the [PDOStatement::bindValue] method before calling [execute] without any arguments. The third optional parameter of [bindValue] specifies the SQL type of the value ([PDO::PARAM_INT] for an integer, [PDO::PARAM_STR]—the default—for a string). The drawback is that one statement is required per parameter. The associative array method is often more convenient.

The method used here to perform the inserts:

  • one prepared statement;
  • n executions of the prepared statement;

is more efficient in terms of execution time than executing n different SQL statements. Above all, it has another decisive advantage: the parameter values are passed to the DBMS separately from the text of the SQL statement. Therefore, they cannot alter the command’s meaning. If the SQL command were constructed by concatenation—for example, ["insert into persons values ('$first_name', '$last_name', $age)"], where a first name containing an apostrophe (“ Jean-Michel d'Ormesson”) would cause a syntax error, and a malicious user could slip in SQL commands of their choice: this is known as a SQL injection attack. Prepared queries should therefore be used whenever a query contains values sourced from outside the script. They can be used for the SQL commands, [select], [update], [delete], and [insert]. In the case of the SQL command [select], after it is executed with [PDOStatement::execute], the result rows are retrieved using the methods [PDOStatement::fetch] or [PDOStatement::fetchAll].

Results:

1
2
3
4
5
> php mysql-05.php
1 row inserted: Paul Langevin
1 row inserted: Sylvie Lefur
1 row inserted: Pierre Nicazou
Done

12.6.2. Example 2

The following script, [mysql-06.php], demonstrates the use of a prepared statement for the operation SQL [select], as well as various ways to retrieve the rows returned by this operation:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
<?php

// strict respect of the declared types of function parameters and results
declare(strict_types=1);

// identity of the database
const DSN = "mysql:host=localhost;dbname=dbpersons;charset=utf8mb4";
// credentials of the user
const ID = "admpersons";
const PWD = "nobody";

// a class whose attributes bear the names of the columns of the [persons] table
class Person
{
    private string $last_name;
    private string $first_name;
    private int $age;

    public function __toString(): string
    {
        return "Person[$this->last_name,$this->first_name,$this->age]";
    }
}

// an immutable class built from a row of the [persons] table
final readonly class ImmutablePerson
{
    public function __construct(
        public string $last_name,
        public string $first_name,
        public int $age,
    ) {
    }
}

try {
    // connection to the MySQL database - the rows read will be associative arrays by default
    $connection = PDO::connect(DSN, ID, PWD, [PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
    // we empty the persons table
    $connection->exec("delete from persons");
    // we put 10 persons into the database
    $statement = $connection->prepare("insert into persons (last_name, first_name, age) values (:last_name, :first_name, :age)");
    for ($i = 0; $i < 10; $i++) {
        $statement->execute(["last_name" => "lastName$i", "first_name" => "firstName$i", "age" => $i * 10]);
    }
    // we query the database
    $statement = $connection->prepare("select last_name, first_name, age from persons order by last_name");
    $statement->execute();
    // 1st row: default mode of the connection (associative array)
    $row = $statement->fetch();
    var_dump($row);
    // 2nd row: array indexed by both the names and the numbers of the columns
    $row = $statement->fetch(PDO::FETCH_BOTH);
    var_dump($row);
    // 3rd row: [stdClass] object
    $row = $statement->fetch(PDO::FETCH_OBJ);
    var_dump($row);
    // 4th row: [Person] object
    $statement->setFetchMode(PDO::FETCH_CLASS, Person::class);
    $row = $statement->fetch();
    var_dump($row);
    // sequential reading of the remaining rows
    while ($person = $statement->fetch()) {
        print "$person\n";
    }
    // all the rows at once, each one turned into an [ImmutablePerson] object
    $statement = $connection->prepare("select last_name, first_name, age from persons where age >= :age order by last_name");
    $statement->execute(["age" => 70]);
    $persons = array_map(fn(array $row): ImmutablePerson => new ImmutablePerson(...$row), $statement->fetchAll());
    var_dump($persons);
} catch (PDOException $ex) {
    // error display
    print "Error: " . $ex->getMessage() . "\n";
} finally {
    // closing the connection
    $connection = null;
}
// it's over
print "Done\n";

Comments

  • lines 13–23: a class named [Person] whose typed private attributes have the names of the columns in table [persons];
  • Lines 26–34: an immutable class [ImmutablePerson] whose constructor has parameters named after the columns in table [persons];
  • line 38: the fourth parameter of [PDO::connect] is an array of connection options. The option [PDO::ATTR_DEFAULT_FETCH_MODE] determines how the read rows will be rendered by default: in this case, [PDO::FETCH_ASSOC], which uses associative arrays indexed by column names. Without this option, the default mode is [PDO::FETCH_BOTH] (see below);
  • lines 43–45: 10 rows are inserted into the table [persons] using a prepared query. The array of values is constructed directly within the call to [execute];
  • lines 47–48: A SQL [select] command is “prepared” and then executed;
  • line 50: the [PDOStatement::fetch] method is used to retrieve the next line from the result of the executed SQL [select] operation—in this case, the first line. The [fetch] method can return rows in various ways. The script demonstrates a few of them. Without any parameters, it uses the connection’s default mode, which in this case is an associative array. Note that age is rendered as an integer ([int(0)]). This is a new feature in PHP 8.1: previously, the MySQL driver rendered all values as character strings ([string(1) "0"] in PHP 7). The result displayed by [var_dump] is as follows:
2
3
4
5
6
7
8
9
array(3) {
  ["last_name"]=>
  string(9) "lastName0"
  ["first_name"]=>
  string(10) "firstName0"
  ["age"]=>
  int(0)
}
  • Line 53: The parameter [PDO::FETCH_BOTH] causes the returned row to be an array indexed by both column names and column numbers:
10
11
12
13
14
15
16
17
18
19
20
21
22
23
array(6) {
  ["last_name"]=>
  string(9) "lastName1"
  [0]=>
  string(9) "lastName1"
  ["first_name"]=>
  string(10) "firstName1"
  [1]=>
  string(10) "firstName1"
  ["age"]=>
  int(10)
  [2]=>
  int(10)
}
  • line 56: the parameter [PDO::FETCH_OBJ] causes the returned row to be an object of type [stdClass], whose attributes are the names of the table’s columns:
24
25
26
27
28
29
30
31
object(stdClass)#2 (3) {
  ["last_name"]=>
  string(9) "lastName2"
  ["first_name"]=>
  string(10) "firstName2"
  ["age"]=>
  int(20)
}
  • Line 59: The read mode of the [fetch] method is set using the [PDOStatement::setFetchMode] method. This mode then becomes the default mode for the [$statement] command until it is changed—either by another [setFetchMode] operation or by passing a mode as a parameter to the [fetch] method, as was done previously. The operation [setFetchMode(PDO::FETCH_CLASS, Person::class)] specifies that the read line must be placed in an object of type [Person]. [Person::class] is the string "Person," the full name of the class. PDO creates the object and then assigns the values from the columns of the same name to its attributes, even if those attributes are private. The following line displays the result:
32
33
34
35
36
37
38
39
object(Person)#4 (3) {
  ["last_name":"Person":private]=>
  string(9) "lastName3"
  ["first_name":"Person":private]=>
  string(10) "firstName3"
  ["age":"Person":private]=>
  int(30)
}
  • Lines 63–65: Show how to process the remaining rows of [select] sequentially. The method [fetch] returns [false] when there are no more rows, which terminates the loop. The display of [$person] uses the [__toString] method from the [Person] class:
40
41
42
43
44
45
Person[lastName4,firstName4,40]
Person[lastName5,firstName5,50]
Person[lastName6,firstName6,60]
Person[lastName7,firstName7,70]
Person[lastName8,firstName8,80]
Person[lastName9,firstName9,90]
  • lines 67–68: a configured [select]: we only want people who are at least 70 years old;
  • Line 69: The [PDOStatement::fetchAll] method returns all rows of the result at once, in this case as associative arrays (the connection’s default mode). The [array_map] function converts each of them into a [ImmutablePerson] object. The expression [new ImmutablePerson(...$row)] uses the spread operator [...] on an associative array: each key becomes a named argument (PHP 8.1). Thus, the line [["last_name" => "lastName7", "first_name" => "firstName7", "age" => 70]] results in the call [new ImmutablePerson(last_name: "lastName7", first_name: "firstName7", age: 70)]. This technique is suitable for an immutable class whose attributes are initialized by the constructor, whereas the [PDO::FETCH_CLASS] mode—which assigns values directly to the attributes—fails with a class such as [ImmutablePerson], whose constructor requires arguments. It also has the advantage of having PHP verify the type of each value. The displayed result is as follows:
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
array(3) {
  [0]=>
  object(ImmutablePerson)#2 (3) {
    ["last_name"]=>
    string(9) "lastName7"
    ["first_name"]=>
    string(10) "firstName7"
    ["age"]=>
    int(70)
  }
  [1]=>
  object(ImmutablePerson)#6 (3) {
    ["last_name"]=>
    string(9) "lastName8"
    ["first_name"]=>
    string(10) "firstName8"
    ["age"]=>
    int(80)
  }
  [2]=>
  object(ImmutablePerson)#7 (3) {
    ["last_name"]=>
    string(9) "lastName9"
    ["first_name"]=>
    string(10) "firstName9"
    ["age"]=>
    int(90)
  }
}

The following table summarizes the read modes encountered:

Mode
A line is rendered in the form
[PDO::FETCH_ASSOC]
an associative array [column => value]
[PDO::FETCH_NUM]
an array indexed by the column numbers 0, 1, 2…
[PDO::FETCH_BOTH]
an array indexed by both column names and numbers (default mode for PDO)
[PDO::FETCH_OBJ]
an object [stdClass] whose attributes are the column names
[PDO::FETCH_CLASS]
an object of the specified class whose attributes have the column names

Each driver also has its own options, grouped within its class starting with PHP 8.4. For example, the option that executes a SQL command each time a connection is made to MySQL is written as [Pdo\Mysql::ATTR_INIT_COMMAND]. The old constants [PDO::MYSQL_ATTR_INIT_COMMAND], which are found in many documents, have been deprecated since PHP 8.5.

12.7. Using Transactions

A transaction allows you to group a sequence of SQL commands into a single unit of execution: either all commands succeed, or one of them fails, in which case all preceding SQL commands are rolled back. In other words, when you use a transaction to execute SQL commands, after the transaction completes, the database is in a stable state:

  • either in a new state created by the successful execution of all SQL commands in the transaction;
  • or in the state it was in before the transaction began to be executed.

Let’s revisit the example of executing the SQL commands contained in a text file discussed in the section “Executing Any SQL Commands.” We will include this execution in a transaction. The SQL commands will be contained in the following [sql2.txt] file:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
select * from persons
select last_name,first_name from persons order by last_name asc, first_name desc
select * from persons where age between 20 and 40 order by age desc, last_name asc, first_name asc
insert into persons values('Josette','Bruneau',46)
update persons set age=47 where last_name='Bruneau'
select * from persons where last_name='Bruneau'
delete from persons where last_name='Bruneau'
select * from persons where last_name='Bruneau'
insert into persons values('Josette','Bruneau',46)
select * from persons where last_name='Bruneau'
xselect * from persons where last_name='Bruneau'

The erroneous command on line 11 will cause the entire transaction to fail. The database should therefore revert to its state prior to the transaction. In the example above, the row inserted by line 9 should not appear in the table. The script differs very little from the [mysql-04.php] script. The name of the SQL command file becomes [__DIR__ . "/sql2.txt"], and the [executeCommands] function loses its fourth parameter:

22
23
// execution of the file of SQL statements
$errors = executeCommands($connection, SQL_COMMANDS_FILENAME, tracking: true);

This is because, since the SQL commands run within a transaction, any error will cause the transaction to terminate. The [executeCommands] function becomes the following:

35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
function executeCommands(PDO $connection, string $sqlFileName, bool $tracking = false): array
{
    // uses the connection $connection
    // executes the SQL commands contained in the text file $sqlFileName
    // this file is a file of SQL commands to execute, one per line
    // if $tracking is true, each execution of an SQL statement is displayed with its success or failure
    // the SQL commands are executed in a transaction: if one of them fails, the transaction is rolled back
    // and the database is found in the state it was in before the transaction
    // the function returns the array of error messages (empty if there was no error)

    // we put the lines of the file into an array, without the end-of-line marks or the empty lines
    $queries = is_readable($sqlFileName)
        ? file($sqlFileName, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES)
        : false;
    // error?
    if ($queries === false) {
        return ["The file [$sqlFileName] does not exist or could not be read"];
    }
    // the queries are going to be executed in a transaction
    $connection->beginTransaction();
    // we execute the queries one by one - no errors at the start
    $errors = [];
    foreach ($queries as $query) {
        // we remove the spaces at the beginning and end of the line
        $query = trim($query);
        // empty query?
        if ($query === "") {
            // we move on to the next query
            continue;
        }
        // the name of the SQL command: the first word of the query, in lowercase
        $command = strtolower(explode(" ", $query, 2)[0]);
        try {
            // execution of the query - an exception is thrown in case of error
            // a SELECT statement returns rows, the other statements a number of modified rows
            $result = $command === "select"
                ? $connection->query($query, PDO::FETCH_NUM)
                : $connection->exec($query);
            // screen tracking or not?
            if ($tracking) {
                print "[$query]: Successful execution\n";
            }
            // we display the result of the execution
            displayInfo($command, $result);
        } catch (PDOException $ex) {
            // an error occurred
            addError($errors, $query, $ex->getMessage(), $tracking);
            // we stop at the 1st error
            break;
        }
    }
    // end of the transaction
    if ($errors === []) {
        // there were no errors: we commit the transaction
        $connection->commit();
    } else {
        // there was an error: we roll back the transaction
        $connection->rollBack();
        // adding an error
        addError($errors, "", "Transaction rolled back", $tracking);
    }
    // result
    return $errors;
}

Comments

  • Lines 41–42: Reminder of the role of a transaction;
  • line 54: a transaction is started using the [PDO::beginTransaction] method. From this point on, any SQL command executed within the [foreach] loop is executed within this transaction;
  • lines 82–83: Upon the first error, the loop is exited: there is no point in executing the subsequent commands since the transaction will be rolled back;
  • lines 87–95: The array [$errors] is empty if no error occurred. The transaction is then committed using the method [PDO::commit]: the changes made by the SQL commands become permanent. Otherwise, the transaction is rolled back using method [PDO::rollBack]: the changes made by the SQL commands in the transaction are undone. The transaction error is then added to the error list.

Before the [mysql-07.php] script is executed, the [persons] table contains the ten rows inserted by the [mysql-06.php] script:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
> mysql -u admpersons -pnobody dbpersons -e "select * from persons"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+-----------+-----+
| first_name | last_name | age |
+------------+-----------+-----+
| firstName0 | lastName0 |   0 |
| firstName1 | lastName1 |  10 |
| firstName2 | lastName2 |  20 |
| firstName3 | lastName3 |  30 |
| firstName4 | lastName4 |  40 |
| firstName5 | lastName5 |  50 |
| firstName6 | lastName6 |  60 |
| firstName7 | lastName7 |  70 |
| firstName8 | lastName8 |  80 |
| firstName9 | lastName9 |  90 |
+------------+-----------+-----+

Run the [mysql-07.php] script. The screen displays are as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
> php mysql-07.php
[select * from persons]: Successful execution
first_name,last_name,age
------------------------
firstName0,lastName0,0
firstName1,lastName1,10
firstName2,lastName2,20
firstName3,lastName3,30
firstName4,lastName4,40
firstName5,lastName5,50
firstName6,lastName6,60
firstName7,lastName7,70
firstName8,lastName8,80
firstName9,lastName9,90
[select last_name,first_name from persons order by last_name asc, first_name desc]: Successful execution
last_name,first_name
--------------------
lastName0,firstName0
lastName1,firstName1
lastName2,firstName2
lastName3,firstName3
lastName4,firstName4
lastName5,firstName5
lastName6,firstName6
lastName7,firstName7
lastName8,firstName8
lastName9,firstName9
[select * from persons where age between 20 and 40 order by age desc, last_name asc, first_name asc]: Successful execution
first_name,last_name,age
------------------------
firstName4,lastName4,40
firstName3,lastName3,30
firstName2,lastName2,20
[insert into persons values('Josette','Bruneau',46)]: Successful execution
 1 row(s) has (have) been modified
[update persons set age=47 where last_name='Bruneau']: Successful execution
 1 row(s) has (have) been modified
[select * from persons where last_name='Bruneau']: Successful execution
first_name,last_name,age
------------------------
Josette,Bruneau,47
[delete from persons where last_name='Bruneau']: Successful execution
 1 row(s) has (have) been modified
[select * from persons where last_name='Bruneau']: Successful execution
first_name,last_name,age
------------------------
[insert into persons values('Josette','Bruneau',46)]: Successful execution
 1 row(s) has (have) been modified
[select * from persons where last_name='Bruneau']: Successful execution
first_name,last_name,age
------------------------
Josette,Bruneau,46
[xselect * from persons where last_name='Bruneau']: Error (SQLSTATE[42000]: Syntax error or access violation: 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 'xselect * from persons where last_name='Bruneau'' at line 1)
[]: Error (Transaction rolled back)

-----------------------
There were 2 error(s)
[xselect * from persons where last_name='Bruneau']: Error (SQLSTATE[42000]: Syntax error or access violation: 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 'xselect * from persons where last_name='Bruneau'' at line 1)
[]: Error (Transaction rolled back)
Done
  • Lines 47–52: The row [Josette, Bruneau, 46] was successfully inserted into the table within the transaction;
  • Line 53: An error occurs on command [xselect];
  • Line 54: The transaction is then rolled back.

If we check the database status, we find it in the same state as before the script was run. In particular, we do not see the [Josette, Bruneau, 46] row from line 52 of the results above:

1
2
3
4
5
6
7
> mysql -u admpersons -pnobody dbpersons -e "select count(*) from persons where last_name='Bruneau'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+
| count(*) |
+----------+
|        0 |
+----------+

Summary:

  • A transaction begins with method [PDO::beginTransaction];
  • it is successfully completed using the [PDO::commit] method;
  • it is terminated upon failure using the [PDO::rollBack] method;
  • the [PDO::inTransaction] method indicates whether a transaction is in progress.

When working with a database, it is good practice to place any SQL operation within a transaction to isolate it from other database users (this is also one of its purposes). A transaction should be as short as possible. Therefore, be sure to end it with a [commit] or a [rollBack], as appropriate. Finally, please note: with MySQL, commands that modify the database structure ([create table], [drop table], etc.) cannot be rolled back. They implicitly commit the current transaction. This is why the [sql2.txt] file contains only [select], [insert], [update], and [delete] commands.

https://stahe.github.io
This course, written by Anthropic's AI Claude at the request of Serge Tahé, is made available to the public under the terms of the Creative Commons Attribution – No Commercial Use – ShareAlike 3.0 Unported.