PHP PDO specific operations

0X01: Test PDO for installation success

Run the following code, if prompted with a parameter error, the PDO has been installed, if the description object does not exist, modify the PHP configuration file php.ini, Php_pdo_yourssqlserverhere.extis the previous comments.

The code is as follows:
$test =new PDO ();

0x02: Connecting to a Database

Run the Apache server and make sure the server is running and the PDO installation is successful, so let's connect to the database.

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 does connect to the database:
‘ . $E);
}

0X03: Basic Query

Using the query and exec two methods in PDO makes database queries very simple. If you want the number of rows for the query result, exec is very useful, so it is very helpful for select query statements.

The code is as follows:
$statement = <<<sql
SELECT *
From ' Foods '
WHERE ' healthy ' = 0
SQL;

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

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

0X04: Gets the number of rows

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

The code is as follows:
Echo $foods->rowcount;

0X05: Traversing result set

PDO statment can be traversed using the Forech statement, as shown in the following code:

The code is as follows:
foreach ($foods->fetchall () as $food) {
echo $food [' name ']. ‘
‘;
}

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

0X06: Escaping special characters from user input

PDO provides a method called quote, which allows you to escape special characters with quotes in the input string.

The code is as follows:
$input = This is ' s ' a ' pretty Dange ' rous str ' ing

After the transfer using the quote method:

The code is as follows:
$db->quote ($input): ' This is\ ' s\ ' a \ ' \ ' \ ' Pretty dange\ ' rous str\ ' ing '

0x07:exec ()

PDO can implement update,delete and insert operations using the EXEC () method, which returns the number of rows affected after execution:

The code is as follows:
$statement = <<<sql
DELETE from ' Foods '
WHERE ' healthy ' = 1;
SQL;
echo $db->exec ($statement);

0X08: Preprocessing statements

Although the exec methods and queries are still heavily used and supported in PHP, the PHP official web requires that you replace them with pre-processing statements. Why is it? Mainly because: it is more secure. Preprocessing 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 is in the simulated preprocessing method, the parameter data is inserted into the statement before the statement is passed to the SQL Server, which makes some systems vulnerable to SQL injection.

If your SQL Server does not really support preprocessing, we can easily fix this problem by initializing the Shishun in PDO as follows:

The code is as follows:
$db->setattribute (Pdo::attr_emulate_prepares, false);

Here is our first pre-processing statement:

The code is as follows:
$statement = $db->prepare (' SELECT * from Foods WHERE ' name ' =? and ' healthy ' =? ');
$statement 2 = $db->prepare (' SELECT * from Foods WHERE ' name ' =:name and ' healthy ' =:healthy) ';

As shown in the preceding code, there are two ways to create parameters, named and Anonymous (not in one statement at the same time). Then you can use the Bindvalue to tap into your input:

The code is as follows:
$statement->bindvalue (1, ' Cake ');
$statement->bindvalue (2, true);

$statement 2->bindvalue (': Name ', ' Pie ');
$statement 2->bindvalue (': Healthy ', false);

Note that when you use named arguments, you include a colon (:). PDO also has a Bindparam method that can be used to bind a value by reference, that is, it finds the corresponding value only when the statement executes.

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

The code is as follows:
$statement->execute ();
$statement 2->execute ();

Get our results:
$cake = $statement->fetch ();
$pie = $statement 2->fetch ();

To avoid the use of Bindvalue-only code fragmentation, you can use an array to give the Execute method a parameter, like this:

The code is as follows:
$statement->execute (Array (1 = ' Cake ', 2 = true));
$statement 2->execute (Array (': Name ' = ' Pie ', ': healthy ' = ' = False));

0X09: Transaction

A transaction is the execution of a set of queries, but does not save their impact to the database. The advantage of this is that if you execute 4 interdependent INSERT statements, when one fails, you can rollback so that other data cannot be inserted into the database, ensuring that the interdependent fields are inserted correctly. You need to make sure that the database engine you are using supports transactions.

0X10: Open Transaction

You can simply use the BeginTransaction () method to open a transaction:

The code is as follows:
$db->begintransaction ();
$db->intransaction (); true!

Then you can proceed to execute your database operation statement at the last commit transaction:

The code is as follows:
$db->commit ();

There is a rollback () method similar to mysqli, but it does not roll back all types (for example, using Drop TABLE in MySQL), this method is not really reliable, and I recommend that you try 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 is as follows:
$options = Array ($option 1 = $value 1, $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, to return an anonymous object so that you 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:

The code is as follows:
$query = $db->query (' SELECT * from ' foods ');
$foods = $query->fetchall (pdo::fetch_class, ' food ');

Pdo::attr_errmode

We have explained this in the above, but people who like Trycatch need to use: pdo::errmode_exception. If you want to throw PHP warnings for whatever reason, use pdo::errmode_warning.

Pdo::attr_timeout

When you are worried about loading time, you can use this property to specify a time-out for your query in seconds. Note that if you set the time above, the missing capital throws a e_warning exception unless Pdo::attr_errmode is changed.

PHP PDO specific operations