Details about the PDO class in PHP and the PHPPDO class _ PHP Tutorial

Details about the PDO class in PHP and the PHPPDO class. Let's take a look at the PDO class in PHP. PDO is the abbreviation of PHPDataObjects. it is described as "lightweight database access in PHP, compatibility interface details PDO class in PHP, details PHPPDO class

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=3306';$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. The PHP official website explains other available DSN.

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\'ing'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 `healthy`=?');$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:

A transaction executes a group of queries, but does not store them, which affects the database. The advantage of doing so is that if you execute four mutually dependent insert statements, when one fails, you can roll back so that other data cannot be inserted into the database, make sure that the interdependent fields are correctly inserted. Make sure that the database engine you are using supports transactions.
Start transaction

You can easily start a transaction using the beginTransaction () method:

$db->beginTransaction(); $db->inTransaction(); // true!

Then you can continue to execute your database operation statement and commit the transaction at the end:

$db->commit();

The rollBack () method in MySQLi is similar, but it does not roll back all types (for example, using drop table in MySQL). This method is not truly reliable, I recommend that you try to avoid dependency on this method.

Other useful options

There are several options you can consider. These can be used as the fourth parameter input during object initialization.

 $options = array($option1 => $value1, $option[..]);$db = new PDO($dsn, $username, $password, $options);

PDO: ATTR_DEFAULT_FETCH_MODE

You can select the type of result set that will be returned by PDO, such as PDO: FETCH_ASSOC, which allows you to use $ result ['column _ name'], or PDO: FETCH_OBJ, returns an anonymous object so that you can use $ result-> column_name

You can also put the results into a specific class (model). you can set a Read mode for each individual query, just like this:

$query = $db->query('SELECT * FROM `foods`');$foods = $query->fetchAll(PDO::FETCH_CLASS, 'Food');PDO::ATTR_ERRMODE

-All read modes

We have explained this article above, but those who like TryCatch need to use: PDO: ERRMODE_EXCEPTION. If you want to throw a PHP warning for whatever reason, use PDO: ERRMODE_WARNING.

PDO: ATTR_TIMEOUT

When you are worried about the loading time, you can use this attribute to specify a timeout value for your query, in seconds. note: if the specified time is exceeded, the E_WARNING exception will be thrown by default, unless the PDO: ATTR_ERRMODE is changed.

For more information about attributes, see attribute settings on the PHP official website.
Last thought

PDO is a great way to access your database in PHP and can be considered as the best way. Unless you refuse to use the object-oriented method or the method name that is too accustomed to MySQLi, there is no reason not to use PDO.

What's better is to completely switch to the use of only preprocessing statements, which will eventually make your life easier!

Let's take a look at the PDO class. PDO is short for PHP Data Objects. it is described as a lightweight, compatible interface for accessing databases in PHP...