PHP and database with PDO

The content here is under the Attribution 4.0 International (CC BY 4.0) license

It is assumed that you already have basic knowledge of SQL. The focus here will be on how to use SQL in conjunction with PDO. If you are not comfortable with SQL, going through an introduction of SQL is recommended.

As we know it is difficult to find material on pure database-independent SQL, we advise you to read about different database implementations. In short: don’t just stick to MySQL or Postgres, try reading about the implementations, whether from Oracle, SQLite or Firebase. This way, you will be able to understand the differences between these databases.

PDO (PHP Data Object)

As of PHP 5.5, the mysql_ family of functions was considered deprecated (and removed in version 7.0), providing an alternative to using the mysqli_ functions for applications that use the MySQL database. However, the The pg_ family of functions has not undergone any changes, good news for those who use the Postgres database.

Of course, if you want to use the Oracle database, there is no problem, just use the oci_ family of functions, which is provided by the Oracle OCI8 extension. So far, we have not had any reports of questions related to the OCI8 extension, but we recommend that you take a look at its features to carry out a more confident test.

Imagine the following scenario: you want to change databases without having to worry about your application. If you are using any family of functions, be it mysqli_, oci_ or pg_, you will be forced to change all of your source code to support the new database.

Thinking about this type of problem, PHP came up with a solution called PDO (PHP Data Object), which provides the facility and abstraction so that, regardless of the database, the change of manufacturer is transparent, both from Oracle to MySQL and from Postgres for MySQL.

Of course, this transparent exchange is completely related to the way your application was developed. If it was completely developed with MySQL instructions (such as UPDATE, SELECT, GROUP BY), you will have to change your entire application. In addition to using PDO, it is also necessary to think about how to carry out SQL statements in a generic way for all databases.

Connecting and using PDO

As PDO is a class, we must instantiate it to have access to its methods.

$pdo = new PDO('mysql:dbname=database;host=127.0.0.1', 'user', 'password');

The first thing we should notice is the connection string. The first parameter passed to the class constructor. This is where we define which database we are going to use (MySQL, Oracle, Postgres, etc.), the name of the database and its location (the IP where it is located, also known as host).

Below is a table with the supported drivers and database for use with PDO:

You can also find out which drivers your PHP installation supports using the static getAvailableDrivers method.

$drivers = \PDO::getAvailableDrivers();

print_r($drivers);

When running this code, in my PHP installation, we support the following databases: MySQL, Postgres and SQlite. But don’t worry if yours is different, as this depends on how PHP was installed on your machine.

Array
(
    [0] => mysql
    [1] => pgsql
    [2] => sqlite
)

In addition to these mandatory options, when creating the PDO object, as a last parameter we can define some options, for example, how PDO will behave when an error occurs:

$dsn = 'mysql:dbname=database;host=127.0.0.1';
$user = 'root';
$password = '123456';

$pdo = new \PDO($dsn, $user, $password, [
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION
]);

The default value for the ATTR_ERRMODE attribute is ERRMODE_SILENT, and this mode does not display any type of error to the user. Instead, the error information is set internally to be retrieved later.

As the third parameter is optional, the PDO class provides us with a setAttribute method to set these options after creating the object. This makes the class very dynamic, as we can read these options from a file, and then set them.

$dsn = 'mysql:dbname=database;host=127.0.0.1';
$user = 'root';
$password = '123456';

$pdo = new PDO($dsn, $user, $password);
$pdo->setAtributte(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

An important detail about using errors with PDO is that an exception will always be thrown if it is not possible to connect to the database, regardless of the error mode defined in the ATTR_ERRMODE attribute.

For a complete list of possible constants used in PDO options, see the official documentation at http://php.net/manual/pdo.constants.php.

It is also possible to return the value that a given attribute has through the getAttribute method:

print $pdo->getAttribute(\PDO::ATTR_ERRMODE);

As in the previous example we set our error mode to ERRMODE_EXCEPTION, the result we get when running the script is 2 (the value of the constant ERRMODE_EXCEPTION). Let’s modify it now so that, instead of using exception mode, we use silent mode:

$dsn = 'mysql:dbname=database;host=127.0.0.1';
$user = 'root';
$password = '123456';

$pdo = new \PDO($dsn, $user, $password, [
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_SILENT
]);

Now that we have changed our error mode, let’s retrieve its value again:

print $pdo->getAttribute(\PDO::ATTR_ERRMODE);

Now, as expected, we have the result 0, as this is the value of the constant PDO::ERRMODE_SILENT.

Handling errors

With some methods, it is possible to find out if there was an error when executing a statement and apply specific treatment according to the error returned from the database.

$pdo = new \PDO($dsn, $user, $password, [
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_SILENT
]);

$pdo->query('SELECT * FROM mesa_que_nao_existe');

if ($pdo->errorCode()) {
    $details = $pdo->errorInfo();

    print sprintf(
        'Code: %s, Driver code: %s, Message: %s',
        $details[0],
        $details[1],
        $details[2],
    );
}

See that, in this example, we use the errorCode method to find out if there was an error and, shortly afterwards, we use the errorInfo method to get more information about the error.

Executing SQL

We already have a connection to the database of our choice to use PDO, now we will see how we can manipulate the data. To do this, we will look at the exec method of the PDO class. And for the examples below we will use the following table structure

CREATE TABLE `users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `email` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`));

Now that we have our table, let’s manipulate some data in it, starting with the SELECT statement:

$dsn = 'mysql:dbname=database;host=127.0.0.1';
$user = 'root';
$password = '123456';

$pdo = new \PDO($dsn, $user, $password);

print $pdo->exec('SELECT * FROM users');

If you have already had contact with the exec method, you know that, when executing the previous script, the result we will obtain is just a 0 (zero), as the exec method is not used to select data (or any other SQL statement that returns data), but only for change. In short, the exec method will return the number of rows changed by the executed SQL statement.

$pdo = new \PDO($dsn, $user, $password);

$affectedlines = $pdo->exec(
    "INSERT INTO users (name, email)
    VALUES ('pdo', '[email protected]');"
);

print $linesAfected . 'line inserted';

When we run this script, a new line will be inserted in the users table and the number 1 will be returned, as only 1 record was inserted, that is, only 1 row was affected in our table.

1 line inserted

Now that we have inserted a record into our users example table, let’s update the inserted record and change its name from pdo to PHP.

$pdo = new \PDO($dsn, $user, $password);

$affectedlines = $pdo->exec(
    "UPDATE users SET name = 'PHP'
    WHERE email = '[email protected]';"
);

print $linesAfected . 'line updated';

When we run the script, we have the following result:

1 updated line

If we run the same script again, we have a different result as no lines are affected. We must always remember that the exec method will return the number of rows affected by the SQL statement.

0 line updated

The last statement we will see for use with the exec method is the DELETE SQL statement. It is used to delete the user we created and update the previous steps.

$pdo = new \PDO($dsn, $user, $password);

$affectedlines = $pdo->exec(
    "DELETE FROM users
    WHERE email = '[email protected]';"
);

print $linesAfected . 'deleted line';

When we run the script, we have the following result:

1 line deleted

Escaping data

A very important item when executing SQL statements manually is to escape all arguments that may come from an unsafe source, such as a form that the user fills out. By escaping the arguments, we increase the security level of our application drastically, preventing SQL injection.

$email = $_POST['email'];

print 'DELETE FROM users
        WHERE email = ' . $pdo->quote($email) . ';';

Using the quote method, we guarantee that any parameters sent will be treated as a normal string, as quotes are added around the parameter.

DELETE FROM users WHERE email = '[email protected]';

It is important to be careful with the exec method, because even if the return is not what was expected, the SQL statement is executed in the database like any other.

Transactions

So far in the examples presented, all executed statements are reflected in the database immediately. But imagine a scenario in which we need to guarantee data consistency and that no error can occur during its execution, for example, the insertion of several rows into the database in a table that has a foreign key.

If any of the data we are inserting does not have the foreign key, the database will stop execution and return an error. And imagine that this occurred after 5 records were inserted into the database. With this, we have a problem, as we will have to do extra work to find where it was inserted, what data is already in the database, fix our script and run it again.

With this scenario in mind, we use transactions. With them, we guarantee that all records will be inserted successfully in a session, and only when they are all executed, we effectively apply them to the database. If something goes wrong, all changes made are undone, returning the database state to the state before the modification.

With PDO, everything is simpler. To start our transaction, we use the beginTransaction method:

$pdo = new \PDO($dsn, $user, $password);

$pdo->beginTransaction();

Using beginTransaction, we are forced to use two other methods to persist or undo changes to the database. To persist data, we use the commit method. It should be invoked only when all changes made to the database have returned successfully.

If something goes wrong during the execution of the instructions, we must use the rollback method, which will return the state of the database before all the changes were made.

Below is an example of how to use the commit method after all SQL statements have been executed successfully:

$pdo = new \PDO($dsn, $user, $password);

$pdo->beginTransaction();

// SQL statements

$pdo->commit();

Or to undo the changes (rollback), if any executed instruction does not return successfully. This time, we added a try/catch block to ensure that we only execute the rollback method if an exception is thrown:

$pdo = new \PDO($dsn, $user, $password);

$pdo->beginTransaction();

try {
    // SQL statements
} catch (\Exception $error) {
    $pdo->rollback();
}

Returning data

You may be wondering how we search for existing data in the database using PDO. To do this, we will use the query method, which introduces us to a new class used by PDO, called PDOStatement.

$pdo = new \PDO($dsn, $user, $password);

$data = $pdo->query('SELECT * FROM users');

print_r($data);

Through the PDOStatement object, returned by the query method, we have access to the records in the database.

PDOStatement Object
(
    [queryString] => SELECT * FROM users
)

To make it clearer, see the previous example, which displays which properties we have within the object returned by the query method. See also the diagram below, which exemplifies the dependencies of the PDO class:

Let’s use the following data from the users table as an example:

+----+----------+------------------------+
| id | name | email |
+----+----------+------------------------+
| 1 | Marabesi | [email protected] |
| 2 | Michael | [email protected] |
+----+----------+------------------------+

With our PDOStatement object, we can then return the data:

$sql = $pdo->query('SELECT * FROM users');

$data = $sql->fetchAll();

print_r($data);

When using the fetchAll method without any arguments, we have the following result:

Array
(
    [0] => Array
        (
            [id] => 1
            [0] => 1
            [name] => Marabesi
            [1] => Marabesi
            [email] => [email protected]
            [2] => [email protected]
        )
    [1] => Array
        (
            [id] => 2
            [0] => 2
            [name] => Michael
            [1] => Michael
            [email] => [email protected]
            [2] => [email protected]
        )
)

We have an enumerative array and, for each row in our database, an enumerative and an associative array are returned (which makes reading the data complicated, as it is being duplicated). This occurs because the default mode used to return data is PDO::FETCH_BOTH. But we can change this behavior according to the constant used. To return just an associative array, you can use the constant PDO::FETCH_ASSOC.

$sql = $pdo->query('SELECT * FROM users');

$data = $sql->fetchAll(PDO:FETCH_ASSOC);

print_r($data);

By using PDO::FETCH_ASSOC, we eliminate the enumerative array that was mixed together with the associative one, thus making it clearer what is returned from the database.

Array
(
    [0] => Array
        (
            [id] => 1
            [name] => Marabesi
            [email] => [email protected]
        )
    [1] => Array
        (
            [id] => 2
            [name] => Michael
            [email] => [email protected]
        )
)

In the official PHP documentation, you can find some interesting contributions from users about using the fetchAll method, at http://php.net/manual/pdostatement.fetch.php.

Automatically escaping arguments

A big difference when using PDO is the possibility of automatically escaping the parameters sent by the user through the prepare method.

$name = $_GET['name'];

$query = $pdo->prepare('SELECT * FROM users WHERE name = :name');

$query->execute([':name' => $name]);

$data = $query->fetch(PDO::FETCH_ASSOC);

print_r($data);

Note that, when using the prepare method, we do not need to escape argument by argument in our SQL statement, we just need to combine the prepare and execute methods for this. Note that in our SQL statement, right after the WHERE clause, we are using a token to represent what will be replaced by the real value after being properly escaped. And the same token is used as the key of the array that we pass to the execute method, which is responsible for joining our SQL statement and the values ​​passed.

We cannot escape some fields, such as table name and table field names, as PDO does not provide us with this functionality by default. This makes the use of the following syntax invalid:

$query = $pdo->prepare('SELECT * FROM :table');

$query->execute([':table' => 'users']);

And when we run this script, an error is displayed:

PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: parameter was not defined' in /my_dir/pdo.php:10
Stacktrace:
#0 /my_dir/pdo.php(10): PDOStatement->execute(Array)
#1 {main}
  thrown in /my_dir/pdo.php on line 1

Alternatively, you can create your own function to escape columns and table names if you wish. In our example, we will use a function to manually escape these fields for us.

Let’s look at the code of our function that escapes these fields for us:

function escape($argument)
{
    return preg_replace('/[^A-Za-z0-9_]+/', '', $argument);
}

This function of ours guarantees that we will only have letters and numbers using the preg_replace function to apply the regular expression /[^A-Za-z0-9_]+/. With this, we can now use this function in our SQL statement.

Note that, in addition to using our new function, we also changed where we look for the name of our table. The name will be provided by the user through the global variable $_GET. See the modified code:

$TableName = $_GET['table'];

$table = escape($TableName);

$query = $pdo->prepare("SELECT * FROM $table");

$query->execute();

No errors are displayed and the SQL statement is executed normally in our database, returning all records from the table entered by the user through the global $_GET.

This was just an example of how we could manually escape table names, but there are other ways you can use, such as the str_replace functions or the quote method of the PDO class.

Other ways of manipulating data

We also have other types of constants to use, as shown below:

• PDO::FETCH_BOUND – This mode is used in conjunction with the bindColumn method, where the result is assigned to the chosen column.
• PDO::FETCH_OBJ – Returns an array of objects.
• PDO::FETCH_CLASS – Maps the database return to the desired class.
• PDO::FETCH_INTO – Same behavior as the PDO::FETCH_CLASS mode, but it is possible to use the $this context.
• PDO::FETCH_LAZY – Returns data from the database as it is accessed.
• PDO::FETCH_NAMED – Has the same behavior as PDO::FETCH_ASSOC mode, but when there is more than one column with the same name, an array will be created with the name of the column with the values ​​repeated within that array .
• PDO::FETCH_NUM – Returns an enumerative array starting from index 0 (zero).

FETCH_BOUND

This mode is perhaps one of the most dynamic to use with PDO, as it allows you to link the value of a column to a PHP variable. In our example below, we are selecting the id, name and email columns from the users table. After that, we are linking each of these columns to the respective variables: $id, $nome and $email.

This link is what allows us to actually manipulate the content we are searching for in our database. See in the code below that we display the contents of these variables inside the while loop:

$prepare = $pdo->prepare('SELECT id, name, email FROM users');

$prepare->execute();

$prepare->bindColumn(1, $id);
$prepare->bindColumn(2, $name);
$prepare->bindColumn(3, $email);

while($prepare->fetch(\PDO::FETCH_BOUND)) {
    print sprintf('%d %s %s', $id, $name, $email);
}

Note the order in which the columns were used in the bindColumn method, as it is the same order in the SQL statement. Let’s represent this in a list to make it easier to understand:

1. id
2. name
3. email

To use the bindColumn method, it is mandatory that the value to be assigned is in a variable, as this parameter is passed by reference, which does not make it possible to use it with fixed values.

...

// valid syntax, but a `FATAL ERROR` is displayed

$prepare->bindColumn(':email', 'email');

With FETCH_BOUND, we can also use it to assign not only columns, but also value via the bindValue or bindParam method.

$email = $_GET['email'];

$prepare = $pdo->prepare('SELECT id, name, email FROM users WHERE email = :email');

// We get the same result if we use bindParam
$prepare->bindValue(':email', $email);
$prepare->execute();

print_r($prepare->fetch(\PDO::FETCH_ASSOC));

And the result we get is the user record Marabesi.

Array
(
    [id] => 1
    [name] => Marabesi
    [email] => [email protected]
)

The bindValue method is yet another way to escape arguments automatically through the prepare method.

As we saw, the bindValue and bindParam methods have basically the same function, however there are some differences between them, such as the number of arguments accepted by each one and the way in which the value is passed by each one.

FETCH_OBJ

Until now, we have only returned database data as associative/enumerative arrays. But PDO provides us with a very simple way to return objects instead of arrays, through the constant \PDO::FETCH_OBJ.

Let’s then select the id, name and email columns again, and display the results using the print_r function to see what changes.

$prepare = $pdo->prepare('SELECT id, name, email FROM users');

$prepare->execute();

print_r($prepare->fetchAll(\PDO::FETCH_OBJ));

The result we obtain is an enumerative array containing objects of type stdClass (generic class used in PHP, we can use an analogy to the Object class in Java). For each column in the database, a property is created on the object with the same name.

Array
(
    [0] => stdClass Object
        (
            [id] => 1
            [name] => Marabesi
            [email] => [email protected]
        )
    [1] => stdClass Object
        (
            [id] => 2
            [name] => Michael
            [email] => [email protected]
        )
)

We access the object’s properties as with any other object. Let’s use the foreach loop to loop through each item in our array and display the data.

foreach ($prepare->fetchAll(\PDO::FETCH_OBJ) as $usuario) {
    print $user->id;
    print $user->name;
    print $user->email;
}

FETCH_CLASS

The \PDO::FETCH_CLASS mode works in the same way as \PDO::FETCH_OBJ, however, when using \PDO::FETCH_CLASS, it is possible to specify which type of object we want PDO to return the data. For our example, we will build a class named Usuario to represent the records in the usuarios table

class User {
    public $id;
    public $name;
    public $email;
}

Now that we have our class, we can pass its name as a second parameter to the fetchAll method.

$prepare = $pdo->prepare('SELECT id, name, email FROM users');

$prepare->execute();

print_r($preparar->fetchAll(\PDO::FETCH_CLASS, 'User'));

As expected, instead of the result being an array with several objects of type stdClass (as occurs in \POD::FETCH_OBJ), we now have several objects of type Usuario.

An interesting behavior when we use \PDO::FETCH_CLASS is that we don’t necessarily need to define the class properties with the same name as the fields in the table, as PHP does this automatically for us.

Whether or not the attributes are defined in the User class, we obtain the same result as follows:

Array
(
[0] => User Object
(
[id] => 1
[name] => Marabesi
[email] => [email protected]
)
[1] => User Object
(
[id] => 2
[name] => Michael
[email] => [email protected]
)
)

FETCH_INTO

The \PDO::FETCH_INTO mode basically acts like \PDO::FETCH_CLASS, in which we can specify a class so that PDO returns the database records of the type we want. See the following example where we tell PDO to return records from the User database.

$prepare = $pdo->prepare('SELECT id,nome,email FROM users');

$prepare->setFetchMode(\PDO::FETCH_INTO, new Usuario());

$prepare->execute();

print_r($prepare->fetchAll());

We only have to pay attention to a few differences. Notice the new way in which we define how PDO will return data to us. This time, we use the setFetchMode method, where we inform the type we want in the first parameter and, as a second parameter, the objects we want it to return. Note that the objects within the array are of type User. This way, we have the following result:

Array
(
    [0] => User Object
        (
            [id:User:private] => 1
            [name:User:private] => Marabesi
            [email:User:private] => [email protected]
        )
    [1] => User Object
        (
            [id:User:private] => 2
            [name:User:private] => Michael
            [email:User:private] => [email protected]
        )
)

With the \PDO::FETCH_INTO mode, we must obligatorily use the setFetchMode method; otherwise an exception will be thrown:

PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: No fetch-into object
specified.' in /my_dir/pdo/fetch_into.php:16
Stacktrace:
#0 /my_dir/pdo/fetch_into.php(16): PDOStatement->fetchAll(9)
#1 {main}
thrown in /my_dir/pdo/fetch_into.php on line 16

The difference between using \PDO::FETCH_CLASS or \PDO::FETCH_INTO is the use of $this. While \PDO::FETCH_INTO allows us to inform that we want PDO to return the type of the current instance of the object (through $this), FETCH_CLASS only allows us to inform strings as a second parameter.

// Using FETCH_CLASS
$prepare->setFetchMode(\PDO::FETCH_CLASS, 'User');

// Using FETCH_INTO
$prepare->setFetchMode(\PDO::FETCH_INTO, $this);

If we try to use any type of argument other than string with the FETCH_CLASS mode, an exception will be thrown.

$prepare->setFetchMode(\PDO::FETCH_CLASS, new Usuario());

$prepare->setFetchMode(\PDO::FETCH_CLASS, $this);

Both previous examples will display the following error. The \PDO::FETCH_CLASS mode only accepts the string type as a second parameter.

PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: classname must be a string' in /my_dir/pdo/fetch_into.php:14
Stacktrace:
#0 /my_dir/pdo/fetch_into.php(14): PDOStatement->setFetchMode(8, Object(User))
#1 {main}
  thrown in /my_dir/pdo/fetch_into.php on line 14

However, when using the \PDO::FETCH_INTO mode, we have great flexibility, as we can use $this to reference the very class we are using to retrieve the data, without creating any auxiliary class to represent the records.

class User {
    private $pdo;

    public function __construct()
    {
        // connect to the database
        // $this->pdo = new PDO(..)
    }

    public function getUsers()
    {
        $prepare = $this->pdo->prepare(
            'SELECT id, name, email FROM users'
        );

        $prepare->setFetchMode(\PDO::FETCH_INTO, $this);

        $prepare->execute();

        return $prepare->fetch();
    }
}

$entity = new User();
print_r($entidade->getUsuarios());

In the constructor method, we connect to the database so that it is possible to retrieve the data in the getUsuarios method, and it is this method that interests us, where we use \PDO::FETCH_INTO.

See that we pass $this as the second parameter, which means the current instance of the object, which in our case is the User object. This makes PDO populate an array with database records of type User. See the result we got:

Object User
(
    [pdo:User:private] => PDO Object
        (
        )

    [id] => 1
    [name] => Marabesi
    [email] => [email protected]
)

We only obtain data from the user Marabesi, as we use the fetch method, which returns the first row found in the database.

FETCH_LAZY

\PDO::FETCH_LAZY is a combination of \PDO::FETCH_BOTH and \PDO::FETCH_CLASS modes. With this, we can access the returned data as associative arrays (informing column name), enumerative arrays (where each column represents a number starting from zero) or as an object. In our example, we use the three ways of accessing data and obtain the same result:

$prepare = $pdo->prepare(
            'SELECT id, name, email FROM users'
        );

$prepare->execute();

$result = $prepare->fetch(\PDO::FETCH_LAZY);

print $result['id']; // 1
print $result[0]; // 1
print $result->id; // 1

Furthermore, the result returned from the database is encapsulated in a PDORow object, which is only produced at the time it is accessed. Therefore, its name contains the word LAZY, which means lazy. In other words, records are only actually returned when they are accessed.

PDORow Object
(
    [queryString] => SELECT id, name, email FROM users
    [id] => 1
    [name] => Marabesi
    [email] => [email protected]
)

An important detail is that we are unable to use the fetchAll method in conjunction with the \PDO::FETCH_LAZY mode, as shown in the following code, in which we exchange the fetch method for fetchAll.

$prepare = $pdo->prepare(
            'SELECT id, name, email FROM users'
        );

$prepare->execute();

$result = $prepare->fetchAll(\PDO::FETCH_LAZY);

When we try to run this script, a FATAL ERROR is displayed, informing us that it is not possible to use this method:

PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: PDO::FETCH_LAZY can't be used with PDOStatement::fetchAll()' in /my_dir/pdo/fetch_lazy.php:13
Stacktrace:
#0 /my_dir/pdo/fetch_lazy.php(13): PDOStatement->fetchAll(1)
#1 {main}
  thrown in /my_dir/pdo/fetch_lazy.php on line 13

This type of error is a defense by PHP to comply with the “lazy” mode that it was set to work in, when choosing the \PDO::FETCH_LAZY mode. Lazy mode works according to demand, only the requested data will be accessed.

Using the fetchAll method breaks this mode, as it would return all records at once, making the use of lazy mode meaningless.

FETCH_NAMED

For this example, we will need to create a new table named permissao to relate to our users table:

CREATE TABLE IF NOT EXISTS `permission` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `usuarios_id` INT(11) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_usuarios_id` (`usuarios_id` ASC),
  CONSTRAINT `fk_usuarios_id`
    FOREIGN KEY (`usuarios_id`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 1;

Now we have a new table called permissao, with a foreign key to the usuarios table, since, for our example, a user can have one or more permissions. And to proceed, let’s insert two records into the permission table:

INSERT INTO `permissao` (`name`, `usuarios_id`) VALUES ('Administrative module', '1');
INSERT INTO `permissao` (`name`, `usuarios_id`) VALUES ('Financial module', '1');

If you haven’t noticed yet, we have a column with the same name in the users table and in the permission table. See the columns and their values ​​below, and notice the name column that appears twice: the first with the user name and the second with the name of the permission.

+----------+---------------------------+------------- ----------+
| name | name | email |
+----------+---------------------------+------------- ----------+
| Marabesi | Administrative module | [email protected] |
| Marabesi | Financial module | [email protected] |
+----------+---------------------------+------------- ----------+

The SQL statement executed was as follows:

SELECT u.name, p.name, u.email
FROM users AS u
INNER JOIN permission AS p on u.id = p.usuarios_id;

But what does the \PDO::FETCH_NAMED mode have to do with all this? Very simple, when using the \PDO::FETCH_NAMED mode columns with the same name, an array is created with the name of the column (in our case name) with the values ​​repeated within this array:

$prepare = $pdo->prepare(
    'SELECT u.name, p.name, u.email
    FROM users AS u
    INNER JOIN permission AS p on u.id = p.usuarios_id;'
);

$prepare->execute();

print_r($prepare->fetchAll(\PDO::FETCH_NAMED));

When executing the script, we have an array with the key name and, within it, the repeated values.

Array
(
    [0] => Array
        (
            [name] => Array
                (
                    [0] => Marabesi
                    [1] => Administrative module
                )
            [email] => [email protected]
        )
    [1] => Array
        (
            [name] => Array
                (
                    [0] => Marabesi
                    [1] => Financial module
                )

            [email] => [email protected]
        )
)

If we do not use \PDO::FETCH_NAMED and use \PDO::FETCH_ASSOC, the value that would be returned in the name column would be the value from the permission table, as they appear last in the SQL statement.

FETCH_NUM

As you may have deduced from the name, with the \PDO::FETCH_NUM mode an enumerative array of each row in the database is returned.

$prepare = $pdo->prepare('SELECT id, name, email FROM users');

$prepare->execute();

print_r($prepare->fetchAll(\PDO::FETCH_NUM));

And the result we get when we select all records from the users table is the following:

Array
(
    [0] => Array
        (
            [0] => 1
            [1] => Marabesi
            [2] => [email protected]
        )

    [1] => Array
        (
            [0] => 2
            [1] => Michael
            [2] => [email protected]
        )
)

Don’t pay attention to the implementation, but to the language

The main tip when studying relational databases for PHP is not to worry about the specific implementation, such as MySQL, MySQL Server, Oracle, among others. But worry about the SQL language, which is independent of who provides the technology.

Focus on basic commands such as SELECT, UPDATE, DELETE, CREATE TABLE, and on some not so popular features, such as PROCEDURES, TRIGGERS and, finally, on creating indexes with the database . Rarely, some questions about database administration may appear (based on the simulations), but then it is up to you to decide whether to delve deeper into this topic or not.

Furthermore, the test covers a lot of your knowledge about PDO itself. So, in addition to the topics shown here, take a look at the official documentation, at http://php.net/manual/class.pdo.php. There you can find valuable user contributions.

Living without a database?

In current applications, it would be somewhat complicated, but not impossible, to live without a database. In this chapter, we try to cover all items related to the use of databases with the PDO. But not only that. Pay attention to the functions that PHP provides in addition to PDO, such as pg_connect, which is used to specifically connect to the Postgres database, and the Mysqli class (http://php.net/manual/book.mysqli.php) which replaced the mysql_* family of functions that relate only to the MySQL database.

In addition to the specific features of PHP, SQL statements, the universal language of relational databases, are very important. In the test, they can (and probably will) ask about basic routines, such as how to carry out queries, manipulate data, etc. There will also be the famous gotchas: find the error in the following SQL statement (although, sometimes, the statement is perfectly correct). For these reasons and others, take the time to just unravel the mysteries of SQL.