Summary of PHP PDO operations_javascript skills

0x01: Test whether PDO is installed successfully

Run the following code. If the parameter error is prompted, PDO has been installed. If the object does not exist, modify the PHP configuration file php.ini and cancel the comment in front of php_pdo_yourssqlserverhere.extis.

0x02: Connect to database

Run the Apache server, make sure the server is running and PDO is installed successfully, then let’s connect to the database.

0x03: 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.

If the above query is correct, $foods is now a PDO Statement object. We can get the results we need from this object and how many result sets we have queried in total.

0x04: Get the number of lines

If you are using a Mysql database, the PDO Statement contains a rowCount method to get the number of rows in the result set, as shown in the following code:

0x05: Traverse the result set

PDO Statment can be traversed using the forech statement, as shown in the following code:

PDO also supports the Fetch method, which only returns the first result.

0x06: Escape special characters entered by the user

PDO provides a method called quote, which can escape special characters in places with quotes in the input string.

After transferring using quote method:

0x07: exec()

PDO can use the exec() method to implement UPDATE, DELETE and INSERT operations. After execution, it will return the number of affected rows:

0x08: Prepared statement

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:

Here is our first prepared statement:

As shown in the above code, there are two ways to create parameters, named and anonymous (cannot appear in one statement at the same time). You can then use bindValue to type in your input:

Note that you need to include a colon (:) when using named parameters. PDO also has a bindParam method that can bind values ​​by reference, which means that it only looks for the corresponding value when the statement is executed.

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

In order to avoid the code fragmentation caused by using only bindValue, you can use an array as a parameter to the execute method, like this:

0x09: Transaction

A transaction executes a set of queries but does not save their effects in the database. The advantage of this is that if you execute 4 interdependent insert statements, when one fails, you can roll back so that other data cannot be inserted into the database, ensuring that interdependent fields can be inserted correctly. You need to make sure that the database engine you use supports transactions.

0x10: Start transaction

You can simply use the beginTransaction() method to start a transaction:

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

There is also a method similar to the rollBack() in MySQLi, but it does not roll back all types (such as using DROP TABLE in MySQL). This method is not really reliable. I recommend trying to avoid relying on this method.

0x11: Other useful options

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

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, which will return an anonymous object for you to 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:

PDO::ATTR_ERRMODE

We have already explained this above, but people 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 loading time, you can use this attribute to specify a timeout for your query, in seconds. Note that if the time you set is exceeded, an E_WARNING exception will be thrown by default, unless PDO::ATTR_ERRMODE was changed.