How to use PHP PDO
This is a PHP PDO tutorial. As of version 5.1, PHP has shipped with the PHP Data Objects (PDO) extension. According to the official documentation, PDO is "...a lightweight, consistent interface for accessing databases..." and "...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 is great if you might ever have to switch databases. It can also handle your queries more efficiently and securely than you probably already do.
PDO is very simple to use once you know the syntax. It closely mimics the object oriented methods associated with mysqli that are already built into php. Opening a connection is very simple. You simply create a new PDO object telling it which database adapter to use, which database to select and the username and password.
You can start all queries by calling the prepare method on the PDO object you created.
/* OR */
$query=$cn->prepare('SELECT title FROM pages where id=:id;', array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
/* OR */
$sql = 'UPDATE pages SET title = :title WHERE id = :id;';
$query=$cn->prepare(sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
In the last two queries above, :id and :title are bind parameters. Rather than use mysql_real_escape_string or other similar functions, we can let PDO to the escaping for us. To execute the query simply call the execute method. To tell PHP PDO what to use for those bind parameters you can pass an associative array to the execute method.
/* OR */
$query->execute(array(':id' => $_POST['id'], ':title' => $_POST['title']));
To get the result(s) of the query, simply call the fetch method:
/* OR */
while($row = $query->fetch(PDO::FETCH_ASSOC))
{
echo $row['title'];
}
You can iterate over all the results by putting fetch in a while loop as shown above. Fetch will take a parameter telling it what format to put the returned data in. FETCH_ASSOC returns the data in an associative array. FETCH_NUM uses numerical indexes and FETCH_BOTH allows you to use either. The final thing to do is set the release the dataset once you're done with it. This can be done by setting it to null.
$query=$cn->prepare('SELECT title FROM pages where id<:id;', array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$query->execute(array(':id' => 50));
while($row = $query->fetch(PDO::FETCH_ASSOC))
{
echo $row['title'];
}
$query=null;
