Detailed explanation of the PDO class in PHP, detailed explanation of the PHPPDO class_PHP tutorial

Detailed explanation of PDO class in PHP, detailed explanation of PHPPDO class

Introduction

Let’s take a look at the PDO class. PDO is the abbreviation of PHP Data Objects, which is described as "a lightweight, compatible interface for accessing databases in PHP." Despite its unpleasant name, PDO is a lovable way of accessing databases in PHP.
Differences from MySQLi

MySQLi and PDO are very similar, with two main differences:

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

2.PDO does not have ordinary function calls (mysqli_*functions)
Start Steps

First of all, you have to make sure whether your PHP has the PDO plug-in installed. You can use the result of $test=new PDO() to test. If the prompt says that the parameters do not match, it proves that the PDO plug-in has been installed. If it says that the object does not exist, you must first confirm whether php_pdo_yourssqlserverhere.extis is commented out in pho.ini. If there is no such sentence, then you have to install PDO, so I won’t go into details here.

Connect

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($dsn, $username, $password); // also allows an extra parameter of configuration
} catch(PDOException $e) {
 die('Could not connect to the database:<br/>' . $e);
}

With the exception of $dsn, all statements and variables are self-explanatory. DSN refers to the data source name, and there are multiple input types. The most common one is the one we just used. The PHP official website explains other available DSNs.

You can omit other additional parameters of DSN and just put a colon after the database driver, such as (mysql:). In this case PDO will try 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 note is that we wrap our initialization object with a try-catch block. A PDOException will be thrown when the PDO connection fails instead of when the query fails. If you wish you can use the following code $db=line to select the exception mode.

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

Or you can pass parameters directly during PDO initialization:

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

What we are using now is the wrong way - simply returning false when it fails. There is no reason for us not to handle exceptions.

Basic query

Using query and exec methods in PDO makes database query very simple. If you want to get the number of rows in the query result, exec is very easy to use, so it is very useful for SELECT query statements.

Now let’s look at both methods with an example below:

$statement = <<<SQL
 SELECT *
 FROM `foods`
 WHERE `healthy` = 0
SQL;
 
$foods = $db->query($statement);

Assuming our query is correct, $foods is now a PDO Statement object. We can use it to get our results or check how many result sets were found in this query.
Number of rows

The disadvantage is that PDO does not provide a unified method to calculate the number of returned rows. PDO Statement contains a method called rowCount, but this method is not guaranteed to work in every SQL driver (fortunately, it can work in the Mysql database).

If your SQL driver does not support this method, you also have 2 options: use a secondary query (SELECT COUNT(*)) or use a 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();

Iterate through the result set

It’s not hard at all to print out these delicious treats:

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

The only thing to note is that PDO also supports the Fetch method. This method will only return the first result, which is very 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 safe data. PDO provides a method called quote, which can escape special characters in quotation marks in the input string.

$input: this is's' a '''pretty dange'rous str'ing

After escaping, we finally get 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. After execution, it will return the number of affected rows:

$statement = <<<SQL
 DELETE FROM `foods`
 WHERE `healthy` = 1;
SQL;
 
echo $db->exec($statement); // outputs number of deleted rows

Prepared statements

Although exec methods and queries are still widely used and supported in PHP, the PHP official website still requires everyone to use prepared statements instead. Why? Mainly because: it's safer. Prepared 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 simulates preprocessing and inserts parameter data into the statement before passing it to the SQL server. This makes some The system is vulnerable to SQL injection.

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

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

Let’s start with our first prepared statement:

$statement = $db->prepare('SELECT * FROM foods WHERE `name`=? AND `healthy`=?');
$statement2 = $db->prepare('SELECT * FROM foods WHERE `name`=:name AND `healthy`=:healthy)';

正如你所见，有两种创建参数的方法，命名的与匿名的（不可以同时出现在一个语句中）。然后你可以使用bindValue来敲进你的输入：

$statement->bindValue(1, 'Cake');
$statement->bindValue(2, true);
 
$statement2->bindValue(':name', 'Pie');
$statement2->bindValue(':healthy', false);

注意使用命名参数的时候你要包含进冒号(:)。PDO还有一个bindParam方法，可以通过引用绑定数值，也就是说它只在语句执行的时候查找相应数值。

现在剩下的唯一要做的事情，就是执行我们的语句：

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

为了避免只使用bindValue带来的代码碎片，你可以用数组给execute方法作为参数，像这样：

$statement->execute(array(1 => 'Cake', 2 => true));
$statement2->execute(array(':name' => 'Pie', ':healthy' => false));

事务

前面我们已经描述过了什么是事务：

一个事务就是执行一组查询,但是并不保存他们的影响到数据库中。这样做的好处是如果你执行了4条相互依赖的插入语句,当有一条失败后,你可以回滚使得其他的数据不能够插入到数据库中,确保相互依赖的字段能够正确的插入。你需要确保你使用的数据库引擎支持事务。
开启事务

你可以很简单的使用beginTransaction()方法开启一个事务：

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

然后你可以继续执行你的数据库操作语句，在最后提交事务：

$db->commit();

还有类似MySQLi中的rollBack()方法，但是它并不是回滚所有的类型（例如在MySQL中使用DROP TABLE）,这个方法并不是真正的可靠，我建议尽量避免依赖此方法。

其他有用的选项

有几个选项你可以考虑用一下。这些可以作为你的对象初始化时候的第四个参数输入。

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

你可以选择PDO将返回的是什么类型的结果集，如PDO::FETCH_ASSOC，会允许你使用$result['column_name']，或者PDO::FETCH_OBJ，会返回一个匿名对象，以便你使用$result->column_name

你还可以将结果放入一个特定的类（模型），可以通过给每一个单独的查询设置一个读取模式，就像这样：

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

上面我们已经解释过这一条了，但喜欢TryCatch的人需要用到：PDO::ERRMODE_EXCEPTION。如果不论什么原因你想抛出PHP警告，就使用PDO::ERRMODE_WARNING。

PDO::ATTR_TIMEOUT

当你为载入时间而着急时,你可以使用此属性来为你的查询指定一个超时时间，单位是秒. 注意，如果超过你设置的时间，缺省会抛出E_WARNING异常, 除非 PDO::ATTR_ERRMODE 被改变.

更多属性信息可以在 PHP官网的属性设置 里查看到.
最后的思考

PDO是一个在PHP中访问你的数据库的很棒的方式，可以认为是最好的方式。除非你拒绝使用面向对象的方法或是太习惯 MySQLi 的方法名称，否则没有理由不使用PDO。

更好的是完全切换到只使用预处理语句，这最终将使你的生活更轻松！