Problem description:
We all know that since PHP 5.1.0, PDO has been a standard part of PHP. PDO provides 3 classes PDO, PDOStatement and PDOException, PDOException needless to say. The function and positioning are very clear from the name.
Then the question arises: Why do the two classes PDO and PDOStatement exist at the same time?
Why are there such questions? First look at the picture below (picture taken from @PHP official website manual):
The picture shows the methods declared by the PDO and PDOStatement classes. It can be seen that although most of the methods provided by these two classes are different, the core methods are obviously Overlap or repeat, such as:
PDO::query(), PDO::exec() both execute a SQL statement, but the returns are different
PDOStatement::execute() also executes a SQL statement, but the statement has been preprocessed
PDOStatement::execute() is okay, but PDO::query() and PDO::exec() exist at the same time What is the necessity? It will also cause difficulties in use and understanding.
Well, even if there is a necessity to exist at the same time
then there is still a pointWhy do these two classes exist at the same time? Instead of one class (if there is only the PDO class), what about doing what these two classes can do together?
What is the relationship between the PDO class and the PDOStatement class?
If the PDO class is used to execute SQL and manage connections, and the PDOStatement class is only used to process result sets, then it feels much more comfortable and smooth.
I hope someone can explain the design considerations of PHP's PDO. Thank you sincerely~
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
My understanding is that one is used to execute ordinary SQL, and the other can be used for parameter binding and the like...
I will tell you my personal understanding. Please correct me if there are any mistakes.
First look at the PDO class
You can see that both query() and prepare() return a PDOStatement object, which means that PDOStatement can operate the result set.
Looking at PDO::prepare again, the manual says that it executes a prepared statement. In fact, it gets a prepared statement PDOStatement, and then actually executes SQL by calling PDOStatement::execute().
Usually some of our projects use prepare to execute SQL statements. This approach is to prevent SQL injection and improve the query performance of the same template SQL. The official manual content is introduced:
The query only needs to be parsed (or prepared) once, but can be executed multiple times with the same or different parameters. When the query is prepared, the database will analyze, compile and optimize its plan for executing the query. For complex queries this process can take up enough time that it will noticeably slow down an application if there is a need to repeat the same query many times with different parameters. By using a prepared statement the application avoids repeating the analyze/compile/optimize cycle. This means that prepared statements use fewer resources and thus run faster.
The parameters to prepared statements don't need to be quoted; the driver automatically handles this. If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible).
As for what the poster said about the difference between PDO::query and PDO::exec, as follows:
query only executes a select statement, and exec executes select, insert, update,delete
PDO::query executes a SQL statement, and if passed, returns a PDOStatement object
PDO::exec executes a SQL statement and returns the number of affected rows. This function does not return a result collection.
PDOStatement::execute was introduced earlier. It is a sub-function under PDOStatement. One feature is that it supports binding parameters without considering SQL injection security issues. Another feature is that it supports multiple executions, the same as template SQL. Help improve performance.
If you only query one statement, the advantage of using query is that the result set returned by query can be traversed directly.
If you use exec to execute it, it will only return the number of affected rows, not the PDOStatement result set. You cannot traverse it directly. Follow official recommendations to use query or execute.
This is roughly what I understand.
The above three methods can all execute SQL. If you find it confusing, you can only use the third method.
If you execute SQL with input parameters, in order to prevent SQL injection, you should use prepare:🎜Because as long as exec and query can achieve it, prepare+execute can also achieve it.
And prepare+execute can achieve it. , such as preprocessing parameterized queries, exec and query cannot be implemented.
Exec and query appear more for convenience. For example, sometimes the SQL statements we execute do not have external parameters, then using exec and query will To be more concise, obviously exec is more suitable for executing a single write operation (INSERT/UPDATE/DELETE) statement, because exec can directly return the affected rows. If you use query, you have to call rowCount() to get the affected rows, such as :
$db->query($sql)->rowCount();$db->query($sql)->rowCount();当如果你执行没有外来参数的SQL获取SELECT结果,这时则应该用query而不是exec:
$db->query($sql)->fetchAll();When you execute SQL without external parameters to obtain the SELECT result, you should use query instead of exec:$db->query($sql)->fetchAll();