Details about the PDO class in PHP

This article mainly introduces the PDO class in PHP. The PDO class can help people use the database more conveniently in PHP. For more information, see

This article mainly introduces the PDO class in PHP. The PDO class can help people use the database more conveniently in PHP. For more information, see

Introduction

Let's take a look at the PDO class. PDO, short for PHP Data Objects, is described as a lightweight and compatible interface for accessing databases in PHP ". Although its name is not nice, PDO is a popular way to access the database in PHP.
Different from MySQLi

MySQLi and PDO are very similar and there are two main differences:

1. MySQLi can only access MySQL, but PDO can access 12 different databases

2. PDO does not call common functions (mysqli _ * functions)
Start step

First, check whether your PHP has installed the PDO plug-in. You can use the result of $ test = new PDO () to test. If the prompt says the parameter does not match, it indicates that the PDO plug-in has been installed. If the object does not exist, check whether php_pdo_yourssqlserverhere.extis has been commented out in pho. ini. If you do not have such a statement, you have to install PDO.

Connection

Now let's confirm that the server is working and start to connect to the database:

$ Dsn = 'mysql: dbname = demo; host = localhost; port = 100'; $ username = 'root'; $ password = 'password _ here '; try {$ db = new PDO ($ dsn, $ username, $ password); // also allows an extra parameter of configuration} catch (PDOException $ e) {die ('could not connect to the database:
'. $ E );}

All statements and variables except $ dsn can be self-explanatory. DSN refers to the data source name, which has multiple input types. The most common one is the one we just used, which is explained on the PHP official website.

You can save the other parameters of DSN by adding a colon after the database driver, such as (mysql :). In this case, PDO tries to connect to the local database. Just as you need to specify the database name in the query when using MySQLi.

The last thing you need to pay attention to is that we wrap our initialization object with the try-catch Block. When the PDO connection fails, a PDOException exception is thrown instead of a query failure. If you want to, you can use the following code $ db = line to select the exception mode.

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

Alternatively, you can directly PASS Parameters during PDO initialization:

$ Db = new PDO ($ dsn, $ username, $ password, array (PDO: ATTR_ERRMODE => PDO: ERRMODE_EXCEPTION ));

We are now using the error method-simply returning false when the failure occurs, so we have no reason not to handle the exception.

Basic Query

Using query and exec in PDO makes it very easy to query databases. If you want to obtain the number of rows in the query result exec, it is very useful for SELECT query statements.

The following example shows the two methods:

$ Statement = < Query ($ statement );

Suppose our query is correct. $ foods is now a PDO Statement object. We can use it to get our results or view the total number of result sets found in this query.
Number of rows

The disadvantage is that PDO does not provide a uniform method to calculate the number of returned rows. PDO Statement contains a method called rowCount, but this method cannot be used in every SQL Driver (fortunately, it can play a role in the Mysql database ).

If your SQL driver does not support this method, you also have two options: Use a secondary query (SELECT COUNT (*) or use a simple count ($ foods) to obtain the number of rows.

Fortunately, for our MySQL example, we can use the following simple method to output the correct number of rows.

Echo $ foods-> rowCount ();

Traverse result set

It is not difficult to print out these delicious foods:

Foreach ($ foods-> FetchAll () as $ food) {echo $ food ['name'].'
';}

The only thing to note is that PDO also supports the Fetch method. This method returns only the first result, which is very useful for querying only one result set.
Escape user input (special characters)

You have heard of (mysqli _) real_escape_string, which is used to ensure secure data input by users. PDO provides a method called quote, which can escape special characters in the quotation marks in the input string.

$ Input: this is's 'A ''' pretty dange 'rous str ''ing

After escaping, the following result is obtained:

$ Db-> quote ($ input): 'This is \'s \ 'a \ '\ 'pretty dange \ 'rous str \ 'in' exec ()

As mentioned above, you can use the exec () method to perform UPDATE, DELETE, and INSERT operations. After execution, it returns the number of affected rows:

$ Statement = < Exec ($ statement); // outputs number of deleted rows

Preprocessing statement

Although exec methods and queries are still widely used and supported in PHP, the official PHP Website still requires that you use preprocessing statements instead. Why? This is mainly because it is safer. The pre-processing statement does not insert parameters directly in the actual query, which avoids many potential SQL injections.

However, for some reason, PDO does not actually use preprocessing. It simulates preprocessing and inserts the parameter data into the statement before passing the statement to the SQL server, this makes some systems vulnerable to SQL injection.

If your SQL server does not really support preprocessing, we can easily solve this problem by passing parameters during PDO initialization as follows:

$ Db-> setAttribute (PDO: ATTR_EMULATE_PREPARES, false );

Next let's start our first preprocessing statement:

$ Statement = $ db-> prepare ('select * FROM foods WHERE 'name' =? AND 'health' =? '); $ Statement2 = $ db-> prepare ('select * FROM foods WHERE 'name' =: name AND 'healthy' =: healthy )';

As you can see, there are two methods to create parameters: named and anonymous (it cannot appear in a statement at the same time ). Then you can use bindValue to input your input:

$ Statement-> bindValue (1, 'cake'); $ statement-> bindValue (2, true); $ statement2-> bindValue (': name', 'pie '); $ statement2-> bindValue (': healthy', false );

Note that you must include the colon (:) when using the named parameter (:). PDO also has a bindParam method, which can be used to reference a bound value, that is, it only searches for the corresponding value when the statement is executed.

The only thing that remains to be done is to execute our statement:

$ Statement-> execute (); $ statement2-> execute (); // Get our results: $ cake = $ statement-> Fetch (); $ pie = $ statement2-> Fetch ();

To avoid using only the code fragments that bindValue brings, you can use an array to give the execute method as a parameter, like this:

$ Statement-> execute (array (1 => 'cake', 2 => true); $ statement2-> execute (array (': name' => 'pie ', ': healthy' => false ));

Transactions

We have already described what transactions are: