PHPPDO Operation summary _ javascript skills

This article mainly introduces the PHPPDO Operation summary. This article describes how to use PDO to connect to the database, perform basic queries, obtain the number of rows, traverse the result set, escape the special characters entered by the user, exec () pre-processing statement, etc. For more information, see 0x01: test whether the PDO is successfully installed.

Run the following code. If a parameter error is prompted, it means that PDO has been installed. If the object does not exist, modify the PHP configuration file php. ini and cancel the comment above php_pdo_yourssqlserverhere.extis.

The Code is as follows:

$ Test = new PDO ();

0x02: connect to the database

Run the Apache server and confirm that the server is running and the PDO is successfully installed. Next Let's connect to the database.

The Code is as follows:

$ Dsn = 'mysql: dbname = demo; host = localhost; port = 100 ';
$ 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 it very easy to query databases. If you want to obtain the number of rows in the query result exec, it is very useful for SELECT query statements.

The Code is as follows:

$ Statement = < SELECT *
FROM 'Foods'
WHERE 'health' = 0
SQL;

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

If the preceding query is correct, $ foods is now a PDO Statement object. We can obtain the results we need and the total number of result sets we have found from this object.

0x04: Get the number of rows

If the Mysql database is used, 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 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:

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: Escape special characters entered by the user

PDO provides a method called quote, which can escape special characters in the quotation marks in the input string.

The Code is as follows:

$ Input = this is's 'A ''' pretty dange 'rous str ''ing

After the quote method is used for transfer:

The Code is as follows:

$ Db-> quote ($ input): 'This is \'s \ 'a \ '\ 'pretty dange \ 'rous str \ 'in'

0x07: exec ()

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

The Code is as follows:

$ Statement = < Delete from 'Foods'
WHERE 'Healthy '= 1;
SQL;
Echo $ db-> exec ($ statement );

0x08: preprocessing statement

Although exec methods and queries are still widely used and supported in PHP, the official PHP Website still requires that you use preprocessing statements instead. Why? This is mainly because it is safer. The pre-processing statement does not insert parameters directly in the actual query, which avoids many potential SQL injections.

However, for some reason, PDO does not actually use preprocessing. It simulates preprocessing and inserts the parameter data into the statement before passing the statement to the SQL server, this makes some systems vulnerable to SQL injection.

If your SQL server does not really support preprocessing, we can easily solve this problem by passing parameters during PDO initialization as follows:

The Code is as follows:

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

The following is our first preprocessing statement:

The Code is as follows:

$ Statement = $ db-> prepare ('select * FROM foods WHERE 'name' =? AND 'health' =? ');
$ Statement2 = $ db-> prepare ('select * FROM foods WHERE 'name' =: name AND 'healthy '=: healthy )';

As shown in the code above, there are two methods to create parameters: named and anonymous (it cannot appear in a statement at the same time ). Then you can use bindValue to input your input:

The Code is as follows:

$ Statement-> bindValue (1, 'cake ');
$ Statement-> bindValue (2, true );

$ Statement2-> bindValue (': name', 'pie ');
$ Statement2-> bindValue (': healthy', false );

Note that you must include the colon (:) when using the named parameter (:). PDO also has a bindParam method, which can be used to reference a bound value, that is, it only searches for the corresponding value when the statement is executed.

The only thing that remains to be done is to execute our statement:

The Code is as follows:

$ Statement-> execute ();
$ Statement2-> execute ();

// Obtain our results:
$ Cake = $ statement-> Fetch ();
$ Pie = $ statement2-> Fetch ();

To avoid using only the code fragments that bindValue brings, you can use an array to give the execute method as a parameter, like this:

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 group of queries, but does not store them, which affects the database. The advantage of doing so is that if you execute four mutually dependent insert statements, when one fails, you can roll back so that other data cannot be inserted into the database, make sure that the interdependent fields are correctly inserted. Make sure that the database engine you are using supports transactions.

0x10: Start the transaction

You can easily start a transaction using the beginTransaction () method:

The Code is as follows:

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

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

The Code is as follows:

$ Db-> commit ();

The rollBack () method in MySQLi is similar, but it does not roll back all types (for example, using drop table in MySQL). This method is not truly reliable, I recommend that you try to avoid dependency on this method.

0x11: other useful options

There are several options you can consider. These can be used as the fourth parameter input during object initialization.

The Code is as follows:

$ Options = array ($ option1 => $ value1, $ option [...]);
$ Db = new PDO ($ dsn, $ username, $ password, $ options );

PDO: ATTR_DEFAULT_FETCH_MODE

You can select the type of result set that will be returned by PDO, such as PDO: FETCH_ASSOC, which allows you to use $ result ['column _ name'], or PDO: FETCH_OBJ, returns an anonymous object so that you can use $ result-> column_name

You can also put the results into a specific class (model). You can set a read mode for each individual query, just 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 article 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 worried about the loading time, you can use this attribute to specify a timeout value for your query, in seconds. note: If the specified time is exceeded, the E_WARNING exception will be thrown by default, unless the PDO: ATTR_ERRMODE is changed.