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.

Copy code The code is as follows:

$test=new PDO();

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.

Copy code The code is as follows:

$dsn = 'mysql:dbname=demo;host=localhost;port=3306';
$username = 'root';
$password = 'password_here';
try {
$db = new PDO($dsn, $username, $password);
} catch(PDOException $e) {
Die('Could not connect to the database:
' . $e);
}

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.

Copy code The code is as follows:

$statement = << SELECT *
FROM `foods`
WHERE `healthy` = 0
SQL;

$foods = $db->query($statement);

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:

Copy code The code is as follows:

echo $foods->rowCount;

0x05: Traverse the result set

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

Copy code The code is as follows:

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

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.

Copy code The code is as follows:

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

After transferring using quote method:

Copy code The code is as follows:

$db->quote($input): 'this is's' a '''pretty dange'rous str'ing'

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:

Copy code The code is as follows:

$statement = << DELETE FROM `foods`
WHERE `healthy` = 1;
SQL;
echo $db->exec($statement);

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:

Copy code The code is as follows:

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

Here is our first prepared statement:

Copy code The code is as follows:

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

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:

Copy code The code is as follows:

$statement->bindValue(1, 'Cake');
$statement->bindValue(2, true);

$statement2->bindValue(':name', 'Pie');
$statement2->bindValue(':healthy', false);

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:

Copy code The code is as follows:

$statement->execute();
$statement2->execute();

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

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:

Copy code The code is as follows:

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

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:

Copy code The code is as follows:

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

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

Copy code The code is as follows:

$db->commit();

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.

Copy code The code is as follows:

$options = array($option1 => $value1, $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, 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:

Copy code The code is as follows:

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

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.