Pdo php script is executed 2 times. PHP PDO - working with databases correctly

meta tag keywords example (4)

Target

As I see it, your goal in this case is twofold:

create and maintain single/reusable connection for each database
make sure the connection is configured correctly

Solution

$provider = function() ( $instance = new PDO("mysql:......;charset=utf8", "username", "password"); $instance->setAttribute(PDO::ATTR_ERRMODE, PDO: :ERRMODE_EXCEPTION); $instance->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); return $instance; ); $factory = new StructureFactory($provider);

Then in another file or below in the same file:

$something = $factory->create("Something"); $foobar = $factory->create("Foobar");

The factory itself should look something like this:

Class StructureFactory ( protected $provider = null; protected $connection = null; public function __construct(callable $provider) ( $this->provider = $provider; ) public function create($name) ( if ($this->connection = == null) ( $this->connection = call_user_func($this->provider); ) return new $name($this->connection); ) )

This way you can have a centralized structure that ensures that a connection is only created when needed. This would also make the process of unit testing and maintenance easier.

The supplier in this case will be found somewhere during the bootstrap phase. This approach will also give a clear location where you can define the configuration you are using to connect to the DB.

Keep in mind that this extremely simplified example. You may also like to watch the following two videos:

Also, I highly recommend reading a proper tutorial about using PDO (there is a bad tutorial log online).

From time to time I see questions about connecting to a database.
Most of the answers are not how I do it, or I just can't answer correctly. Anyway; I never thought about it because the way I do it works for me.

But here's a crazy thought; Perhaps I'm doing all this wrong, and if so; I would really like to know how to properly connect to the database MySQL data using PHP and PDO and make it accessible.

Here's how I do it:

First, here is my file structure (truncated) :

Public_html/ * index.php * initialize/ -- load.initialize.php -- configure.php -- sessions.php

index.php
At the very top I require("initialize/load.initialize.php"); ,

load.initialize.php

# site configurations require("configure.php"); # connect to database require("root/somewhere/connect.php"); // this file is placed outside of public_html for better security. # include classes foreach (glob("assets/classes/*.class.php") as $class_filename)( include($class_filename); ) # include functions foreach (glob("assets/functions/*.func.php") as $func_filename)( include($func_filename); ) # handle sessions require("sessions.php");

I know there is a better, or more correct way to include classes, but I don't remember what it was. Didn't get time to look into it yet, but I think it was something with autoload . something like that...

configure.php
Here I'm basically just overriding some php.ini-properties and do some others global settings for site

connect.php
I set the connection to the class so that other classes can expand this...

Class connect_pdo ( protected $dbh; public function __construct() ( try ( $db_host = " "; // hostname $db_name = " "; // databasename $db_user = " "; // username $user_pw = " "; // password $con = new PDO("mysql:host=".$db_host."; dbname=".$db_name, $db_user, $user_pw); $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $con->exec("SET CHARACTER SET utf8"); // return all sql requests as UTF-8 ) catch (PDOException $err) ( echo "harmless error message if the connection fails"; $err->getMessage() "
"; file_put_contents("PDOErrors.txt",$err, FILE_APPEND); // write some details to an error-log outside public_html die(); // terminate connection ) ) public function dbh() ( return $this->dbh ; ) ) # put database handler into a var for easier access $con = new connect_pdo(); $con = $con->dbh(); //

This is where I really believe there is room for massive improvement as I recently started learning OOP and have been using PDO instead of mysql.
So I just followed a few tutorials for beginners and tried out different things...

sessions.php
Besides handling normal sessions, I also initialize some classes in the session like this:

If (!isset($_SESSION["sqlQuery"]))( session_start(); $_SESSION["sqlQuery"] = new sqlQuery(); )

So this class is available everywhere. It may not be very good practice (?) ...
Anyway, this is what this approach allows me to do everywhere:

Echo $_SESSION["sqlQuery"]->getAreaName("county",9); // outputs: Aust-Agder (the county name with that id in the database)

Inside mine class sqlQuery which extends mine Class connect_pdo , I have a public function getAreaName that handles the query in my database.
Pretty neat I think.

Works like a charm
So that's basically how I do it.
Also, whenever I need to retrieve something from my DB from a class, I just do something similar to this:

$id = 123; $sql = "SELECT whatever FROM MyTable WHERE id = :id"; $qry = $con->prepare($sql); $qry -> bindParam(":id", $id, PDO::PARAM_INT); $qry -> execute(); $get = $qry->fetch(PDO::FETCH_ASSOC);

Because I'm inserting the connection into a variable inside connect_pdo.php, I just reference it and I'm good to go. It works. I'm getting the expected results...

But regardless of this; I would really appreciate it if you guys could tell me if I leave here. Instead I would have to change areas that I could or should change for improvement etc...

I really want to study...

$dsn = "mysql:host=your_host_name;dbname=your_db_name_here"; // define host name and database name $username = "you"; // define the username $pwd="your_password"; // password try ( $db = new PDO($dsn, $username, $pwd); ) catch (PDOException $e) ( $error_message = $e->getMessage(); echo "this is displayed because an error was found "; exit(); )

I recently came up with a similar answer/question on my own. This is what I did, in case anyone is interested:

args = func_get_args(); ) public function __call($method, $args) ( if (empty($this->db)) ( $Ref = new \ReflectionClass("\PDO"); $this->db = $Ref->newInstanceArgs($ this->args); ) return call_user_func_array(array($this->db, $method), $args); ) )

To call it you only need to change this line:

$DB = new \Library\PDO(/* normal arguments */);

And a hint type if you use it (\Library\PDO$DB).

This is indeed similar to the accepted answer and yours; however, it has a significant advantage. Consider this code:

$DB = new \Library\PDO(/* args */); $STH = $DB->prepare("SELECT * FROM users WHERE user = ?"); $STH->execute(array(25)); $User = $STH->fetch();

While it may look like regular PDO (it's modified by that \Library\ only), it doesn't actually initialize the object until you call the first method, whichever it is. This makes it more optimized since creating a PDO object is a bit expensive. This is a transparent class, or what it is called Ghost, a form. You can treat $DB like a regular PDO instance, pass it around, do the same operations, etc.

I would suggest not using $_SESSION to access your DB connection globally.

You can do one of several things (okay the worst for the best practitioner):

Accessing $dbh using global $dbh inside your functions and classes

Use a singleton registry and access it globally, for example:

$registry = MyRegistry::getInstance(); $dbh = $registry->getDbh();

Add the database handler to the classes it needs:

Class MyClass ( public function __construct($dbh) ( /* ... */ ) )

However, it is a little more advanced and requires more "wiring" without the frame. Thus, if dependency injection is too complex for you, use a singleton registry instead of a collection of global variables.

Provides methods for preparing expressions and working with objects that can make you more productive!

Introduction to PDO

"PDO - PHP Data Objects is a database access layer that provides unified methods for accessing different databases."

It does not rely on specific database syntax and allows you to easily switch to another database type and platform simply by changing the connection string in most cases.

This lesson is not a description of the process of working with SQL. It is intended for those who use extensions mysql or mysqli to help them move to a more powerful and portable PDO.

Database support

The extension supports any database for which there is a PDO driver. Drivers are currently available for the following database types:

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_4D (4D)

For the system to work, it is enough to install only those drivers that are really needed. You can get a list of drivers available on the system as follows:

Print_r(PDO::getAvailableDrivers());

Connection

Different databases may have slightly different connection methods. Methods for connecting to several popular databases are shown below. You will notice that the first three are identical to each other, and only SQLite has a specific syntax.

try ( # MS SQL Server and Sybase with PDO_DBLIB $DBH = new PDO("mssql:host=$host;dbname=$dbname, $user, $pass"); $DBH = new PDO("sybase:host=$host ;dbname=$dbname, $user, $pass"); # MySQL with 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(); )

Pay attention to the block try/catch- you should always wrap PDO operations in a block try/catch and use the exception mechanism. Typically only one connection is made, our example shows multiple connections to show the syntax. $DBH contains a database handle and will be used throughout our tutorial.

You can close any connection by setting the handle to null.

# Close the connection $DBH = null;

You can learn more about specific options and connection strings for various databases from the documents on PHP.net.

Exceptions and PDO

PDO can use exceptions to handle errors. This means that all PDO operations must be enclosed in a block try/catch. PDO can throw three levels of errors, the error control level is selected by setting the error control mode attribute for the database descriptor:

$DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT); $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING); $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Regardless of the control level set, a connection error always raises an exception and should therefore always be enclosed in a block try/catch.

PDO::ERRMODE_SILENT

Error control level is set by default. At this level, errors are generated according to the same principle as in extensions mysql or mysqli. The other two levels of error control are more suitable for the DRY (Don't Repeat Youself) programming style.

PDO::ERRMODE_WARNING

At this level of error control, standard PHP warnings are generated and the program can continue executing. This level is convenient for debugging.

PDO::ERRMODE_EXCEPTION

This level of error control should be used in most situations. Exceptions are generated to carefully handle errors and hide data that could help someone hack your system. Below is an example demonstrating the benefits of exceptions:

# Connect to the database try ( $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass); $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION) ; # Wrongly typing DELECT instead of SELECT! $DBH->prepare("DELECT name FROM people"); ) catch(PDOException $e) ( echo "Sorry. But the operation could not be performed."; file_put_contents("PDOErrors.txt" , $e->getMessage(), FILE_APPEND); )

There is an intentional error in the SELECT statement here. This will throw an exception. The exception will send a description of the error to the log file and display a message to the user.

Inserting and updating data

Inserting new data or updating existing data is one of the most commonly used common database operations. When using PDO, it is decomposed into two stages. Everything described in this chapter applies to both operations. UPDATE And INSERT.

Here's an example of the most used data insertion type:

# STH is the "state handle" $STH = $DBH->prepare("INSERT INTO folks (first_name) values ("Cathy")"); $STH->execute();

Of course, you can perform this operation using the method exec(), and the number of calls will be one less. But it's better to use a longer method to get the benefits of prepared expressions. Even if you only intend to use them once, prepared expressions will help you protect against attacks on your system.

Prepared Expressions

Prepared statements are precompiled SQL statements that can be executed many times by sending only the data to the server. They have the added benefit of automatically populating the template with data in the form of protection against attacks via SQL code attachments.

You can use prepared expressions by including templates in your SQL code. Below are 3 examples: one without templates, one with unnamed templates, one with named templates.

# no templates - open to SQL injection attacks! $STH = $DBH->("INSERT INTO folks (name, addr, city) values ($name, $addr, $city)"); # unnamed templates $STH = $DBH->("INSERT INTO folks (name, addr, city) values (?, ?, ?); # named templates $STH = $DBH->("INSERT INTO folks (name, addr , city) value (:name, :addr, :city)");

You should avoid using the first method. The choice of named or unnamed patterns affects how you set the data for these expressions.

Unnamed templates

# assign variables to each template, indexed from 1 to 3 $STH->bindParam(1, $name); $STH->bindParam(2, $addr); $STH->bindParam(3, $city); # Insert one line $name = "Dima" $addr = "Lizyukova St."; $city = "Moscow"; $STH->execute(); # Insert another line $name = "Senya" $addr = "Communist dead end"; $city = "Peter"; $STH->execute();

The operation takes place in two stages. In the first step, variables are assigned to the templates. Then, the variables are assigned values and the expression is executed. To send the next piece of data, you need to change the values of the variables and run the expression again.

Looks a bit cumbersome for expressions with a lot of parameters? Certainly. However, if your data is stored in an array, then everything will be very short:

# Data to be inserted $data = array("Monya", "Forget-Me-Not Avenue", "Zakutaysk"); $STH = $DBH->("INSERT INTO folks (name, addr, city) values (?, ?, ?)"); $STH->execute($data);

The data in the array is substituted into the templates in the order they appear. $data goes to the first template, $data to the second, and so on. However, if the array is indexed in a different order, then such an operation will not be performed correctly. You need to ensure that the order of the patterns matches the order of the data in the array.

Named Templates

Here is an example of using a named template:

# The first argument to the function is the name of the named template # A named template always starts with a colon $STH->bindParam(":name", $name);

You can use shortcuts, but they work with associated arrays. Example:

# Data to be inserted $data = array("name" => "Michelle", "addr" => "Kuznechny Lane", "city" => "Cnjkbwf"); # Shorthand $STH = $DBH->("INSERT INTO folks (name, addr, city) value (:name, :addr, :city)"); $STH->execute($data);

Your table keys do not require a colon, but must still match template names. If you are using an array of arrays, you can iterate through it and simply call execute for each data set.

Another nice feature of named templates is the ability to insert objects directly into your database when the properties and field names match. Example:

# Simple object class person ( public $name; public $addr; public $city; function __construct($n,$a,$c) ( $this->name = $n; $this->addr = $a; $ this->city = $c; ) # etc... ) $cathy = new person("Katya","Lenin Avenue","Mozhaisk"); # Execute: $STH = $DBH->("INSERT INTO folks (name, addr, city) value (:name, :addr, :city)"); $STH->execute((array)$cathy);

Converting object type to array V execute causes properties to be treated as array keys.

Receiving data

The state identifier method is used to obtain data ->fetch(). Before calling the method fetch() you need to tell PDO how you will retrieve data from the database. You can select the following options:

PDO::FETCH_ASSOC: returns an array indexed by column names
PDO::FETCH_BOTH (default): returns an array indexed by column names and numbers
PDO::FETCH_BOUND: Assigns the values of your columns to a set of variables using the method ->bindColumn()
PDO::FETCH_CLASS: assigns column values to properties of a named class; if the corresponding property does not exist, it is created
PDO::FETCH_INTO: updates an existing instance of a named class
PDO::FETCH_LAZY: combination PDO::FETCH_BOTH/PDO::FETCH_OBJ, creates object variable names as they are used
PDO::FETCH_NUM: returns an array indexed by column numbers
PDO::FETCH_OBJ: returns an anonymous object with property names corresponding to column names

In reality, basic situations are resolved using three options: FETCH_ASSOC, FETCH_CLASS And FETCH_OBJ. To set the data extraction method:

$STH->setFetchMode(PDO::FETCH_ASSOC);

You can also set the data retrieval method directly in the method call ->fetch().

FETCH_ASSOC

This type of data retrieval creates an associative array indexed by column names. It should be fairly well known to those who use extensions mysql/mysqli. Sample data sample:

$STH = $DBH->query("SELECT name, addr, city from folks"); # Set the data retrieval mode $STH->setFetchMode(PDO::FETCH_ASSOC); while($row = $STH->fetch()) ( echo $row["name"] . "\n"; echo $row["addr"] . "\n"; echo $row["city"] . "\n"; )

Cycle while continues to iterate through the sample result one row at a time until complete.

FETCH_OBJ

With this type of data retrieval, a class object is created std for each row of received data:

$STH = $DBH->query("SELECT name, addr, city from folks"); # Set the data fetch mode $STH->setFetchMode(PDO::FETCH_OBJ); # show the result while($row = $STH->fetch()) ( echo $row->name . "\n"; echo $row->addr . "\n"; echo $row->city . "\ n"; )

FETCH_CLASS

With this type of extraction, the data is placed directly into the class you choose. Using FETCH_CLASS the properties of your object are set BEFORE calling the constructor. It is very important. If a property corresponding to the column name does not exist, then such a property will be created (as public) for you.

This means that if data needs transformation after being retrieved from the database, it can be done automatically by your object as soon as it is created.

For example, imagine a situation where the address must be partially hidden for each entry. We can accomplish the task by manipulating the property in the constructor:

Class secret_person ( public $name; public $addr; public $city; public $other_data; function __construct($other = "") ( $this->address = preg_replace("//", "x", $this-> address); $this->other_data = $other; ) )

Once the data is extracted into the class, all lowercase a-z characters in the address will be replaced by the x character. Now, using the class and retrieving the data, the transformation occurs completely transparently:

$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 was 'Leninsky Prospekt 5' you will see 'Lxxxxxxxxxx xx-x 5'. Of course, there are situations where you want the constructor to be called before the data is assigned. PDO has the means to implement this:

$STH->setFetchMode(PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE, "secret_person");

Now, when you repeat the previous example with the mode set PDO::FETCH_PROPS_LATE the address will not be hidden since the constructor has been called and the properties have been assigned.

If you need, you can pass arguments to the constructor when extracting data into the object:

$STH->setFetchMode(PDO::FETCH_CLASS, "secret_person", array("stuff"));

If you need to pass different data to the constructor for each object, you can set the data retrieval mode inside the method fetch:

$i = 0; while($rowObj = $STH->fetch(PDO::FETCH_CLASS, "secret_person", array($i))) ( // do stuff $i++ )

Some other useful methods

Since PDO cannot be fully described in a short article, we will present several useful methods for performing basic operations.

$DBH->lastInsertId();

Method ->lastInsertId() is always called by a database handle (not a state handle) and returns the value of the auto-incremented id of the last inserted row for a given connection.

$DBH->exec("DELETE FROM folks WHERE 1"); $DBH->exec("SET time_zone = "-8:00"");

Method ->exec() used for various auxiliary operations.

$safe = $DBH->quote($unsafe);

Method ->quote() quotas strings so they can be used in queries. This is your reserve in case prepared expressions are not used.

$rows_affected = $STH->rowCount();

Method ->rowCount() returns value integer, indicating the number of rows that are processed by the operation. In the latest version of PDO, according to the bug report (http://bugs.php.net/40822), this method does not work with expressions SELECT. If you have problems and cannot update PHP, you can get the number of rows in the following way:

$sql = "SELECT COUNT(*) FROM folks"; if ($STH = $DBH->query($sql)) ( # Check the number of rows if ($STH->fetchColumn() > 0) ( # There should be a SELECT code here) else ( echo "There are no rows matching the query." ; ) )

I hope you liked the lesson!

Setting up and using PDO - PHP Data Objects extension for working with databases

Creating a test database and table

First, let's create the database for this tutorial:

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

A user with the login testuser and password testpassword was granted full access rights to the solar_system database.

Now let’s create a table and fill it with data, the astronomical accuracy of which is not implied:

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");

Connection Description

Now that the database has been created, let's define DSN () - information for connecting to the database, presented as a string. The description syntax differs depending on the DBMS used. In the example we are working with MySQL/MariaDB, so we indicate:

host name where the DBMS is located;
port (optional if standard port 3306 is used);
database name;
encoding (optional).

The DSN line in this case looks like this:

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

The database prefix is specified first. In the example - mysql. The prefix is separated from the rest of the line by a colon, and each subsequent parameter is separated by a semicolon.

Creating a PDO Object

Now that the DSN string is ready, let's create a PDO object. The input constructor accepts the following parameters:

DSN string.
The name of the user who has access to the database.
This user's password.
An array with additional parameters (optional).

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

Additional parameters can also be defined after the object is created using the SetAttribute method:

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

Defining the default sampling method

PDO::DEFAULT_FETCH_MODE is an important parameter that determines the default fetch method. The specified method is used when obtaining the result of a request.

PDO::FETCH_BOTH

Default mode. The result of the selection is indexed by both numbers (starting from 0) and column names:

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

After executing a query with this mode against the test table of planets, we get the following result:

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

PDO::FETCH_ASSOC

The result is stored in an associative array in which the key is the column name and the value is the corresponding row value:

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

As a result we get:

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

PDO::FETCH_NUM

When using this mode, the result is presented as an array indexed by column numbers (starting from 0):

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

PDO::FETCH_COLUMN

This option is useful if you need to get a list of values for one field in the form of a one-dimensional array, the numbering of which starts from 0. For example:

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

As a result we get:

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

PDO::FETCH_KEY_PAIR

We use this option if we need to get a list of values of two fields in the form of an associative array. The array keys are the data in the first column of the selection, the array values are the data in the second column. For example:

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

As a result we get:

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

PDO::FETCH_OBJECT

When using PDO::FETCH_OBJECT, an anonymous object is created for each fetched row. Its public properties are the names of the sample columns, and the query results are used as their values:

$stmt = $pdo->query("SELECT name, color FROM planets"); $results = $stmt->fetch(PDO::FETCH_OBJ);

As a result we get:

StdClass Object ( => earth => blue)

PDO::FETCH_CLASS

In this case, as in the previous one, the column values become properties of the object. However, you must specify an existing class that will be used to create the object. Let's look at this with an example. First, let's create a class:

Class Planet ( private $name; private $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; ) )

Please note that the Planet class has private properties and does not have a constructor. Now let's execute the request.

If you are using the fetch method with PDO::FETCH_CLASS , you must use the setFetchMode method before sending a request to fetch the data:

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

The first parameter we pass to the setFetchMode method is the PDO::FETCH_CLASS constant. The second parameter is the name of the class that will be used when creating the object. Now let's do:

$planet = $stmt->fetch(); var_dump($planet);

As a result, we get a Planet object:

Planet Object ( => earth => blue)

The values returned by the query are assigned to the corresponding properties of the object, even private ones.

Defining properties after constructor execution

The Planet class does not have an explicit constructor, so there will be no problems assigning properties. If a class has a constructor in which the property was assigned or changed, they will be overwritten.

When using the FETCH_PROPS_LATE constant, property values will be assigned after the constructor is executed:

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 the Planet class by adding a constructor that takes two arguments as input: name and color. The default values for these fields are moon and gray, respectively.

If you do not use FETCH_PROPS_LATE, the properties will be overwritten with default values when the object is created. Let's check it out. First let's run the query:

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

As a result we get:

Object(Planet)#2 (2) ( ["name":"Planet":private]=> string(4) "moon" ["color":"Planet":private]=> string(4) "gray" )

As expected, the values retrieved from the database are overwritten. Now let's look at solving the problem using FETCH_PROPS_LATE (a similar request):

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

As a result, we get what we need:

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

If a class constructor does not have default values, and they are needed, the constructor parameters are set when calling the setFetchMode method with the third argument in the form of an array. For example:

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

Constructor arguments are required, so let's do:

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

Incoming parameters also act as default values that are needed for initialization. In the future, they will be overwritten with values from the database.

Retrieving Multiple Objects

Multiple results are fetched as objects using the fetch method inside a while loop:

While ($planet = $stmt->fetch()) ( // processing results )

Or by sampling all the results at once. In the second case, the fetchAll method is used, and the mode is specified at the time of the call:

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

PDO::FETCH_INTO

When this selection option is selected, PDO does not create a new object, but rather updates the properties of the existing one. However, this is only possible for public properties or when using the __set magic method on the object.

Prepared and direct requests

There are two ways to execute queries in PDO:

straight, which consists of one step;
prepared, which consists of two steps.

Direct requests

There are two methods for performing direct queries:

query is used for statements that do not make changes, such as SELECT. Returns a PDOStatemnt object from which query results are retrieved using the fetch or fetchAll methods;
exec is used for statements like INSERT, DELETE or UPDATE. Returns the number of rows processed by the request.

Direct operators are used only if there are no variables in the query and you are confident that the query is safe and properly escaped.

Prepared queries

PDO supports prepared statements, which are useful for protecting an application from : the prepare method performs the necessary escaping.

Let's look at an example. You want to insert the properties of a Planet object into the Planets table. First, let's prepare the request:

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

We use the prepare method, which takes an SQL query with pseudo-variables (placeholders) as an argument. Pseudovariables can be of two types: unnamed and named.

Unnamed pseudo variables

Unnamed pseudo-variables (positional placeholders) are marked with ? . The resulting query is compact, but requires the values to be substituted in the same order. They are passed as an array through the execute method:

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

Named Pseudo Variables

When using named placeholders, the order in which values are passed for substitution is not important, but the code in this case becomes less compact. Data is passed to the execute method in the form of an associative array, in which each key corresponds to the name of a pseudo-variable, and the value of the array corresponds to the value that needs to be substituted into the request. Let's redo the previous example:

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

The prepare and execute methods are used both when executing change requests and when fetching.

And information about the number of rows processed, if necessary, will be provided by the rowCount method.

Controlling PDO error behavior

The error mode selection parameter PDO::ATTR_ERRMODE is used to determine how PDO behaves in the event of errors. There are three options available: PDO::ERRMODE_SILENT , PDO::ERRMODE_EXCEPTION and PDO::ERRMODE_WARNING .

PDO::ERRMODE_SILENT

Default option. PDO will simply record information about the error, which the errorCode and errorInfo methods will help you obtain.

PDO::ERRMODE_EXCEPTION

This is the preferred option where PDO throws an exception (PDOException) in addition to error information. An exception interrupts script execution, which is useful when using PDO transactions. An example is given in the description of transactions.

PDO::ERRMODE_WARNING

In this case, PDO also records error information. The script flow is not interrupted, but warnings are issued.

bindValue and bindParam methods

You can also use the bindValue and bindParam methods to substitute values in a query. The first associates the value of the variable with the pseudo-variable that is used to prepare the request:

$stmt = $pdo->prepare("INSERT INTO planets(name, color) VALUES(:name, :color)"); $stmt->bindValue("name", $planet->name, PDO::PARAM_STR);

Linked the value of the $planet->name variable to the pseudo variable:name . Note that when using the bindValue and bindParam methods, the type of the variable is specified as the third argument using the appropriate PDO constants. In the example - PDO::PARAM_STR .

The bindParam method binds a variable to a pseudo variable. In this case, the variable is associated with a pseudo-variable reference, and the value will be inserted into the request only after the execute method is called. Let's look at an example:

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

Transactions in PDO

Let's imagine an unusual example. The user needs to select a list of planets, and each time the request is executed, the current data is deleted from the database, and then new ones are inserted. If an error occurs after deleting, the next user will receive an empty list. To avoid this, we use transactions:

$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(); )

The beginTransaction method disables automatic execution of requests, and inside the try-catch construct, requests are executed in the desired order. If no PDOExceptions are thrown, the requests will be completed using the commit method. Otherwise, they will be rolled back using the rollback method, and automatic execution of queries will be restored.

This created consistency in query execution. Obviously, for this to happen, PDO::ATTR_ERRMODE needs to be set to PDO::ERRMODE_EXCEPTION .

Conclusion

Now that working with PDO has been described, let's note its main advantages:

with PDO it is easy to transfer the application to other DBMSs;
All popular DBMSs are supported;
built-in error management system;
various options for presenting sample results;
prepared queries are supported, which shorten the code and make it resistant to SQL injections;
Transactions are supported, which help maintain data integrity and query consistency when users work in parallel.

June 3, 2018 Andrey Chernyshov Translation Tutorial 2025 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:

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 string format 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 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);

As a result:

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

Like the previous constant, this will assign the column values to properties of the object, but in this case we must configure an existing class that will be used to create the object. For demonstration, first we will create a class:

Don’t pay attention to the simplicity of the code, let’s better look at the Planet class that we created: it has private in its properties and the class does not have a constructor. Now let's try to get results.

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:

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:

Var_dump($planet); object(Planet)#2 (2) ( ["name":"Planet":private]=> string(4) "moon" ["color":"Planet":private]=> string(4) "gray" )

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

There are two main methods for using direct queries: query() and exec() . The first one creates a PDOStatemnt object, which can be accessed via the fetch() or fetchAll() methods: if you use them in cases where the table does not change, such as SELECT .

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:

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 consistency. 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 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 this 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 database as an example 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!