0x01: Test whether PDO is installed successfully
Run the following code. If the parameter is wrong, it means that PDO has been installed. If the object does not exist, modify the PHP configuration file php.ini and cancel the comments in the previous php_pdo_yourssqlserverhere.extis.
The code copy is as follows:
$test=new PDO();
0x02: Connect to the database
Run the Apache server and confirm that the server has been run and the PDO installation is successful. Then let’s connect to the database.
The code copy 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 in PDO makes querying databases very simple. If you want to get the number of rows of the query result, it is very useful for SELECT query statements.
The code copy is as follows:
$statement = <<<SQL
SELECT *
FROM `foods`
WHERE `healthy` = 0
SQL;
$foods = $db->query($statement);
If the query above is correct, $foods is now a PDO Statement object, from which we can get the results we need and how many result sets we have queried in total.
0x04: Get the number of rows
If you are using Mysql database, the PDO Statement contains a rowCount method to obtain the number of rows in the result set, as shown in the following code:
The code copy is as follows:
echo $foods->rowCount;
0x05: traversal result set
PDO Statment can be traversed using the forech statement, as shown in the following code:
The code copy 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 the special characters entered by the user
PDO provides a method called quote, which allows special characters to escape the places with quotes in the input string.
The code copy is as follows:
$input= this is's' a '''pretty danger'rous str'ing
After transfer using the quote method:
The code copy is as follows:
$db->quote($input): 'this is/'s/' a /'/'/'pretty danger/'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:
The code copy is as follows:
$statement = <<<SQL
DELETE FROM `foods`
WHERE `healthy` = 1;
SQL;
echo $db->exec($statement);
0x08: Preprocessing statement
Although the exec method and query are still widely used and supported in PHP, the official website of PHP still requires everyone to use preprocessing statements to replace them. Why? Mainly because: it is safer. Preprocessing statements do not directly insert parameters into actual queries, which avoids many potential SQL injections.
However, for some reason, PDO does not actually really use preprocessing. It simulates preprocessing methods, inserts parameter data into statements before passing the statement to the SQL server, which makes some systems susceptible 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:
The code copy is as follows:
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
Here is our first preprocessing statement:
The code copy 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, name and anonymous (cannot appear in one statement at the same time). Then you can use bindValue to type in your input:
The code copy is as follows:
$statement->bindValue(1, 'Cake');
$statement->bindValue(2, true);
$statement2->bindValue(':name', 'Pie');
$statement2->bindValue(':healthy', false);
Note that when using named parameters, you must include the colon (:). PDO also has a bindParam method, which can bind numeric values by reference, that is, it only looks for the corresponding numeric values when the statement is executed.
The only thing left to do now is to execute our statement:
The code copy is as follows:
$statement->execute();
$statement2->execute();
//Get our results:
$cake = $statement->Fetch();
$pie = $statement2->Fetch();
To avoid using only bindValue code fragments, you can use an array to give the execute method as a parameter, like this:
The code copy is as follows:
$statement->execute(array(1 => 'Cake', 2 => true));
$statement2->execute(array(':name' => 'Pie', ':healthy' => false));
0x09: Transactions
A transaction is to execute a set of queries, but does not save their impact on 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 the interdependent fields can be inserted correctly. You need to make sure that the database engine you are using supports transactions.
0x10: Start transaction
You can simply use the beginTransaction() method to start a transaction:
The code copy is as follows:
$db->beginTransaction();
$db->inTransaction(); // true!
Then you can continue to execute your database operation statement and submit the transaction at the end:
The code copy is as follows:
$db->commit();
There is also a rollBack() method similar to MySQLi, but it does not rollback all types (for example, 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 using. These can be entered as the fourth parameter when your object is initialized.
The code copy 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 so that you can use $result->column_name
You can also put the result into a specific class (model), by setting a read mode for each individual query, like this:
The code copy is as follows:
$query = $db->query('SELECT * FROM `foods`');
$foods = $query->fetchAll(PDO::FETCH_CLASS, 'Food');
PDO::ATTR_ERRMODE
We have explained this 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 anxious about loading time, you can use this property to specify a timeout time for your query in seconds. Note that if the time you set exceeds the time, the default E_WARNING exception will be thrown unless PDO::ATTR_ERRMODE is changed.