pdo methods. Why should you use PDO to access databases? Result processing, FETCH and FETCHALL methods

Term PDO is an abbreviation for the concept PHP Data Objects. As the name suggests, this technology allows you to work with database contents through objects.

Why not myqli or mysql?

Most often, with regard to new technologies, the question arises of their advantages over the good old and proven tools, as well as the transfer of current and old projects to them.

Object Orientation PDO

PHP It is developing very actively and strives to become one of the best tools for the rapid development of web applications, both mass and corporate level.

Talking about PHP, we mean modern object-oriented PHP, allowing you to write universal code that is convenient for testing and reuse.

Usage PDO allows you to move database work to an object-oriented level and improve code portability. In fact, the use PDO not as difficult as one might think.

Abstraction

Let's imagine that we have been developing an application for a long time using MySQL. And then, at one fine moment, it becomes necessary to replace MySQL on PostgreSQL.

At a minimum, we will have to replace all calls mysqli_connect() (mysql_connect()) on pg_connect() and, by analogy, other functions used to query and process data.

Using PDO, we will limit ourselves to changing a few parameters in the configuration files.

Parameter binding

Using linked parameters provides greater flexibility in query design and improves protection against SQL injections.

Receiving data as objects

Those who are already using ORM(object-relational mapping - object-relational mapping of data), for example, Doctrine, know the convenience of representing data from database tables in the form of objects. PDO allows you to receive data in the form of objects and without using ORM.

The mysql extension is no longer supported

Extension support mysql permanently removed from new PHP 7. If you plan to migrate the project to a new version PHP, you should use at least mysqli in it now. Of course, it's better to start using PDO if you haven't already done so.

It seems to me that these reasons are sufficient to tip the scales in favor of using PDO. Moreover, you don’t need to install anything additional.

Checking the presence of PDO in the system

Versions PHP 5.5 and higher, most often, already contain an extension for working with PDO. To check, just run a simple command in the console:

php -i | grep "pdo"

Now let’s open it in any browser and find the necessary data by searching by line PDO.

Getting to know PDO

Process of working with PDO not too different from the traditional one. In general, the process of using PDO looks like that:

  1. Connect to the database;
  2. If necessary, prepare a request and link parameters;
  3. Executing the request.

Connecting to the database

To connect to the database you need to create a new object PDO and pass it the name of the data source, also known as DSN.

In general, DSN consists of the driver name separated by a colon from a connection string specific to each driver PDO.

For MySQL, the connection is made like this:

$connection = new PDO("mysql:host=localhost;dbname=mydb;charset=utf8", "root", "root");

$connection = new PDO ( "mysql:host=localhost;dbname=mydb;charset=utf8", "root" , "root" ) ;

In this case, DSN contains driver name mysql, host indication (possible format host=HOST_NAME:PORT), database name, encoding, username MySQL and his password.

Requests

Unlike mysqli_query(), V PDO there are two types of requests:

  • Returning the result ( select, show);
  • Not returning a result ( insert, detail and others).

First of all, let's consider the second option.

Executing queries

Let's look at an example of executing a request using the example insert.

$connection->exec("INSERT INTO users VALUES (1, "somevalue"");

$connection -> exec () ;

Of course, this query returns the number of affected rows and you can see it as follows.

$affectedRows = $connection->exec("INSERT INTO users VALUES (1, "somevalue""); echo $affectedRows;

$affectedRows = $connection -> exec ( "INSERT INTO users VALUES (1, "somevalue"") ;

echo $affectedRows ;

Getting Query Results

In case of use mysqli_query(), the code could be as follows.

$result = mysql_query("SELECT * FROM users"); while($row = mysql_fetch_assoc($result)) ( echo $row["id"] . " " . $row["name"]; )

$result = mysql_query ("SELECT * FROM users" ) ;

while ($row = mysql_fetch_assoc ($result) ) (

For PDO, the code will be simpler and more concise.

foreach($connection->query("SELECT * FROM users") as $row) ( echo $row["id"] . " " . $row["name"]; )

foreach ($connection -> query ("SELECT * FROM users") as $row ) (

echo $row [ "id" ] . " " . $row["name"];
Data acquisition modes

As in mysqli, PDO allows you to receive data in different modes. To determine the mode, class PDO contains the corresponding constants.

  • PDO::FETCH_ASSOC— returns an array indexed by the name of the column in the database table;
  • PDO::FETCH_NUM— returns an array indexed by column number;
  • PDO::FETCH_OBJ- returns an anonymous object with property names corresponding to the column names. For example, $row->id will contain the value from the id column.
  • PDO::FETCH_CLASS— returns a new instance of the class, with property values ​​corresponding to the data from the table row. If the parameter is specified PDO::FETCH_CLASSTYPE(For example PDO::FETCH_CLASS | PDO::FETCH_CLASSTYPE

), the class name will be determined from the value of the first column. Note

: This is not a complete list; all possible constants and their combinations are available in the documentation.

An example of obtaining an associative array:

$statement = $connection->query("SELECT * FROM users"); while($row = $statement->fetch(PDO::FETCH_ASSOC)) ( echo $row["id"] . " " . $row["name"]; )

$statement = $connection ->

echo $row [ "id" ] . " " . $row["name"];

), the class name will be determined from the value of the first column. while ($row = $statement -> fetch (PDO::FETCH_ASSOC) ) ( : It is recommended to always specify the sampling mode because the mode PDO::FETCH_BOTH

will require twice as much memory - in fact, two arrays will be created, associative and regular. PDO::FETCH_CLASS Consider using sampling mode . Let's create a class:

User

class User ( protected $id; protected $name; public function getId() ( return $this->id; ) public function setId($id) ( $this->id = $id; ) public function getName() ( return $this->name; ) public function setName($name) ( $this->name = $name; ) )

class User

protected $id ;

protected $name ;

public function getId()

return $this -> id ;

public function setId ($id)

$this -> id = $id ;

public function getName()

return $this -> name ;

public function setName ($name)

$this -> name = $name ;

Now let's select the data and display the data using class methods:

$statement = $connection->query("SELECT * FROM users"); while($row = $statement->fetch(PDO::FETCH_CLASS, "User")) ( echo $row->getId() . " " . $row->getName(); )

$statement = $connection -> query ("SELECT * FROM users" ) ;

while ($row = $statement -> fetch (PDO::FETCH_CLASS, "User") ) (

echo $row -> getId() . " " . $row -> getName () ;

Prepared queries and parameter binding PDO To understand the essence and all the benefits of parameter binding, you need to take a closer look at the mechanisms . When calling$statement -> query() PDO will prepare a request, execute it and return the result.

When calling $connection -> prepare() a prepared request is created. Prepared queries are the ability of a database management system to receive a query template, compile it, and execute it after retrieving the values ​​of the variables used in the template. Template engines work in a similar way. Smarty And Twig.

When calling $statement -> execute() values ​​for substitution are transferred to the query template and the DBMS executes the query. This action is similar to calling the template engine function render().

An example of using prepared queries in PHP PDO:

In the code above, a request for selecting a record with a field is prepared id equal to the value that will be substituted for : id. At this stage, the DBMS will analyze and compile the request, possibly using caching (depending on the settings).

Now you need to pass the missing parameter and execute the request:

$id = 5; $statement->execute([ ":id" => $id ]);

Benefits of using linked parameters

Perhaps, after considering how prepared queries work and the associated parameters, the benefits of using them become obvious.

PDO provides a convenient way to escape user data, for example, code like this is no longer needed:

Instead, it now makes sense to do this:

You can even shorten the code further by using numbered parameters instead of named ones:

At the same time, using prepared queries improves performance when using the same query template multiple times. An example of selecting five random users from a database:

$numberOfUsers = $connection->query("SELECT COUNT(*) FROM users")->fetchColumn(); $users = ; $statement = $connection->prepare("SELECT * FROM users WHERE id = ? LIMIT 1"); for ($i = 1; $i<= 5; $i++) { $id = rand(1, $numberOfUsers); $users = $statement->execute([$id])->fetch(PDO::FETCH_OBJ); )

$numberOfUsers = $connection -> query ("SELECT COUNT(*) FROM users" ) -> fetchColumn () ;

$users = ;

for ($i = 1 ; $i<= 5 ; $i ++ ) {

$id = rand(1, $numberOfUsers);

$users = $statement -> execute ([ $id ] ) -> fetch (PDO::FETCH_OBJ ) ;

When calling a method prepare(), the DBMS will analyze and compile the request, using caching if necessary. Later in the cycle for, only data is sampled with the specified parameter. This approach allows you to retrieve data faster, reducing application running time.

When getting the total number of users in the database, the method was used fetchColumn(). This method retrieves the value of a single column and is useful when retrieving scalar values ​​such as count, sum, maximum, or minimum values.

Bound Values ​​and the IN Operator

Often, when starting to work with PDO, difficulties arise with the operator IN. For example, imagine that the user enters several names separated by commas. User input is stored in a variable $names.

Database Connection is set when an instance of the PDO class is created. It doesn't matter which driver you choose to use; You will always need to use the PDO class. Its constructor accepts parameters for specifying the database source (known as a DSN), and optional parameters for a username and password.

Connection to MySQL:

$dbh = new PDO("mysql:host=localhost;dbname=test", $user, $pass);

If any connection errors occur, an exception will be thrown: an object of the PDOException class. You can catch it if you want to handle this situation, or you can leave it for the global exception handler, which is set via set_exception_handler().

Handling connection errors:

try ( $dbh = new PDO("mysql:host=localhost;dbname=test", $user, $pass); foreach($dbh->query('SELECT * from FOO') as $row) ( print_r($ row); ) $dbh = null; ) catch (PDOException $e) ( die("Error! That's it. Here we are... ".$e->getMessage()); )

Attention: If you don't catch the exception thrown by the PDO constructor, the default action taken by the zend engine is to stop the script and show a traceback. This crap will reveal all your intimate details of communication with the database. That is will show detailed database connection details including username and password! It is up to you to catch this exception, either explicitly (via a statement try catch), or implicitly via set_exception_handler().

Once a database connection is successful, it remains active for the entire life of the PDO object instance. To close a connection, you must destroy the object, ensuring that all remaining references to it are removed - this can be done by assigning the value NULL to the variable that contains the object. If you don't do this explicitly, PHP will automatically close the connection when the script exits.

Closing a connection:

$dbh = new PDO("mysql:host=localhost;dbname=test", $user, $pass);

Many web applications benefit from creating persistent connections to database servers. Persistent connections are not closed when the script exits, but are cached and reused when another script requests connections using the same connection credentials. A persistent connection cache avoids the overhead of establishing a new connection every time a script needs to communicate with the database, resulting in web applications running faster.

Setting up a permanent connection:

$dbh = new PDO("mysql:host=localhost;dbname=test", $user, $pass, array(PDO::ATTR_PERSISTENT => true));

Keep in mind: If you want to use a persistent connection, you must set PDO::ATTR_PERSISTENT in the driver options array, which is passed to the PDO class constructor. By setting this attribute via PDO::setAttribute() after the object is instantiated, the driver will not use persistent links. And also, in this case, you will not be able to extend the PDOStatement class for some of your needs, i.e. it will not be possible to install: PDO::ATTR_STATEMENT_CLASS

  • Translation

Many PHP developers are accustomed to using the mysql and mysqli extensions to work with databases. But since version 5.1 in PHP there is a more convenient way - PHP Data Objects. This class, called PDO for short, provides methods for working with objects and prepared statements that will significantly improve your productivity!

Introduction to PDO

“PDO – PHP Data Objects is a layer that offers a universal way to work with multiple databases.”

It leaves the concern for the syntax features of various DBMSs to the developer, but makes the process of switching between platforms much less painful. Often this only requires changing the database connection string.


This article is written for people who use mysql and mysqli to help them migrate to the more powerful and flexible PDO.

DBMS support

This extension can support any database management system for which a PDO driver exists. At the time of writing, the following drivers are available:
  • PDO_CUBRID (CUBRID)
  • PDO_DBLIB (FreeTDS / Microsoft SQL Server / Sybase)
  • PDO_FIREBIRD (Firebird/Interbase 6)
  • PDO_IBM (IBM DB2)
  • PDO_INFORMIX (IBM Informix Dynamic Server)
  • PDO_MYSQL (MySQL 3.x/4.x/5.x)
  • PDO_OCI (Oracle Call Interface)
  • PDO_ODBC (ODBC v3 (IBM DB2, unixODBC and win32 ODBC))
  • PDO_PGSQL (PostgreSQL)
  • PDO_SQLITE (SQLite 3 and SQLite 2)
  • PDO_SQLSRV (Microsoft SQL Server)
  • PDO_4D (4D)
However, not all of them are on your server. You can see the list of available drivers like this:
print_r(PDO::getAvailableDrivers());

Connection

Methods for connecting to different DBMSs may differ slightly. Below are examples of connecting to the most popular ones. You will notice that the first three have identical syntax, unlike SQLite.
try ( # MS SQL Server and Sybase via PDO_DBLIB $DBH = new PDO("mssql:host=$host;dbname=$dbname", $user, $pass); $DBH = new PDO("sybase:host=$host ;dbname=$dbname", $user, $pass); # MySQL via PDO_MYSQL $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass); # SQLite $DBH = new PDO("sqlite:my/database/path/database.db"); catch(PDOException $e) ( echo $e->getMessage(); )
Please pay attention to the try/catch block - it is always worth wrapping all your PDO operations in it and using the exception mechanism (more on this later).

$DBH stands for “database handle” and will be used throughout the article.

You can close any connection by redefining its variable to null.
# closes the connection $DBH = null;
More information on the topic of distinctive options of different DBMSs and methods of connecting to them can be found on php.net.

Exceptions and PDO

PDO can throw exceptions on errors, so everything should be in a try/catch block. Immediately after creating a connection, PDO can be put into any of three error modes:
$DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT); $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING); $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
But it is worth noting that an error when trying to connect will always throw an exception.

PDO::ERRMODE_SILENT

This is the default mode. You'll likely use roughly the same thing to catch errors in the mysql and mysqli extensions. The next two modes are more suitable for DRY programming.

PDO::ERRMODE_WARNING

This mode will cause a standard Warning and allow the script to continue executing. Convenient for debugging.

PDO::ERRMODE_EXCEPTION

In most situations, this type of script execution control is preferable. It throws an exception, allowing you to cleverly handle errors and hide sensitive information. Like, for example, here:
# connect to the database try ( $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass); $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION) ; # Damn! I typed DELECT instead of SELECT! $DBH->prepare("DELECT name FROM people")->execute(); ) catch(PDOException $e) ( echo "Houston, we have problems."; file_put_contents("PDOErrors" .txt", $e->getMessage(), FILE_APPEND); )
There is a syntax error in the SQL expression that will throw an exception. We can record the details of the error in a log file and hint to the user in human language that something has happened.

Insert and Update

Inserting new data and updating existing data are among the most common database operations. In the case of PDO, this process usually consists of two steps. (The next section is all about both UPDATE and INSERT)


A trivial example of inserting new data:
# STH means "Statement Handle" $STH = $DBH->prepare("INSERT INTO folks (first_name) values ​​("Cathy")"); $STH->execute();
Actually, you can do the same thing with one exec() method, but the two-step method gives all the benefits of prepared statements. They help protect against SQL injections, so it makes sense to use them even for a one-time query.

Prepared Statements

Using prepared statements strengthens protection against SQL injections.

A Prepared statement is a pre-compiled SQL statement that can be executed repeatedly by sending only different sets of data to the server. An additional advantage is that it is impossible to carry out SQL injection through the data used in placeholders.

Below are three examples of prepared statements.
# without placeholders - the door to SQL injections is open! $STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values ​​($name, $addr, $city)"); # unnamed placeholders $STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values ​​(?, ?, ?)"); # named placeholders $STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values ​​(:name, :addr, :city)");
The first example is here for comparison only and should be avoided. The difference between nameless and named placeholders is how you pass data to prepared statements.

Unnamed placeholders

# assign variables to each placeholder, with indices from 1 to 3 $STH->bindParam(1, $name); $STH->bindParam(2, $addr); $STH->bindParam(3, $city); # insert one line $name = "Daniel" $addr = "1 Wicked Way"; $city = "Arlington Heights"; $STH->execute(); # insert another line, with different data $name = "Steve" $addr = "5 Circle Drive"; $city = "Schaumburg"; $STH->execute();
There are two steps here. On the first one, we assign variables to all placeholders (lines 2-4). Then we assign values ​​to these variables and execute the query. To send a new set of data, simply change the variable values ​​and run the request again.

If your SQL expression has many parameters, then assigning a variable to each is very inconvenient. In such cases, you can store the data in an array and pass it:
# the set of data we will insert $data = array("Cathy", "9 Dark and Twisty Road", "Cardiff"); $STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values ​​(?, ?, ?)"); $STH->execute($data);
$data will be inserted in place of the first placeholder, $data in place of the second, etc. But be careful: if your indexes are messed up, this won't work.

Named placeholders

# the first argument is the name of the placeholder # it usually starts with a colon # although it works without them $STH->bindParam(":name", $name);
Here you can also pass an array, but it must be associative. The keys should be, as you might guess, the names of the placeholders.
# the data we insert $data = array("name" => "Cathy", "addr" => "9 Dark and Twisty", "city" => "Cardiff"); $STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values ​​(:name, :addr, :city)"); $STH->execute($data);
One of the conveniences of using named placeholders is the ability to insert objects directly into the database if the property names match the parameter names. For example, you can insert data like this:
# class for a simple object class person ( public $name; public $addr; public $city; function __construct($n,$a,$c) ( $this->name = $n; $this->addr = $a ; $this->city = $c; ) # so on... ) $cathy = new person("Cathy","9 Dark and Twisty","Cardiff"); # and here's the interesting part $STH = $DBH->prepare("INSERT INTO folks (name, addr, city) values ​​(:name, :addr, :city)"); $STH->execute((array)$cathy);
Converting an object to an array during execute() causes the properties to be treated as array keys.

Data sampling



Data can be retrieved using the ->fetch() method. Before calling it, it is advisable to explicitly indicate in what form you require them. There are several options:
  • PDO::FETCH_ASSOC: returns an array with column names as keys
  • PDO::FETCH_BOTH (default): returns an array with indexes both in the form of column names and their serial numbers
  • PDO::FETCH_BOUND: assigns column values ​​to the corresponding variables specified using the ->bindColumn() method
  • PDO::FETCH_CLASS: assigns column values ​​to the corresponding properties of the specified class. If there is no property for some column, it will be created
  • PDO::FETCH_INTO: updates an existing instance of the specified class
  • PDO::FETCH_LAZY: combines PDO::FETCH_BOTH and PDO::FETCH_OBJ
  • PDO::FETCH_NUM: returns an array with keys as column numbers
  • PDO::FETCH_OBJ: returns an anonymous object with properties corresponding to the column names
In practice, you will usually need three: FETCH_ASSOC, FETCH_CLASS, and FETCH_OBJ. To specify the data format, use the following syntax:
$STH->setFetchMode(PDO::FETCH_ASSOC);
You can also set it directly when calling the ->fetch() method.

FETCH_ASSOC

This format creates an associative array with column names as indices. It should be familiar to those who use the mysql/mysqli extensions.
# since this is a regular query without placeholders, # you can immediately use the query() method $STH = $DBH->query("SELECT name, addr, city from folks"); # set the fetch mode $STH->setFetchMode(PDO::FETCH_ASSOC); while($row = $STH->fetch()) ( echo $row["name"] . "\n"; echo $row["addr"] . "\n"; echo $row["city"] . "\n" ;
The while() loop will iterate through the entire query result.

FETCH_OBJ

This type of data acquisition creates an instance of the std class for each row.
# create a query $STH = $DBH->query("SELECT name, addr, city from folks"); # select the fetch mode $STH->setFetchMode(PDO::FETCH_OBJ); # print the result while($row = $STH->fetch()) ( echo $row->name . "\n"; echo $row->addr . "\n"; echo $row->city . "\ n"; )

FETCH_CLASS

When using fetch_class, data is written to instances of the specified class. In this case, values ​​are assigned to the properties of the object BEFORE calling the constructor. If properties with names corresponding to column names do not exist, they will be created automatically (with the scope public).

If your data requires mandatory processing immediately after it is received from the database, it can be implemented in the class constructor.

For example, let's take a situation where you need to hide part of a person's residential address.
class secret_person ( public $name; public $addr; public $city; public $other_data; function __construct($other = "") ( $this->addr = preg_replace("//", "x", $this-> addr); $this->other_data = $other;
When creating an object, all lowercase Latin letters must be replaced with x. Let's check:
$STH = $DBH->query("SELECT name, addr, city from folks"); $STH->setFetchMode(PDO::FETCH_CLASS, "secret_person"); while($obj = $STH->fetch()) ( echo $obj->addr; )
If the address in the database looks like '5 Rosebud', then the output will be '5 Rxxxxxx'.

Of course, sometimes you will want the constructor to be called BEFORE assigning values. PDO allows this too.
$STH->setFetchMode(PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE, "secret_person");
Now that you have supplemented the previous example with an additional option (PDO::FETCH_PROPS_LATE), the address will not be modified, since nothing happens after the values ​​are written.

Finally, if necessary, you can pass arguments to the constructor directly when creating the object:
$STH->setFetchMode(PDO::FETCH_CLASS, "secret_person", array("stuff"));
You can even pass different arguments to each object:
$i = 0; while($rowObj = $STH->fetch(PDO::FETCH_CLASS, "secret_person", array($i))) ( // do something $i++; )

Other Useful Methods

While this article cannot (and does not attempt to) cover every aspect of working with PDO (it is a huge module!), the following few features cannot be left out without mention.
$DBH->lastInsertId();
The ->lastInsertId() method returns the id of the last inserted record. It is worth noting that it is always called on a database object (called $DBH in this article), and not on an object with an expression ($STH).
$DBH->exec("DELETE FROM folks WHERE 1"); $DBH->exec("SET time_zone = "-8:00"");
The ->exec() method is used for operations that do not return any data other than the number of records affected by them.
$safe = $DBH->quote($unsafe);
The ->quote() method places quotes in string data so that it is safe to use them in queries. Useful if you don't use prepared statements.
$rows_affected = $STH->rowCount();
The ->rowCount() method returns the number of records that participated in the operation. Unfortunately, this function did not work with SELECT queries until PHP 5.1.6. If it is not possible to update the PHP version, the number of records can be obtained like this:
$sql = "SELECT COUNT(*) FROM folks"; if ($STH = $DBH->query($sql)) ( # check the number of records if ($STH->fetchColumn() > 0) ( # do a full selection here because the data was found! ) else ( # print a message that no data satisfying the request was found) )

Conclusion

I hope this material will help some of you migrate from the mysql and mysqli extensions.


June 3, 2018 Andrey Chernyshov Translation Tutorial 1737 0

PDO is an acronym for PHP Data Objects: it is a PHP extension for working with databases using objects. One of its advantages lies in the fact that it is not directly tied to a specific database: its interface allows you to access several different environments, including: MySQL, SQLite, PostgreSQL, Microsoft SQL Server.

This guide aims to provide a complete overview of PDO and walk the reader step-by-step from creating and connecting to a database, to choosing the most appropriate retrieval methods, demonstrating how to create prepared queries and describing possible error modes.

Creating a test database and table

First of all, we will create a database:

CREATE DATABASE solar_system; GRANT ALL PRIVILEGES ON solar_system.* TO "testuser"@"localhost" IDENTIFIED BY "testpassword";

We have granted the user testuser all privileges in the solar_system database using testpassword for the password. Now let's create a table and fill it with some information:

USE solar_system; CREATE TABLE planets (id TINYINT(1) UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), name VARCHAR(10) NOT NULL, color VARCHAR(10) NOT NULL); INSERT INTO planets(name, color) VALUES("earth", "blue"), ("mars", "red"), ("jupiter", "strange");

Description of the DSN (Data Source Name) connection

Now that we have a database, we need to set the DSN. DSN stands for Data Source Name, and is a set of information needed to connect to a database, DSN is in the form of a string. The syntax differs depending on the database you need to connect to, but since we are using MySQL/MariaDB, we need to set the following:

  • Type of driver used for connection;
  • The name of the host computer on which the database is running;
  • Connection port (optional);
  • Database name;
  • Encoding (optional).

The format of the line in our case will be like this (we will store it in the $dsn variable):

$dsn = "mysql:host=localhost;port=3306;dbname=solar_system;charset=utf8";

First of all, we set the database prefix or database prefix. In this case, since we are connecting to a MySQL/MariaDB type database, we are using mysql. We then separated the prefix from the rest of the line with a colon, and each subsequent section was separated from the rest with a semicolon.

In the next two sections we specified the hostname on which the database is running and the port used for connection. If no port is specified, the default port will be used, in this case 3306. Immediately after the database name is charset .

Creating a PDO Object

Now that our DSN is ready, we will start creating the PDO object. The PDO constructor uses the DSN string as the first parameter, the database username as the second parameter, the password as the third, and an optional settings array as the fourth.

$options = [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC ]; $pdo = new PDO($dsn, "testuser", "testpassword", $options);

Settings can also be set after the object is created, using the SetAttribute() method:

$pdo->SetAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Configuring PDO for viewing errors

Let's take a look at some of the options available for PDO::ATTR_ERRMODE. These options are extremely important because they determine how PDO behaves when errors occur. Possible options:

PDO::ERRMODE_SILENT

Default option. PDO will simply throw an error code and an error message. They can be obtained using the errorCode() and errorInfo() methods.

PDO::ERRMODE_EXCEPTION

This option is, in my opinion, recommended to use. With its help, in addition to issuing an error code and information, PDO will throw a PDOException, which will interrupt the execution of the script, and it is also useful for PDO transactions (we will look at them a little later).

PDO::ERRMODE_WARNING

With this option, PDO will display an error code and message just like PDO::ERRMODE_SILENT , but will also display a WARNING warning that does not interrupt the script.

Setting the Default Sampling Method

Another important setting is regulated using the PDO::DEFAULT_FETCH_MODE constant. It allows you to configure the default operation of the fetch() method, which will be used to obtain the results of the request. Here are the most commonly used options:

PDO::FETCH_BOTH

When using it, the results obtained will be indexed by both integers and column names. Using it in a method to obtain a row from a table of planets will give us the following results:

$stmt = $pdo->query("SELECT * FROM planets"); $results = $stmt->fetch(PDO::FETCH_BOTH);

Array ( => 1 => 1 => earth => earth => blue => blue)

PDO::FETCH_ASSOC

With this constant, the results will be written to an associative array in which each key will be a column name, and each value will represent a specific value in the row:

$stmt = $pdo->query("SELECT * FROM planets"); $results = $stmt->fetch(PDO::FETCH_ASSOC);

Array ( => 1 => earth => blue)

PDO::FETCH_NUM

Using the PDO::FETCH_NUM constant we get a 0-indexed array:

Array ( => 1 => earth => blue)

PDO::FETCH_COLUMN

This constant is useful for getting only the values ​​from a column, and the method will return all the results inside a simple one-dimensional array. For example, here's a request:

$stmt = $pdo->query("SELECT name FROM planets");

As a result:

Array ( => earth => mars => jupiter)

PDO::FETCH_KEY_PAIR

This constant is useful for getting only the values ​​from a column, and the method will return all the results inside a simple one-dimensional array. For example, here's a request:

This constant is useful when you need to get values ​​from two columns. The fetchAll() method will return the results as an associative array. In this array, the data from the first column will be specified in the form of keys, and from the second - as values:

$stmt = $pdo->query("SELECT name, color FROM planets"); $result = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);

Array ( => blue => red => strange)

PDO::FETCH_OBJECT

When using the PDO::FETCH_OBJECT constant, an anonymous object will be created for each row fetched. Its (public) properties will be named the same as the columns, and the query results will be used as values. Using this method for the same query as above will produce the following result:

$results = $stmt->fetch(PDO::FETCH_OBJ);

stdClass Object ( => earth => blue)

PDO::FETCH_CLASS

When using fetch() with PDO::FETCH_CLASS you must use the setFetchMode() method on the object before attempting to fetch the data, for example:

$stmt = $pdo->query("SELECT name, color FROM planets"); $stmt->setFetchMode(PDO::FETCH_CLASS, "Planet");

We specify the PDO::FETCH_CLASS constant as the first argument of the setFetchMode() method and the name of the class used to create the object (in our case “Planet”) as the second argument. Now let's run the code:

$planet = $stmt->fetch();

This should result in a Planet object:

Var_dump($planet);

Planet Object ( => earth => blue)

Notice how the values ​​returned from the query were assigned to the corresponding characteristics of the object, even though they are private.

Assigning characteristics after creating an object

The “Planet” class did not have any specific constructor, so there were no problems with assigning characteristics; but what if the class has a constructor in which the characteristics are set and changed? Since the values ​​are assigned before the constructor runs, they will be overwritten.

PDO helps provide the FETCH_PROPS_LATE constant: when used, the values ​​will be assigned after the object is created. Example:

Class Planet ( private $name; private $color; public function __construct($name = moon, $color = gray) ( $this->name = $name; $this->color = $color; ) public function setName($ planet_name) ( $this->name = $planet_name; ) public function setColor($planet_color) ( $this->color = $planet_color; ) public function getName() ( return $this->name; ) public function getColor() ( return $this->color; ) )

We modified our Planet class to create a constructor that will take two arguments: name name and color . These arguments have the base values ​​moon and gray, which means that if no other values ​​are given, these will be set.

In this case, if we do not use FETCH_PROPS_LATE, then no matter what values ​​are obtained from the database, all characteristics will remain basic, because during the object creation process, they will be overwritten. To check this, let's run the following query:

$stmt = $pdo->query("SELECT name, color FROM solar_system WHERE name = "earth""); $stmt->setFetchMode(PDO::FETCH_CLASS, "Planet"); $planet = $stmt->fetch();

Now let's take a look at the Planet object and check which values ​​correspond to its characteristics:

As expected, the values ​​retrieved from the database were overwritten by the default values. Now, we will demonstrate the solution to the problems using the FETCH_PROPS_LATE constant (and the same query as the previous one):

$stmt->setFetchMode(PDO::FETCH_CLASS|PDO::FETCH_PROPS_LATE, "Planet"); $planet = $stmt->fetch(); var_dump($planet);

object(Planet)#4 (2) ( ["name":"Planet":private]=> string(5) "earth" ["color":"Planet":private]=> string(4) "blue" )

Finally, the desired result was obtained. But what if the class constructor has no base values ​​and they must be specified? This is already simpler: we can set the constructor parameters in the form of an array, as the third argument after the class name, using the setFetchMode() method. For example, let's change the constructor:

Class Planet ( private $name; private $color; public function __construct($name, $color) ( $this->name = $name; $this->color = $color; ) [...] )

The constructor arguments are now required, so we run:

$stmt->setFetchMode(PDO::FETCH_CLASS|PDO::FETCH_PROPS_LATE, "Planet", ["moon", "gray"]);

In this case, the parameters we specify serve only as the basic values ​​required for the object to operate without errors: they will be overwritten by the values ​​from the database.

Retrieving Multiple Objects

It is of course possible to obtain multiple results at once in the form of objects, either using the fetch() method or through a loop:

While ($planet = $stmt->fetch()) ( // Something to do with the results )

Or getting all the results at once. In this case, as mentioned earlier, when using the fetchAll() method you will need to specify the fetch mode not before running the method, but at the moment when it runs:

$stmt->fetchAll(PDO::FETCH_CLASS|PDO_FETCH_PROPS_LATE, "Planet", ["moon", "gray"]);

PDO::FETCH_INTO

When using this constant, PDO does not create a new object, instead updating the characteristics of an existing one, but only if it is public or if the __set() method is used inside the object.

Prepared against direct requests

PDO has two ways of working with queries: using direct ones and the more reliable one - prepared ones.

Direct requests

The second method, instead, returns the number of the row that was modified by the query: we use it in cases that replace rows, such as INSERT, DELETE or UPDATE. Direct queries should be used only in cases where there are no variables in the queries and there is no doubt about the safety of the method.

Prepared queries

PDO also supports two-step prepared queries: these are useful when queries have variables, and are safer in general since the prepare() method will do all the necessary work for us. Let's take a look at how variables are used. Imagine we want to insert the characteristics of a planet into the Planets table. First, let's prepare a request:

$stmt = $pdo->prepare("INSERT INTO planets(name, color) VALUES(?, ?)");

As stated earlier, we use the prepare() method which takes the SQL query as an argument, using temporary values ​​for the variables. Temporary values ​​can be of two types: positional and nominal.

Positional

Using? positional temporary values, the code is more concise, but we must specify the data to be inserted in the same order as the column names in the array provided as an argument to the execute() method:

$stmt->execute([$planet->name, $planet->color]);

Personalized

By using named placeholders, we don't need a specific order, but we get more code as a result. When running the execute() method, we must supply the data in the form of an associative array, where each key is the name of the temporary value used, and the associated value is what is carried into the query. For example, the previous request would become:

$stmt = $pdo->prepare("INSERT INTO planets(name, color) VALUES(:name, :color)"); $stmt->execute(["name" => $planet->name, "color" => $planet->color]);

The prepare() and execute() methods can both be used for queries that modify or simply retrieve information from the database. In the first case, we use the fetch methods listed above to obtain information, and in the second, we use the rowCount() method.

bindValue() and bindParam() methods

The bindValue() and bindParam() methods can also be used to provide the values ​​that will be inserted into the request. The first binds the value of a given variable to a positional or named temporary value used in preparing the request. Taking the previous case as an example, we will do:

$stmt->bindValue("name", $planet->name, PDO::PARAM_STR);

We bind the value of $planet->name to a temporary value:name . Note that using both the bindValue() and bindParam() methods we can also specify the type of the variable as the third argument using a suitable PDO constant, in this case PDO::PARAM_STR .

By using bindParam() instead we can bind the variable to a suitable temporary value used in query preparation. Note that in this case, the variable is bound to reference and its value will only be changed to temporary when the execute() method runs. The syntax is the same as last time:

$stmt->bindParam("name", $planet->name, PDO::PARAM_STR)

We have bound the variable, not its value, $planet->name to:name ! As said above, the replacement will only happen when the execute() method is run, so the temporary value will be replaced with the value of the variable at that moment.

PDO Transactions

Transactions allow you to maintain consistency when running multiple queries. All queries are executed in batches and are applied to the database only if they are all successful. Transactions will not work with all databases, and not with all SQL constructs, since some of them cause problems.

As an extreme and strange example, imagine that the user had to select a list of planets and every time he made a new selection, you would have to delete the previous one from the database before inserting a new one. What if the deletion happens but the insertion doesn't? We will get a user without planets! Basically, transactions are applied like this:

$pdo->beginTransaction(); try ( $stmt1 = $pdo->exec("DELETE FROM planets"); $stmt2 = $pdo->prepare("INSERT INTO planets(name, color) VALUES (?, ?)"); foreach ($planets as $planet) ( $stmt2->execute([$planet->getName(), $planet->getColor()]); ) $pdo->commit() ) catch (PDOException $e) ( $pdo-> rollBack();

First of all, the beginTransaction() method on the PDO object disables the autocommit of the request, then the requests are started in the required order. At this point, unless a PDOException occurs, requests are automatically passed through the commit() method; otherwise, transactions are canceled through the rollBack() method and autocommit is restored.

This way, with multiple requests, there will always be a sequence. This is pretty obvious, but PDO transactions can only be used by PDO::ATTR_ERRMODE set to PDO::ERRMODE_EXCEPTION .

Bootstrap framework: fast adaptive layout

Step-by-step video course on the basics of adaptive layout in the Bootstrap framework.

Learn how to typesetting simply, quickly and efficiently using a powerful and practical tool.

Layout to order and get paid.

Free course "Site on WordPress"

Do you want to master the WordPress CMS?

Get lessons on website design and layout on WordPress.

Learn to work with themes and cut layouts.

Free video course on drawing a website design, layout and installation on CMS WordPress!

*Mouse over to pause scrolling.

Back forward

Basics of working with the PDO extension

Today we will discuss a very interesting topic - the basics of working with the extension. PDO for PHP.

PDO (PHP Data Objects)- this is simply an interface that allows you to work with various databases without taking into account their specifics. With PDO we can easily switch between and manage different databases. To make it clearer, let's look at an example.

How we should have connected to the database before MySQL?

Mysql_connect($host, $user, $password); mysql_select_db($db);

To connect to SQLite we should have written it like this:

Sqlite_open($db);

If we need a database PostgreSQL, then you need to write it like this:

Pg_connect("host=$host, dbname=$db, user=$user, password=$password");

Not very convenient, right? It turns out that if we want to change the database, we will have to redo a lot of code. And so, to fix this, a special PHP extension appeared - PDO.

Let's see how we can now connect to the database:

$db = new PDO("mysql:host=$host;dbname=$db", $user, $password);

$db = new PDO("sqlite:$db);

PostgreSQL:

$db = new PDO("pgsql:host=$host;dbname=$db", $user, $password);

As you can see, everything is the same, except for the connection line. That's the only difference.


Now let's look at how we used to have to execute queries:

$sql = "INSERT INTO(name, email) VALUES($name, $email)"; // MySQL mysql_query($sql); // SQLite sqlite_query($sql); // PostgreSQL pg_query($sql);

Now we can abstract from this:

// PDO $result = $db->exec($sql);

All! Our the request will be executed no matter what database we use, and into the variable result will show the number of affected rows.

However, we will not be able to select something from the database in this way. For sampling we need to use not exec, A query.

$sql = "SELECT name FROM users"; $result = $db->query($sql);

Now let's Let's remember about safety, because all data needs to be checked. How did we do it before?

$sql = "SELECT * FROM users WHERE name = $name"; $name = $_POST["name"]; // MySQL $name = mysql_real_escape_string($name); // SQLite $name = sqlite_escape_string($name); // PostgreSQL $name = pg_escape_string($name);

Now we don't need to do this. PDO will do everything for us.

$name = $db->quote($name); $result = $db->query($sql);

PDO itself will check everything and process the transmitted data. Cool? :) Even cooler to come! Let's continue.

How did we convert the result into an array before? Let's look at the example of a database MySQL.

$result = mysql_query($sql); // So $row = mysql_fetch_assoc($result); // Or like this... $row = mysql_fetch_array($result, FETCH_ASSOC);

Just like the associative one, we could also get a numbered array. Now let's look at how this is done in PDO:

$stmt = $db->query($sql); //Associative $result = $stmt->FETCH(PDO::FETCH_ASSOC); // Numbered $result = $stmt->FETCH(PDO::FETCH_NUM); // Both types of arrays at the same time $result = $stmt->FETCH(PDO::FETCH_BOTH); // Object $result = $stmt->FETCH(PDO::FETCH_OBJ);

It's also very easy to use:

// Associative echo $result["name"]; // Numbered echo $result; // Object echo $result->name;

For the "lazy" there is this thing:

$stmt = $db->query($sql); $result = $stmt->FETCH(PDO::FETCH_LAZY);

It returns all 3 types at once. Those. This FETCH_BOTH And FETCH_OBJ together. As you may have guessed, after this the data can be accessed in any of three ways:

Echo $result->name; echo $result["name"]; echo $result;

However Fetch returns only one record, so if we want to get all records, then we need to use FetchAll.

$stmt = $db->query("SELECT * FROM users"); $result = $stmt->FetchAll(PDO::FETCH_ASSOC); foreach($result as $user) ( echo $user["name"]."
"; }

But there is another cool thing related to Fetch. With its help we can fill our class with data from the database automatically.

Class User ( public $login; public $id; public function showInfo() ( echo " ".$this->id.""." : ".$this->login."
"; ) ) $db = new PDO("mysql:host=localhost;dbname=test", "root", ""); $stmt = $db->query("SELECT * FROM `users`"); $ result = $stmt->fetchAll(PDO::FETCH_CLASS, "User"); foreach($result as $user) ( $user->showInfo(); )

As you can see, everything is very simple. We just need to specify a constant FETCH_CLASS and separated by a comma in quotes, the name of the class where the data will be inserted.

Then we loop through the object and display the information we need.
Attention! The names of the properties in the class must match the names of the fields in the database.

Among other things, we can create so-called prepared queries. What are their advantages?

1. We can prepare a request once, and then run it as many times as we need. And with both the same and other parameters.

When a query is prepared, the DBMS analyzes it, compiles it, and optimizes its execution plan. In case of complex queries, the execution time will be noticeable if we run it with different parameters. In the case of prepared queries, this is done once and, therefore, less time is wasted.

2. Prepared query parameters do not need to be escaped with quotes; the driver does this automatically. If the application uses only prepared queries, then SQL injections are almost impossible.

PDO can emulate prepared queries, if they are not supported by the driver. Now, let's look at how to use them?

$stmt = $db->prepare("INSERT INTO users (name, login) VALUES (:name, :login)"); $stmt->bindParam(":name", $name); $stmt->bindParam(":login", $login); // Insert one line with these values ​​$name = "vasya"; $login = "vasya123"; $stmt->execute(); // Now another line with different values ​​$name = "petya"; $login = "petya123"; $stmt->execute();

Method bindParam allows us to set parameters. I think everything is clear here. First, where we want the data to be inserted, write the following line " :Name". And then we indicate where they will come from. In this case, they will be taken from the variables name And login.

Now we can use this request with different parameters as many times as we want, and to execute it we need to call the method execute. These were named options. There is also not named.

$stmt = $db->prepare("INSERT INTO users (name, login) VALUES (?, ?)"); // The data from the name variable will be inserted instead of the first question mark $stmt->bindParam(1, $name); // Data from the login variable will be inserted instead of the second question mark $stmt->bindParam(2, $login); // Insert one line with these values ​​$name = "vasya"; $login = "vasya123"; $stmt->execute(); // Now another line with different values ​​$name = "petya"; $login = "petya123"; $stmt->execute();

Next point - How do we catch errors?

There is a class for this PDOException. I recommend writing all your requests in a block try-catch.

Try ( $db = new PDO("myql:host=localhost;dbname=test", "root", ""); $stmt = $db->query("SELECT * FROM users"); $result = $stmt ->fetch(PDO::FETCH_ASSOC); echo $result["login"]; ) catch(PDOException $e) ( echo "Error: ".$e->getMessage()."
"; echo "On the line: ".$e->getLine(); )

Here we made a mistake and wrote myql instead of mysql. And class PDOException will write to us about it.

It has several methods, but the most commonly used are getMessage() which returns us the error text and getLine(), which returns the line number where the error occurred.

And finally, let's talk about transactions. First I'll give the code.

Try ( $db = new PDO("mysql:host=localhost;dbname=test", "root", ""); $db->beginTransaction(); $stmt = $db->exec("INSERT INTO `users `(`login`) VALUES("login1")"); $stmt = $db->exec("INSERT INTO `users`(`login`) VALUES("login2")"); $stmt = $db- >exec("INSERT INTO `users`(`login`) VALUES("login3")"); $db->commit(); ) catch(PDOException $e) ( $db->rollBack(); )

Here we start the transaction using the method beginTransaction(). Next comes some query code. Then we call the method commit() to confirm our changes. If something goes wrong, then in the block catch we call the method rollBack(), which will return all our data to the previous state.

“Why are these transactions actually needed?” - you ask. To answer this question, consider the example I gave above. There you insert the value into the "login" field login1, login2, login3.

Let's imagine that after inserting login1 And login2, some error occurred. It turns out that this data is inserted, and login3- No. In many cases this is unacceptable and will break the application in the future.

It is precisely to prevent such situations that transactions are needed. If our script fails, then the method rollBack() will return everything to its original form. Those. login1 And login2 will also not be inserted. Let's emulate this error.

Try ( $db = new PDO("mysql:host=localhost;dbname=test", "root", ""); $db->beginTransaction(); $stmt = $db->exec("INSERT INTO `users `(`login`) VALUES("login1")"); $stmt = $db->exec("INSERT INTO `users`(`login`) VALUES("login2")"); exit("error") ; $stmt = $db->exec("INSERT INTO `users`(`login`) VALUES("login3")"); $db->commit(); catch(PDOException $e) ( $db-> rollBack();

After insertion login1 And login2 we exit the script using the function exit(). We throw an exception, we end up in a block catch, and then we return everything to its original form. Now, if we look in the database, we will not see there login1 And login2.

We'll finish at this point. Obviously, here we have not covered everything that PDO provides us, but we have learned the basics of working with it. You can always find more detailed information about this extension on the official PHP website.

The material was prepared by Vladislav Andreev specifically for the website

P.S. Do you want to move further in mastering PHP and OOP? Pay attention to premium lessons on various aspects of website building, including programming in PHP, as well as a free course on creating your own CMS system in PHP from scratch using OOP:

Did you like the material and want to thank me?
Just share with your friends and colleagues!


mob_info