PHP PDO operation summary

0x01: test whether the PDO was installed successfully

Run the following code, if the parameter error is prompted to indicate that PDO is installed, if the object does not exist, modify the PHP configuration file php.ini, uncomment php_pdo_yourssqlserverhere.extis.

$test=new PDO();

0x02: connect to database

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

$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 it easy to query the database. Exec is very useful if you want to get the number of rows for the query result, so it is very useful for SELECT queries.

$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 have been queried.

0x04: gets the number of rows

If you use 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:

echo $foods->rowCount;

0x05: traverses the result set

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

foreach($foods->FetchAll() as $food) {

    echo $food['name'] . '

';

}

0x06: escapes the special character entered by the user

PDO provides a method called quote, which escapes the quotes in the input string for special characters.

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

After using the quote method:

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

0 x07: exec ()

PDO can implement UPDATE,DELETE, and INSERT operations using the exec() method, which returns the number of affected rows:

$statement = <<<SQL

    DELETE FROM `foods`

    WHERE `healthy` = 1;

SQL;

echo $db->exec($statement);

0x08: preprocessing statement

For some reason, however, PDO doesn't actually use preprocessing. It simulates preprocessing, inserting parameter data into a statement before passing it to the SQL server, leaving some systems vulnerable to SQL injection.

Here is our first preprocessing statement:

$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 code above, there are two ways to create parameters, named and anonymous (you cannot both appear in a statement). Then you can use bindValue to type in your input:

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

$statement->bindValue(2, true);
$statement2->bindValue(':name', 'Pie');

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

Note that you should include a colon (:) when using named parameters. PDO also has a bindParam method that can bind a value by reference, meaning that it only finds the value when the statement is executed.

Now the only thing left to do is execute our statement:

$statement->execute();

$statement2->execute();

 

//Get our result :

$cake = $statement->Fetch();

$pie  = $statement2->Fetch();

To avoid using only fragments of code from bindValue, you can use the array as an argument to the execute method, like this:

$statement->execute(array(1 => 'Cake', 2 => true));

$statement2->execute(array(':name' => 'Pie', ':healthy' => false));

0 x09: transaction

0x10: opens a transaction

You can simply start a transaction with the beginTransaction() method:

$db->beginTransaction();

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

Then you can continue to execute your database action statement at the end of the commit transaction:

$db->commit();

There is also a rollBack() method like the one in MySQLi, but it does not rollBack all types (for example, using DROP TABLE in MySQL). This method is not really reliable, and I recommend avoiding relying on it.

0x11: other useful options

There are several options you can consider using. These can be used as the fourth parameter input when your object is initialized.

$options = array($option1 => $value1, $option[..]);

$db = new PDO($dsn, $username, $password, $options);

  The 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 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:

$query = $db->query('SELECT * FROM `foods`');

$foods = $query->fetchAll(PDO::FETCH_CLASS, 'Food');

The PDO: : ATTR_ERRMODE

We 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.

The PDO: : ATTR_TIMEOUT

When you are in a hurry about load time, you can use this attribute to specify a timeout in seconds for your query.