PHP PDO operation Summary _javascript tips

0X01: Test PDO whether the installation was successful

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

Copy Code code as follows:

$test =new PDO ();

0x02: Connecting to the database

To run the Apache server and make sure the server is running and the PDO installation is successful, let's connect to the database below.

Copy Code code 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

The use of query and exec two methods in PDO makes database queries very simple. If you want the row number of the query results exec is very handy, so it is very useful for select query statements.

Copy Code code 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, from which we can get the results we need and how many result sets are queried altogether.

0X04: Get Rows number

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:

Copy Code code as follows:

Echo $foods->rowcount;

0X05: Traversing result sets

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

Copy Code code as follows:

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

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

0X06: Escape special characters entered by user

PDO provides a method called quote, which can be used to escape special characters in quotes in an input string.

Copy Code code as follows:

$input = This is ' s ' ' a ' ' Pretty Dange ' rous str ' ing

After using the quote method transfer:

Copy Code code 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 affected rows after execution:

Copy Code code as follows:

$statement = <<<sql
DELETE from ' Foods '
WHERE ' healthy ' = 1;
SQL;
echo $db->exec ($statement);

0X08: Preprocessing statements

Although the Exec method and query are still heavily used and supported in PHP, the PHP official web site requires that you use the method of preprocessing statements instead. Why, then? 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 actually does not really use preprocessing, it is in the analog preprocessing way, the parameter data is inserted into the statement 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 initializing the PDO Shishun in the following way:

Copy Code code as follows:

$db->setattribute (Pdo::attr_emulate_prepares, false);

Here is our first preprocessing statement:

Copy Code code 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 the preceding code shows, there are two ways to create parameters, named and Anonymous (not in one statement at a time). Then you can use Bindvalue to tap into your input:

Copy Code code 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 parameters, you include a colon (:). PDO also has a Bindparam method that can refer to a binding value, which means it only looks for the corresponding value when the statement executes.

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

Copy Code code as follows:

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

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

To avoid using only code fragments brought by Bindvalue, you can use an array to give the Execute method an argument, like this:

Copy Code code as follows:

$statement->execute (Array (1 => ' Cake ', 2 => true));
$statement 2->execute (Array (': Name ' => ' Pie ', ': Healthy ' => false));

0X09: Business

A transaction is the execution of a set of queries, but does not save their impact to the database. The advantage of doing this is that if you execute 4 interdependent inserts, you can roll back after one fails so that other data cannot be inserted into the database, ensuring that the fields that depend on each other are properly inserted. You need to make sure that you use the database engine to support transactions.

0x10: Open a transaction

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

Copy Code code as follows:

$db->begintransaction ();
$db->intransaction (); true!

You can then proceed to execute your database operation statement and commit the transaction at the end:

Copy Code code as follows:

$db->commit ();

There is also a rollback () method in Mysqli, but it is not a rollback of all types (such as using Drop TABLE in MySQL), and this method is not really reliable, and I recommend that you avoid relying on this method as much as possible.

0X11: Other Useful options

There are several options you can consider using. These can be input as the fourth parameter of your object initialization.

Copy Code code 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, and return an anonymous object so you can 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 code 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 those 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 timeout time for your query in seconds. Note that if more than you set the time, the provincial capital throws e_warning exceptions unless the pdo::attr_errmode is changed.