Provides methods for preparing expressions and working with objects that can make your work more productive!
Introduction to PDO
"PDO - PHP Data Objects is a database access layer that provides uniform methods for accessing various databases."
It is independent of specific database syntax and allows you to easily switch to a different database type and platform by simply 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 using extensions mysql or mysqli to help them migrate to more powerful and portable PDO.
Database support
The extension supports any database for which there is a PDO driver. Currently available drivers for the following types of databases:
- 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 in 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 can see 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- always wrap PDO operations in a block try / catch and use the exception mechanism. Usually only one connection is made, our example shows multiple connections to display the syntax. $ DBH contains a handle to the database 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 different databases from the PHP.net docs.
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 generate errors of three levels, the level of error control is selected by setting the error control mode attribute on 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 set monitoring level, a connection error always raises an exception and therefore must always be enclosed in a block try / catch.
PDO :: ERRMODE_SILENT
Default error control level. At this level, errors are generated in the same way as in extensions. mysql or mysqli... The other two levels of error control are more suited to the DRY (Don "t Repeat Youself) style of programming.
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 thrown that allow you to cleanly handle errors and hide data that could help someone else break into 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) ; # Typing DELECT instead of SELECT by mistake! $ 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 a deliberate mistake 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 general database operations. When using PDO, it decomposes in two steps. Everything described in this chapter applies to both operations. UPDATE and INSERT.
Here's an example of the most commonly used type of data insertion:
# 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 (), while the number of calls will be one less. But it is better to use a longer method to take advantage of prepared statements. Even if you are only going to use them once, prepared statements will help you defend against attacks on your system.
Prepared expressions
Prepared statements are precompiled SQL statements that can be executed multiple times by sending only data to the server. They have the added benefit of automatically populating a template with data in the form of protection against SQL injection attacks.
You can use prepared statements by including templates in your SQL code. Below are 3 examples: one with no templates, one with unnamed templates, one with named templates.
# no patterns - 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 templates affects how you set the data for these expressions.
Unnamed templates
# assigning 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 = "Lizyukov 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. The first step is to assign variables to templates. Then, the variables are assigned values and the expression is executed. To send the next chunk of data, you need to change the values of the variables and execute 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", "Prospect Nezabudok", "Zakutaysk"); $ STH = $ DBH -> ("INSERT INTO folks (name, addr, city) values (?,?,?)"); $ STH-> execute ($ data);
The data in the array are substituted into templates in order. $ data goes to the first template, $ data goes 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 keep track of the consistency of the order of the templates and the order of the data in the array.
Named templates
Here's an example using a named template:
# The first function argument is the name of the named template # A named template always starts with a colon $ STH-> bindParam (": name", $ name);
You can use abbreviations, but they work with associated arrays. Example:
# Data to be inserted $ data = array ("name" => "Michelle", "addr" => "Kuznechny lane", "city" => "Cnjkbwf"); # Abbreviation $ STH = $ DBH -> ("INSERT INTO folks (name, addr, city) value (: name,: addr,: city)"); $ STH-> execute ($ data);
Your table keys do not need colons, but must still match template names. If you are using an array of arrays, you can loop through it and just call execute for each dataset.
Another nice thing about named templates is the ability to insert objects directly into your database when 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 an Object Type to array v execute causes properties to be treated as array keys.
Retrieving data
The state identifier method is used to get the data -> fetch ()... Before calling the method fetch () you need to tell PDO how you will get data from the database. The following options can be selected:
- 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 the 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 the 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 matching the column names
In reality, the main situations are resolved with three options: FETCH_ASSOC, FETCH_CLASS and FETCH_OBJ... To set the data retrieval method, use:
$ 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 extraction creates an associative array indexed by column names. It should be fairly well known to those using extensions. mysql / mysqli... Sample data sample:
$ STH = $ DBH-> query ("SELECT name, addr, city from folks"); # Set fetch 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 over the result of the selection one row at a time until complete.
FETCH_OBJ
With this type of data extraction, an object of the class is created std for each line of received data:
$ STH = $ DBH-> query ("SELECT name, addr, city from folks"); # Set fetch mode $ STH-> setFetchMode (PDO :: FETCH_OBJ); # showing 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 that you select. Using FETCH_CLASS your object's properties are set BEFORE the constructor is called. It is very important. If the property of the corresponding column name does not exist, then such a property will be created (as public) For you.
This means that if the data needs transformation after being extracted from the database, then it can be performed automatically by your object as soon as it is created.
For example, imagine a situation where the address must be partially hidden for each record. 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 has been extracted into the class, all lowercase a-z characters in the address will be replaced with x. Now, using the class and receiving data, the transformation is completely transparent:
$ 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 Prospect 5' you will see 'Lhhhhhhhh 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 do this:
$ STH-> setFetchMode (PDO :: FETCH_CLASS | PDO :: FETCH_PROPS_LATE, "secret_person");
Now, when you repeat the previous example with the set mode 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 an 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 () always called by the database handle (not the status handle) and returns the auto-incrementing 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 () quotes strings so that they can be used in queries. This is your reserve in case prepared statements are not used.
$ rows_affected = $ STH-> rowCount ();
Method -> rowCount () returns the value integer specifying the number of lines to be 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 you cannot update PHP, you can get the number of rows in the following way:
$ sql = "SELECT COUNT (*) FROM folks"; if ($ STH = $ DBH-> query ($ sql)) (# Checking the number of rows if ($ STH-> fetchColumn ()> 0) (# There should be SELECT code here) else (echo "There are no rows matching the query." ;))
Hope you enjoyed the tutorial!
The other day I decided to deal with php PDO. It is interesting and it is necessary to study new technologies. I didn't find Russian documentation anywhere, that's why I decided to post it. I hope this article will be of interest to you.
Introduction
Let's start first PHP Data Objects (PDO) is a lightweight interface for accessing databases in PHP. It can work with most databases like MS SQL, Firebird, MySQL, Oracle, PostgreSQL, SQLite and more. But here it is necessary to pay attention that PDO provides the necessary functionality for working with databases, but for each type of database its own access driver for the database must be installed in the form of a PHP extension.
With PDO, you can create applications that are completely independent of the type of database, while you can use most of the functionality of the database, such as the creation of prepared statements or transactions. In the event that this functionality is not supported by the database, PDO emulates this function by its own means, thereby not violating the program logic in any way.
The connection is quite simple, with the exception that now you must immediately specify in one line what type of database you are connecting to, the host name, and also the name of the database.
The format is as follows:
db_type: host = hostname; db = name
Let's look at an example, just complicate the example a little, using exceptions from the PDO library (PDOException). So that in the event of an unsuccessful connection to the database, we will receive an intelligible message about this, and not a bunch of jambs of code, from nowhere.
try ($ db = new PDO ( "mysql: host = localhost; dbname = test", "root", ""); $ rows = $ db -> exec ( "CREATE TABLE` testing` (id INT PRIMARY KEY AUTO_INCREMENT, fname VARCHAR (20) NOT NULL DEFAULT "", email VARCHAR (50) NOT NULL DEFAULT "") "); ) catch (PDOException $ e) (die ("Error:". $ e -> getMessage ());)
If you make a mistake in the SQL statement, there are special functions in PDO:
errorCode () - returns the error number, and
errorInfo () - returns an array containing both the error number and the description text
Queries can be made directly with two functions:
exec () and query ()
Their difference lies in the type of the returned result, exec returns the number of rows affected as a result of the query execution, and the second returns the query result in the PDOStatement object, we'll talk about it below.
Now let's add these functions to the code and make the example a little more complex:
// at the beginning of the config define ("DB_DRIVER", "mysql"); define ("DB_HOST", "localhost"); define ("DB_NAME", "test"); define ("DB_USER", "root"); define ("DB_PASS", ""); try ( // connect to the database$ connect_str = DB_DRIVER. ": host =". DB_HOST. "; dbname =". DB_NAME; $ db = new PDO ($ connect_str, DB_USER, DB_PASS); // insert several rows into the table from the previous example$ rows = $ db -> exec ( "INSERT INTO` testing` VALUES (null, "Ivan", " [email protected]"), (null," Petr "," [email protected]"), (null," Vasiliy "," [email protected]")
"
); $ error_array = $ db -> errorInfo (); if ($ db ->
"
;
// if the request was successful,
// then display the number of affected rows if ($ rows) echo "Number of Rows Affected:"... $ rows. "
"
;
// now select a few lines from the base$ result = $ db -> query ( "SELECT * FROM` testing` LIMIT 2 ")
;
// in case of an error in the SQL statement, display an error message$ error_array = $ db -> errorInfo (); if ($ db -> errorCode ()! = 0000) echo "SQL error:". $ error_array [2]. "
"
;
// now get data from PDOStatement class while ($ row = $ result -> fetch ()) ( // as a result, we get an associative array print_r ($ row); )) catch (PDOException $ e) (die ("Error:". $ e -> getMessage ());)
Prepared expressions
Prepared statements are very similar to regular SQL statements, but they have some advantages. Firstly, they have a higher execution speed, and secondly, they are more reliable from a security point of view, since all parameters passed to them are automatically escaped from all kinds of injections.
They have a significant speed advantage when executing multiple identical queries, rather than rewriting the query each time. It also saves traffic between the application and the database.
PDO provides convenient functions for working with prepared statements. If the selected database type does not support working with prepared statements, PDO will simply emulate their work with its own methods.
And so to begin with, let's create a prepared expression, this is done by the Prepare () function
It accepts an SQL query as a parameter, but instead of the values that need to be changed, it puts pseudo variables, which can be in the form of a question mark (?), Or a pseudo variable name that starts with a colon (:)
$ sth1 = $ db-> prepare (“SELECT * FROM` testing` WHERE id =: id ”);
$ sth2 = $ db-> prepare (“SELECT * FROM` testing` WHERE id =? ”);
Your further work will depend on how you define the variable.
If you have defined variables with a question mark, then pass an array of values to the execute function, in the sequence in which the variables are.
If you have designated variables with names, then you will need to assign a value to each variable using functions:
bindValue () - Assigns a value to a pseudo-variable
bindParam () - binds a pseudo-variable with a real variable, and when the real variable changes, you no longer need to call any additional functions, you can immediately execute ()
Here's an example using the first option:
Please note that after the PDO object creates a prepared expression in the PDOStatement class, we only use it, respectively, it has its own functions errorCode, errorInfo, as well as the result of executing queries, is also immediately stored in it.
And now the second way.
To assign a value to a pseudo variable, use the bindValue () function
This code can be written even more simply by linking the pseudo-variable to the real one:
Here it is necessary to add that it is very desirable (so that there are no unnecessary errors) to indicate the type of the variable as the third parameter. For me personally, in the absence of a variable type, errors arose in the WHERE clause, since it considered the variable to be a text, not a number.
$ sth3-> bindParam (‘: id’, $ id, PDO :: PARAM_INT);
$ sth3-> bindParam (‘: id’, $ id, PDO :: PARAM_STR);
Another very nice thing about using prepared statements like this is variable escaping. Before substitution into the procedure, all variables are escaped and no SQL injection is terrible.
Transactions
A transaction is a collection of database queries that must be executed by all. If any request is not completed or completed with an error, then the transaction is canceled and no data changes in the database occur.
This is to ensure data integrity is maintained across multiple requests. for example, when transferring funds from account to account.
To execute a transaction in PDO, you must switch to manual request confirmation mode.
By the way, transactions are used constantly, but usually PDO works in auto-commit mode, so all transactions consist of one request.
To turn off the auto-confirmation mode, execute the command:
$ db-> beginTransaction ();
After that, we execute as many queries to the database as needed to be done in this transaction.
And only after all requests have been completed, you can confirm the transaction with the function
$ db-> commit ();
or cancel the transaction
$ db-> rollback ();
Here's a small example of transactions:
try ($ connect_str = DB_DRIVER. ": host =". DB_HOST. "; dbname =". DB_NAME; $ db = new PDO ($ connect_str, DB_USER, DB_PASS); $ rows = $ db -> exec ( "CREATE TABLE` testing` (id INT PRIMARY KEY AUTO_INCREMENT, fname VARCHAR (20) NOT NULL DEFAULT "", email VARCHAR (50) NOT NULL DEFAULT "", money INT NOT NULL DEFAULT 0) ENGINE = InnoDB; "); $ rows = $ db -> exec ( "INSERT INTO` testing` VALUES (null, "Ivan", " [email protected]", 15000), (null," Petr "," [email protected]", 411000), (null," Vasiliy "," [email protected]", 1500000) " ) ; // Let's try to transfer the amount of 50,000 from Ivan// Petra $ summ = 50000; $ transaction = true; $ db -> beginTransaction (); $ sth1 = $ db -> query ( "SELECT money FROM testing WHERE fname =" Ivan ""); $ sth2 = $ db -> query ( "SELECT money FROM testing WHERE fname =" Petr ""); $ row1 = $ sth1 -> fetch (); $ row2 = $ sth2 -> fetch (); if (! $ row1 ||! $ row2) $ transaction = false; $ total2 = $ summ + $ row2 ["money"]; $ total1 = $ row1 ["money"] - $ summ; if ($ total1< 0 || $total2 < 0) $transaction = false ; $num_rows1 = $db ->exec (. $ total1. "" WHERE fname = "Ivan" "); $ num_rows2 = $ db -> exec ( "UPDATE` testing` SET money = ""... $ total2. "" WHERE fname = "Petr" "); if ($ transaction) (echo "The transaction was successful"; $ db -> commit (); ) else (echo "Transaction failed"; $ db -> rollback (); )) catch (PDOException $ e) (die ("Error:". $ e -> getMessage ());)
It should also be noted here that not all types of tables support transactions, so in this example I used an InnoDb table instead of the standard MyISAM.
In conclusion, I would like to say that this is far from a complete PDO manual. You can always get all the latest and complete information here: http://www.php.net/manual/en/book.pdo.php
Explore and create.
Bootstrap framework: fast responsive layout
A step-by-step video tutorial on the basics of responsive layout in the Bootstrap framework.
Learn to typeset easily, quickly and efficiently using a powerful and practical tool.
Layout to order and get paid.
Free Course "Site on WordPress"
Want to master a WordPress CMS?
Get tutorials on WordPress website design and layout.
Learn to work with themes and slice the layout.
Free video course on drawing site design, layout and installation on CMS WordPress!
* Hover your mouse to pause scrolling.
Back forward
PDO Extension Basics
Today we will analyze a very interesting topic - the basics of working with the extension. PDO for PHP.
PDO (PHP Data Objects)- it's just some kind of interface that allows you to work with various databases without taking into account their specifics. With PDO, we can easily switch and manage between different databases. To make it clearer, let's look at an example.
How we were supposed to connect to the base earlier MySQL?
Mysql_connect ($ host, $ user, $ password); mysql_select_db ($ db);
To connect to SQLite we had to write like this:
Sqlite_open ($ db);
If we need a database PostgreSQL, then you need to write 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, then 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 base:
$ 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 string. This is the only difference.
Now let's take a look at how we were supposed to execute queries before:
$ 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);
Everything! Our the request will be executed no matter what database we are using, and into a variable result will get the number of affected lines.
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 the data needs to be checked. How have we done 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 will check everything itself and process the transmitted data. Cool? :) It's even cooler! Let's continue.
How did we convert the result to an array before? Consider the base example MySQL.
$ result = mysql_query ($ sql); // So $ row = mysql_fetch_assoc ($ result); // Or like this ... $ row = mysql_fetch_array ($ result, FETCH_ASSOC);
As well as associative, we could get a numbered array. Now let's see 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 is also very easy to use this:
// Associative echo $ result ["name"]; // Numbered echo $ result; // Object echo $ result-> name;
For the "lazy" there is such a thing:
$ stmt = $ db-> query ($ sql); $ result = $ stmt-> FETCH (PDO :: FETCH_LAZY);
It returns all 3 types at once. Those. it FETCH_BOTH and FETCH_OBJ together. As you may have guessed, after that, you can access the data in any of three ways:
Echo $ result-> name; echo $ result ["name"]; echo $ result;
but Fetch returns only one record, so if we want to get all records, then we must 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 commas in quotes, the name of the class where the data will be inserted.
Then we iterate over the object in a loop 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. Moreover, both with the same and with other parameters.
When a query is prepared, the DBMS analyzes it, compiles and optimizes its execution plan. In the case of complex queries, the execution time will be noticeable if we run it with different parameters. In the case of prepared statements, this is done once and, therefore, less time is spent.
2. Parameters of a prepared query do not need to be quoted with quotes; the driver does this automatically. If the application uses only prepared statements, then SQL injection is almost impossible.
PDO can emulate prepared statements 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 string 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, we write the following line " :name". And then we indicate where they will come from. In this case, they will be taken from variables name and login.
Now we can use this request with different parameters as many times as we like, and in order to execute it, we need to call the method execute... These were named parameters. There is also unnamed.
$ stmt = $ db-> prepare ("INSERT INTO users (name, login) VALUES (?,?)"); // Data from the name variable will be inserted instead of the first question mark $ stmt-> bindParam (1, $ name); // The data from the login variable will be inserted in place of the second question mark $ stmt-> bindParam (2, $ login); // Insert one line with these values $ name = "vasya"; $ login = "vasya123"; $ stmt-> execute (); // Now another string with different values $ name = "petya"; $ login = "petya123"; $ stmt-> execute ();
The next moment is how do we catch errors?
There is a class for this PDOException... I recommend writing all your requests in the 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 he 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 on which the error occurred.
And finally, let's talk about transactions... First, I'll give you 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 a transaction using the method beginTransaction ()... Next comes some kind of request code. Then we call the method commit () to confirm our changes. If something went wrong, then in the block catch we call the method rollBack () which will return all our data to its previous state.
"And 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.
Imagine that after inserting login1 and login2, there was some kind of error. 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 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 simulate 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 ()... An exception is thrown for us, we fall into the block catch, and there we return everything to its original form. Now, if we look at the database, we will not see there login1 and login2.
At this point we will end. Obviously, here we have analyzed far from everything that PDO provides us, but we 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 specially for the site site
P.S. Want to move on with your PHP and OOP experience? Check out the premium tutorials on various aspects of site building, including programming in PHP, as well as the free course on creating your own CMS system in PHP from scratch using OOP:
Did you like the material and want to thank you?
Just share with your friends and colleagues!
PDO (PHP Data Objects) is a PHP extension that provides a simple interface for accessing various databases. To put it very simply and briefly, using PDO in PHP, you can connect to databases of different types.
In this tutorial, we will be connecting to a MySQL database, as this is the most common database.
Database connection
You should know that the database server has a name, and users can also connect to it, that is, a username and password must be used to connect.
An example of how we can connect to the database:
$ db = new PDO ("mysql: host = $ host; dbname = $ db", $ user, $ pass);
I think if you are interested in PDO, then your knowledge is enough and you do not need to explain this syntax.
So, we have a connection object for accessing the database.
Exception Handling
When using PDO, I advise you to catch connection errors using the try (...) catch (...) construct. Here's an example of such code:
Try ($ db = new PDO ("myql: host = $ host; dbname = $ dbname", $ user, $ pass);) catch (PDOException $ e) (echo "You have an error:". $ E-> getMessage (). "
"; echo" On line: ". $ e-> getLine ();)
There are different opinions about error handling, for example, not everyone is advised to always use the try (...) catch (...) construct. The point is that PHP will print an error message on the screen anyway, so this code is redundant. Although if you want, for example, to rollback a transaction, then this construction will be useful to you, but more on that below.
Retrieving data from a database using PDO, the query method
To select from the database, the query method is used, to which we pass the SQL query string.
$ db-> query ("SELECT * FROM users");
Remember that this syntax will work for all types of databases.
Also, do not forget about the safety of data transferred in SQL queries. PDO has an analogue of the mysql_real_escape_string () function, the quote method.
$ login = $ db-> quote ($ _ POST ["login"]); $ sql = "SELECT * FROM users WHERE login = $ login"; $ result = $ db-> query ($ sql);
Result handling, FETCH and FETCHALL methods.
Now we need to convert the result from the $ res variable to an array. This is done using the FETCH method, which is passed a constant.
$ res = $ db-> query ($ sql); $ result = $ res-> FETCH (PDO :: FETCH_NUM); // numbered $ result = $ res-> FETCH (PDO :: FETCH_ASSOC); // associative $ result = $ res-> FETCH (PDO :: FETCH_BOTH); // associative and numbered together $ result = $ res-> FETCH (PDO :: FETCH_OBJ); // object type $ result = $ res-> FETCH (PDO :: FETCH_LAZY); // all types at once
Obviously, the FETCH_LAZY constant slows down the script, so it is advisable not to use it.
The FETCH method returns one record from the result. If you want to get all the records, you must use the FETCHALL method. Further, we process the result obtained as a result of using FETCHALL in a foreach loop, as shown in the example:
$ query = $ db-> query ("SELECT * FROM users"); $ result = $ query-> FETCHALL (PDO :: FETCH_ASSOC); foreach ($ result as $ arry) (echo $ arry ["name"]. "
";
}
FETCH_CLASS constant
The FETCH_CLASS constant requires a special explanation; it allows you to fill a previously created class with data from the result of a database query.
Let's see an example using the FETCH_CLASS constant:
Class User (public $ login; public $ pass; public function showInfo () (echo "
". $ this-> pass."
".": ". $ this-> login."
";)) $ result = $ stmt-> FETCHALL (PDO :: FETCH_CLASS," User "); foreach ($ result as $ user) ($ user-> showInfo ();)
Do not forget an important rule - the names of the properties in the created class must be the same as the names of the fields in the database.
Prepared expressions
Prepared statements must be used if your SQL query contains variables.
Prepared PDO statements are the main reason to use PHP Data Objects, as it is the only safe way to execute SQL queries that have user-created variables.
Prepared expressions in PDO is a regular SQL query in which a variable is replaced with a special marker - a placeholder.
Named placeholders
First, let's look at a named placeholder, its syntax is like this:: email.
Let's take a look at an example INSERT query using placeholders.
$ stmt = $ db-> prepare ("INSERT INTO messages (email, message) VALUES (: email,: message)");
In this example, instead of variables in the request, we used two placeholders (: email,: message) ").
$ stmt = $ db-> prepare ("INSERT INTO messages (email, message) VALUES (: email,: message)"); $ stmt-> bindParam (": email", $ email); $ stmt-> bindParam (": message", message); $ email = "E-mail # 1"; $ message = "Some message text"; $ stmt-> execute (); $ email = "E-mail # 2"; $ message = "Some message text"; $ stmt-> execute ();
Please note that in order to prepare the SQL query, we write it in the prepare () method. Then, to indicate to which placeholder which variable to bind, we use the bindParam () method. To execute the SQL query, we call the execute () method.
So, once again the sequence of working with prepared statements step by step:
- We assign the result of the prepare () method to the $ stmt variable.
- Using the bindParam () method, we bind variables and placeholders.
- We assign values to variables.
- Using the execute () method, we execute a query to the database.
This syntax can be written
$ stmt = prepare ("SELECT name FROM users WHERE email =: email"); $ stmt-> execute (array ("email" => $ email));
You can see that an array must be passed to the execute () method, in which the keys must match the names of the placeholders.
By the way, the bindParam () method has a synonym for bindValue ().
Unnamed placeholders
Now let's look at working with unnamed placeholders.
$ stmt = prepare ("SELECT name FROM users WHERE email =?") $ stmt-> execute (array ($ email));
In this syntax, instead of a placeholder: name, another form of its notation is indicated - a question mark :? ...
Here the values of the $ email array will be assigned to the placeholders one by one :? , but in our example there is only one placeholder.
Here's another example of using unnamed placeholders using the bindParam () method:
$ stmt = $ db-> prepare ("INSERT INTO articles (title, text) VALUES (?,?)"); $ stmt-> bindParam (1, $ email); $ stmt-> bindParam (2, $ message); $ email = "E-mail # 1"; $ message = "Some message text"; $ stmt-> execute (); $ email = "E-mail # 2"; $ message = "Some message text"; $ stmt-> execute ();
Inserting into a database, exec () method
If we want to write something to the database, then we can also use the PDO :: exec () method.
$ sql = "INSERT INTO (login, password) VALUES ($ login, $ password)"; $ result = $ db-> exec ($ sql);
If this query is executed, then the $ result variable will contain the number of rows affected in the table.
PDO :: exec () executes INSERT queries, but cannot get data from the database, this is done by the PDO :: query () method. PDO :: exec () only launches an SQL query for execution and returns the number of rows used during its execution, it does not return the result of a selection with a SELECT statement.
PDO works with databases. If you can't work with some type of database, go to php.ini and look for lines starting with extension = php_pdo_ (database name), and uncomment them.
Php for Beginners: Callback Functions Lesson 34! https://www.youtube.com/watch?v=2NwLHXUoXcw https://www.youtube.com/watch?v=GMzI6jR_bE4 https://www.youtube.com/watch?v=gFJsBQIqpto PHP Lesson 9 Recursion https : //www.youtube.com/watch? v = gLAeJcKkd6c http://php.net/manual/ru/mysqli-result.fetch-array.php / * clear the selection results * / mysqli_free_result ($ result); / * close the connection * / mysqli_close ($ link); http://myrusakov.ru/sql-osnovy.html Good article: https://ru.wikipedia.org/wiki/Join_(SQL) OOP PHP. Expansion Tools The chapter "Expansion Tools" from Mat Zandstra's book PHP. Objects, Patterns, and Programming Techniques. https://www.youtube.com/watch?v=6L2bxtTBCRo
http://phpfaq.ru/pdo#intro - there is a good article. There is important information about exceptions.
Easier described: http://myrusakov.ru/php-data-objects.html
Video: https://www.youtube.com/watch?v=ACUiBH5qV0U&list=PLr_acfJGVcirEijJXmKxj8QGkWkKb-Tj-&nohtml5=False
Introduction
Optionally, the --with-mysql-sock [= DIR] sets to location to the MySQL unix socket pointer for all MySQL extensions, including PDO_MYSQL. If unspecified, the default locations are searched.
Optionally, the --with-zlib-dir [= DIR] is used to set the path to the libz install prefix.
$ ./configure --with-pdo-mysql --with-mysql-sock = / var / mysql / mysql.sock
| Version | Description |
|---|---|
| 5.4.0 | mysqlnd became the default MySQL library when compiling PDO_MYSQL. Previously, libmysqlclient was the default MySQL library. |
| 5.4.0 | MySQL client libraries 4.1 and below are no longer supported. |
| 5.3.9 | Added SSL support with mysqlnd and OpenSSL. |
| 5.3.7 | Added SSL support with libmysqlclient and OpenSSL. |
Predefined Constants
The constants below are defined by this driver, and will only be available when the extension has been either compiled into PHP or dynamically loaded at runtime. In addition, these driver-specific constants should only be used if you are using this driver. Using driver-specific attributes with another driver may result in unexpected behavior. PDO :: getAttribute () may be used to obtain the PDO :: ATTR_DRIVER_NAME attribute to check the driver, if your code can run against multiple drivers.
PDO :: MYSQL_ATTR_USE_BUFFERED_QUERY (integer) If this attribute is set to TRUE on a PDOStatement, the MySQL driver will use the buffered versions of the MySQL API. If you "re writing portable code, you should use PDOStatement :: fetchAll () instead.Example # 1 Forcing queries to be buffered in mysql
if ($ db -> getAttribute (PDO :: ATTR_DRIVER_NAME) == "mysql") (
$ stmt = $ db -> prepare ("select * from foo",
array (PDO :: MYSQL_ATTR_USE_BUFFERED_QUERY => true));
) else (
die ( "my application only works with mysql; I should use \ $ stmt-> fetchAll () instead");
}
?>
Enable LOAD LOCAL INFILE.
PDO :: MYSQL_ATTR_INIT_COMMAND (integer)Command to execute when connecting to the MySQL server. Will automatically be re-executed when reconnecting.
Note, this constant can only be used in the driver_options array when constructing a new database handle.
PDO :: MYSQL_ATTR_READ_DEFAULT_FILE (integer)Read options from the named option file instead of from my.cnf. This option is not available if mysqlnd is used, because mysqlnd does not read the mysql configuration files.
PDO :: MYSQL_ATTR_READ_DEFAULT_GROUP (integer)Read options from the named group from my.cnf or the file specified with MYSQL_READ_DEFAULT_FILE... This option is not available if mysqlnd is used, because mysqlnd does not read the mysql configuration files.
PDO :: MYSQL_ATTR_MAX_BUFFER_SIZE (integer)Maximum buffer size. Defaults to 1 MiB. This constant is not supported when compiled against mysqlnd.
PDO :: MYSQL_ATTR_DIRECT_QUERY (integer)Perform direct queries, don "t use prepared statements.
PDO :: MYSQL_ATTR_FOUND_ROWS (integer)Return the number of found (matched) rows, not the number of changed rows.
PDO :: MYSQL_ATTR_IGNORE_SPACE (integer)Permit spaces after function names. Makes all functions names reserved words.
PDO :: MYSQL_ATTR_COMPRESS (integer)Enable network communication compression. This is also supported when compiled against mysqlnd as of PHP 5.3.11.
PDO :: MYSQL_ATTR_SSL_CA (integer)The file path to the SSL certificate authority.
This exists as of PHP 5.3.7.
PDO :: MYSQL_ATTR_SSL_CAPATH (integer)The file path to the directory that contains the trusted SSL CA certificates, which are stored in PEM format.
This exists as of PHP 5.3.7.
PDO :: MYSQL_ATTR_SSL_CERT (integer)The file path to the SSL certificate.
This exists as of PHP 5.3.7.
PDO :: MYSQL_ATTR_SSL_CIPHER (integer)A list of one or more permissible ciphers to use for SSL encryption, in a format understood by OpenSSL. For example: DHE-RSA-AES256-SHA: AES128-SHA
This exists as of PHP 5.3.7.
PDO :: MYSQL_ATTR_SSL_KEY (integer)The file path to the SSL key.
This exists as of PHP 5.3.7.
PDO :: MYSQL_ATTR_SSL_VERIFY_SERVER_CERT (integer)Provides a way to disable verification of the server SSL certificate.
This exists as of PHP 7.0.18 and PHP 7.1.4.
PDO :: MYSQL_ATTR_MULTI_STATEMENTS (integer)Disables multi query execution in both PDO :: prepare () and PDO :: query () when set to FALSE.
Note, this constant can only be used in the driver_options array when constructing a new database handle.
This exists as of PHP 5.5.21 and PHP 5.6.5.
Runtime configuration
The behavior of these functions is affected by settings in php.ini.
| Name | Default | Changeable |
|---|---|---|
| pdo_mysql.default_socket | "/tmp/mysql.sock" | PHP_INI_SYSTEM |
| pdo_mysql.debug | NULL | PHP_INI_SYSTEM |
Here "s a short explanation of the configuration directives.
Sets a Unix domain socket. This value can either be set at compile time if a domain socket is found at configure. This ini setting is Unix only.
Pdo_mysql.debug boolean
Enables debugging for PDO_MYSQL. This setting is only available when PDO_MYSQL is compiled against mysqlnd and in PDO debug mode.
Table of Contents
- PDO_MYSQL DSN - Connecting to MySQL databases
Than install them
rpm -Uvh remi-release-26.rpm
rpm -Uvh epel-release-6-8.noarch.rpm
Know you can use remi repository to gest php-pdo and php-mysql.
yum --enablerepo = remi install php-pdo
yum --enablerepo = remi install php-mysql
Restart the Apache
systemctl stop httpd
systemctl start httpd
Good to go!
10 years ago
SQLSTATE: General error: 2014 Cannot execute queries while other unbuffered queries are active. ...
This one can be a royal pain to deal with. Never stack statements to be executed in one go. Nobody ever mentions this possibility in all the posts I "ve seen dealing with this error.
This example is a Zend Framework example but the theory is the same.
$ sql =<<<____SQL
`tid` int (11) NOT NULL,
`tgen` datetime NOT NULL,
`tterm` datetime,
`rqid` int (11) NOT NULL,
`rqtid` int (11) NOT NULL,
`rqsid` int (11) NOT NULL,
`rqdate` datetime NOT NULL,
`rssid` int (11) NOT NULL,
`rsdate` datetime,
`rscode` tinyint (1)
`rqid` int (5) NOT NULL,
`sid` int (11) NOT NULL,
`rlsid` int (11) NOT NULL,
`dcode` varchar (5) NOT NULL
____SQL;
$ result = $ this -> db -> getConnection () -> exec ($ sql);
?>
This will run fine but PDO will balk with the "unbuffered" error if you follow this with another query.
$ sql =<<<____SQL
CREATE TABLE IF NOT EXISTS `ticket_hist` (
`tid` int (11) NOT NULL,
`trqform` varchar (40) NOT NULL,
`trsform` varchar (40) NOT NULL,
`tgen` datetime NOT NULL,
`tterm` datetime,
`tstatus` tinyint (1) NOT NULL
) ENGINE = ARCHIVE COMMENT = "ticket archive";
____SQL;
$ result = $ this -> db -> getConnection () -> exec ($ sql);
$ sql =<<<____SQL
CREATE TABLE IF NOT EXISTS `request_hist` (
`rqid` int (11) NOT NULL,
`rqtid` int (11) NOT NULL,
`rqsid` int (11) NOT NULL,
`rqdate` datetime NOT NULL,
`rqcode` tinyint (1) NOT NULL,
`rssid` int (11) NOT NULL,
`rsdate` datetime,
`rscode` tinyint (1)
) ENGINE = ARCHIVE COMMENT = "request archive";
____SQL;
$ result = $ this -> db -> getConnection () -> exec ($ sql);
$ sql =<<<____SQL
CREATE TABLE IF NOT EXISTS `relay_hist` (
`rqid` int (5) NOT NULL,
`sdesc` varchar (40) NOT NULL,
`rqemail` varchar (40) NOT NULL,
`sid` int (11) NOT NULL,
`rlsid` int (11) NOT NULL,
`dcode` varchar (5) NOT NULL
) ENGINE = ARCHIVE COMMENT = "relay archive";
____SQL;
$ result = $ this -> db -> getConnection () -> exec ($ sql);
?>
Chopping it into individual queries fixes the problem.
Loading...
