PHP's detailed PDO

This article mainly introduces the PDO class in PHP, the PDO class can help people to use the database in PHP more conveniently. We hope to help you with PDO.

Brief introduction

Let's look at the PDO class together. PDO is the abbreviation for PHP Data objects, which is described as "a lightweight, compatible interface for accessing databases in PHP." Although its name is not good, PDO is a favorite way to access a database in PHP.
Different from the Mysqli

Mysqli and PDO are very similar and have two main differences:

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

2.PDO no normal function call (mysqli_*functions)
Start step

First, you need to determine if your PHP has a PDO plugin installed. You can test it with the results of $test=new PDO (). If the hint is that the parameter does not match, it proves that the PDO plugin has been installed, if the object does not exist, you must first confirm whether the Php_pdo_yourssqlserverhere.extis is commented out in Pho.ini. If you don't have that, you'll have to install PDO, and you won't have to talk about it here.

Connection

Now we confirm that the server is working and start connecting to the database:

$dsn = ' mysql:dbname=demo;host=localhost;port=3306 '; $username = ' root '; $password = ' password_here '; try {$db = new PDO ($d SN, $username, $password); Also allows an extra parameter of a configuration} catch (Pdoexception $e) {die (' Could not connect to the DATABASE:<BR /> '. $E);}

All statements and variables can be self-explanatory except for $DSN. DSN refers to the data source name and has multiple input types. The most common is the one we just used, and the PHP website explains the other available DSNs.

You can omit other extra parameters for DSN, just take a colon after the database driver, for example (MySQL:). In this case, the PDO will attempt to connect to the local database. Just like when you use mysqli, you need to specify the database name in the query.

The last thing you need to be aware of is that we wrap our initialization objects with try-catch blocks. When the PDO connection fails, it throws a Pdoexception exception instead of a query failure. If you want you can use the following code $db=line to select the pattern of the exception.

$db->setattribute (Pdo::attr_errmode, pdo::errmode_exception);

Or you can pass parameters directly on the PDO initialization:

$db = new PDO ($DSN, $username, $password, array (pdo::attr_errmode = pdo::errmode_exception));

We are now using the wrong way-simply returning false at the time of failure, there is no reason not to handle the exception.

Basic Query

Using the query and exec two methods in PDO makes database queries very simple. If you want the number of rows for the query result, exec is very useful, so it is very helpful for select query statements.

Now let's look at both of these methods in one of the following examples:

$statement = <<<sql SELECT * from ' foods ' WHERE ' healthy ' = 0SQL; $foods = $db->query ($statement);

Assuming our query is correct, $foods is now a PDO statement object that we can use to get our results or to see how many result sets are found in this query.
Number of rows

The disadvantage is that PDO does not provide a uniform way to calculate the number of rows returned. PDO statement contains a method called rowcount, but this method does not guarantee a function in every SQL driver (fortunately, it works in MySQL database).

If your SQL driver does not support this method, you also have 2 choices: use two queries (select COUNT (*)) or use simple count ($foods) to get 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 ();

Traversing result Sets

It is not difficult to print out these delicious foods:

foreach ($foods->fetchall () as $food) {echo $food [' name ']. ' <br/> ';}

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

Have you ever heard of (Mysqli_) real_escape_string, which is used to ensure that users enter security data. PDO provides a method called quote, which allows you to escape special characters with quotes in the input string.

$input: This is ' s ' a ' pretty Dange ' rous str ' ing

After escaping, you end up with the following result:

$db->quote ($input): ' This is\ ' s\ ' a \ ' \ ' \ ' Pretty dange\ ' rous str\ ' ing ' exec ()

As mentioned above, you can use the Exec () method to implement Update,delete and insert operations, which return the number of rows affected after execution:

$statement = <<<sql DELETE from ' Foods ' WHERE ' healthy ' = 1; SQL; echo $db->exec ($statement); Outputs number of deleted rows

Preprocessing statements

Although the exec methods and queries are still heavily used and supported in PHP, the PHP official web requires that you replace them with pre-processing statements. Why is it? Mainly because: it is more secure. Preprocessing statements do not insert parameters directly into the actual query, which avoids many potential SQL injections.

However, for some reason, PDO does not actually use preprocessing, it is in the simulated preprocessing method, the parameter data is inserted into the statement before the statement is passed to the SQL Server, which makes some systems vulnerable to SQL injection.

If your SQL Server does not really support preprocessing, we can easily fix this problem by initializing the Shishun in PDO as follows:

$db->setattribute (Pdo::attr_emulate_prepares, false);

Now let's start with our first preprocessing statement:

$statement = $db->prepare (' SELECT * from Foods WHERE ' name ' =? and ' healthy ' =? '); $statement 2 = $db->prepare (' SELECT * from Foods WHERE ' name ' =:name and ' healthy ' =:healthy) ';

As you can see, there are two ways to create parameters, named and Anonymous (not in one statement at the same time). Then you can use the Bindvalue to tap into your input:

$statement->bindvalue (1, ' Cake '); $statement->bindvalue (2, true); $statement 2->bindvalue (': Name ', ' Pie '), $statement 2->bindvalue (': Healthy ', false);

Note that when you use named arguments, you include a colon (:). PDO also has a Bindparam method that can be used to bind a value by reference, that is, it finds the corresponding value only when the statement executes.

The only thing left to do now is to execute our statement:

$statement->execute (); $statement 2->execute (); Get Our results: $cake = $statement->fetch (); $pie = $statement 2->fetch ();

To avoid the use of Bindvalue-only code fragmentation, you can use an array to give the Execute method a parameter, like this:

$statement->execute (Array (1 = ' Cake ', 2 = True)), $statement 2->execute (': Name ' = ' Pie ', ': Healthy ' = false));

Transaction

We have described what is a transaction before:

A transaction is the execution of a set of queries, but does not save their impact to the database. The advantage of this is that if you execute 4 interdependent INSERT statements, when one fails, you can rollback so that other data cannot be inserted into the database, ensuring that the interdependent fields are inserted correctly. You need to make sure that the database engine you are using supports transactions.
Open transaction

You can simply use the BeginTransaction () method to open a transaction:

$db->begintransaction (); $db->intransaction (); true!

Then you can proceed to execute your database operation statement at the last commit transaction:

$db->commit ();

There is a rollback () method similar to mysqli, but it does not roll back all types (for example, using Drop TABLE in MySQL), this method is not really reliable, and I recommend that you try to avoid relying on this method.

Other useful options

There are several options you can consider using. These can be entered as the fourth parameter when your object is initialized.

$options = Array ($option 1 = $value 1, $option [...]); $db = new PDO ($DSN, $username, $password, $options);

Pdo::attr_default_fetch_mode

You can choose what type of result set PDO will return, such as PDO::FETCH_ASSOC, which will allow you to use $result[' column_name ', or pdo::fetch_obj, to return an anonymous object so that you use $result- >column_name

You can also put the results into a specific class (model) by setting a read mode for each individual query, like this:

$query = $db->query (' SELECT * from ' foods ") $foods = $query->fetchall (pdo::fetch_class, ' food ');

-All read modes

We have explained this in the above, but people who like Trycatch need to use: pdo::errmode_exception. If you want to throw PHP warnings for whatever reason, use pdo::errmode_warning.

Pdo::attr_timeout

When you are worried about loading time, you can use this property to specify a time-out for your query in seconds. Note that if you set the time above, the missing capital throws a e_warning exception unless Pdo::attr_errmode is changed.

More property information can be found in the properties settings of the PHP website.
The final thought

PDO is a great way to access your database in PHP and can be considered the best way. There is no reason not to use PDO unless you refuse to use an object-oriented approach or are too accustomed to mysqli method names.

The better is to switch completely to using only pre-processing statements, which will ultimately make your life easier!

Related recommendations:

How PHP uses PDO to access Oracle databases in a detailed way

Introduction to the functional operation of the PDO data Access abstraction layer in PHP