PDO provides a data-access abstraction layer, which means that, regardless of which database you're using, you use the same functions to issue queries and fetch data. PDO does not provide a database abstraction; it doesn't rewrite SQL or emulate missing features. You should use a full-blown abstraction layer if you need that facility.

^{_{Source — https://php.net/manual/en/intro.pdo.php}}

Connection

PDO uses a DSN to define the connection to the database. It also has a number of connection options which can help you to fine-tune your PDO instance. Some of these options are worth setting by default. Here is an example:

$dsn = "mysql:host=localhost;dbname=test;charset=utf8";
$opt = array(
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
);
$pdo = new PDO($dsn,'root','', $opt);

Let's take a closer look at this code:

• $dsn contains the database driver (mysql), host (localhost), database name (test), and character set (utf8). Of course, these parameters can be replaced with variables as well.
• After $dsn comes the username and password.
• The $opt parameter is an array contains configuration options.

It is recommended to set ERRMODE_EXCEPTION as it will let PDO throw exceptions on errors; this mode is the most reliable way to handle PDO errors.
Setting ATTR_DEFAULT_FETCH_MODE is also a good idea. It saves you having to include it with every fetch, making your application code less bloated.

There are many bad examples around telling you to wrap every PDO statement into try..catch - so, I have to make a distinct note:

DO NOT use the try..catch operator just to handle an error message. Uncaught exceptions are already excellent for this purpose, as they will treat PDO errors in just the same way as other PHP errors - so, you can define the behavior using site-wide settings.
A custom exception handler could be added later, but it is not required. For new users especially, it is recommended to use unhandled exceptions, as they are extremely informative, helpful and secure.
More info...

Prepared statements

Prepared statements are one of the main reasons for using PDO.
The way how it works is explained here: How can prepared statements protect from SQL injection attacks? So, here follows the rules of using PDO:

• Every dynamic data literal has to be represented in a query by either name (:name) or regular placeholder (?).
• Every query has to be run in 3 (or 4) steps:
  • prepare() - will prepare the query and create a statement object.
  • bindValue() / bindParam() - this is an optional step as variables can be passed directly into execute().
  • execute() - will actually run the query.
  • fetch* - will return the query result in a usable form.

Some rules of thumb:

• Use named placeholders only if you need a complex query or if you already have an associative array which keys are equal to table field names. Otherwise, regular placeholders are simpler to use.
• Use "lazy" binding when possible - passing data into execute will dramatically shorten your code.
• If you don't know if you need bindValue() or bindParam(), go for the former. bindValue() is less ambiguous and has fewer side effects.

So, here is an example:

$id  = 1;
$stm = $pdo->prepare("SELECT name FROM table WHERE id=?");
$stm->execute(array($id));
$name = $stm->fetchColumn();

Getting results

PDO has some extremely handy methods to return the query result in different formats:

• fetch() - a general purpose fetch method similar to mysql_fetch_array().
• fetchAll() to get all the rows without while loop.
• fetchColumn() to get a single scalar value without getting an array first.

fetchAll() is a very handy function when you make yourself familiar with separating business logic from presentation logic. It lets you get data first and then use it to display:

$stm = $pdo->prepare("SELECT id,name FROM news WHERE dt=curdate()");
$stm->execute();
$data = $stm->fetchAll();

Now we have all the news in the $data array and we can move to presentation part:

?>
<table>
<? foreach ($data as $row): ?>
  <tr>
    <td>
      <a href="news.php?<?=$row['id']?>">
        <?=htmlspecialchars($row['name'])?>
      </a>
    </td>
  </tr>
<? endforeach ?>

Complex cases

Although prepared statements are good things in general, there are some good tips, tricks and pitfalls to know about. First of all, one have to understand that placeholders cannot represent an arbitrary part of the query, but a complete data literal only. Neither part of literal, nor whatever complex expression or a syntax keyword can be substituted with prepared statement.

Here are some typical cases:

PDO Prepared statements and LIKE

Prepare the full literal first:

$name = "%$name%";
$stm  = $pdo->prepare("SELECT * FROM table WHERE name LIKE ?");
$stm->execute(array($name));
$data = $stm->fetchAll();

PDO Prepared statements and LIMIT

When in emulation mode (which is on by default), PDO substitutes placeholders with actual data. And with "lazy" binding (using array in execute()), PDO treats every parameter as a string. As a result, the prepared LIMIT ?,? query becomes LIMIT '10', '10' which is invalid syntax that causes the query to fail.

There are two solutions:

• Turn emulation off (as MySQL can sort all placeholders out properly).
• Bind the number explicitly and setting proper type (PDO::PARAM_INT) for this variable.

To turn emulation off, one can run this code (or set in a connection options array):

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

Or to bind these variables explicitly with param type:

$stm = $pdo->prepare('SELECT * FROM table LIMIT ?, ?');
$stm->bindParam(1, $limit_from,PDO::PARAM_INT);
$stm->bindParam(2, $per_page,PDO::PARAM_INT);
$stm->execute();
$data = $stm->fetchAll();

PDO Prepared statements and IN

It is impossible to substitute an arbitrary query part using PDO prepared statements. For such cases as the IN() operator, one must create a set of ?s manually and put them into the query:

$arr = array(1,2,3);
$in  = str_repeat('?,', count($arr) - 1) . '?';
$sql = "SELECT * FROM table WHERE column IN ($in)";
$stm = $db->prepare($sql);
$stm->execute($arr);
$data = $stm->fetchAll();

PDO Prepared statements and identifiers.

PDO has no placeholder for identifiers such as database or table names, so a developer must manually format them. To properly format an identifier, follow these two rules:

• Enclose identifier in backticks.
• Escape backticks inside by doubling them.

The code would be:

$table = "`".str_replace("`","``",$table)."`";

After such formatting, it is safe to insert the $table variable into query.

It is also important to always check dynamic identifiers against a list of allowed values. Here is a brief example (from How can I prevent SQL injection in PHP?):

$orders  = array("name","price","qty"); //field names
$key     = array_search($_GET['sort'],$orders); // see if we have such a name
$orderby = $orders[$key]; //if not, first one will be set automatically. smart enuf :)
$query   = "SELECT * FROM `table` ORDER BY $orderby"; //value is safe

another example could be found below:

PDO Prepared statements and INSERT/UPDATE query

(from Insert/update helper function using PDO)
A usual PDO-prepared INSERT query statement consists of 2-5 kilobytes of repeated code, with every field name being repeated six to ten times. Instead, we need a compact helper function to handle a variable number of inserted fields. Of course with face control for these fields, to allow only approved fields into query.

The following code is based on the assumption that HTML form field names are equal to SQL table field names. It is also using the unique MySQL feature of allowing SET statements both for INSERT and UPDATE queries:

function pdoSet($fields, &$values, $source = array()) {
  $set = '';
  $values = array();
  if (!$source) $source = &$_POST;
  foreach ($fields as $field) {
    if (isset($source[$field])) {
      $set.="`".str_replace("`","``",$field)."`". "=:$field, ";
      $values[$field] = $source[$field];
    }
  }
  return substr($set, 0, -2); 
}

This function will produce a correct sequence for the SET operator,

`field1`=:field1,`field2`=:field2

to be inserted into query and $values array for execute().
Can be used this way:

$allowed = array("name","surname","email"); // allowed fields
$sql = "INSERT INTO users SET ".pdoSet($allowed,$values);
$stm = $dbh->prepare($sql);
$stm->execute($values);

Or, for a more complex case:

$allowed = array("name","surname","email","password"); // allowed fields
$_POST['password'] = MD5($_POST['login'].$_POST['password']);
$sql = "UPDATE users SET ".pdoSet($allowed,$values)." WHERE id = :id";
$stm = $dbh->prepare($sql);
$values["id"] = $_POST['id'];
$stm->execute($values);