Fetching data using PHP and PDO with bound placeholders

This post shows how to fetch data from a MySQL database using PHP’s PDO library with bound placeholders. This is a fairly basic tutorial and the functions used in it will be used in a post tomorrow titled "Load JSON data with jQuery, PHP and MySQL".

Data used

The example data used in this post is from my fruit table which is defined in my example table for MySQL post. Use the queries in that post to create the example table and sample data.

Connect to the database with PDO

Connecting to a database with PDO is covered in the PHP manual and is done along the lines of this:

$dsn = "mysql:host=localhost;dbname=test";
$username = "XXXXXX";
$password = "YYYYYY";

try {
    $pdo = new PDO($dsn, $username, $password);
}
catch(PDOException $e) {
    die("Could not connect to the databasen");
}

Fetching PDO data with bound placeholders

Bound placeholders take the form of ? or :name and mean you don’t need to deal with escaping data because it’s done by the PDO library. I’ll use the ? placeholder in these examples.

To select all the records from the table with name "apple" do this:

$stmt = $pdo->prepare("SELECT * FROM fruit WHERE name = ?");
$stmt->execute(array("Apple"));

The first line prepares the query to be run and the second actually runs the query; the ? placeholder will be replaced with "Apple". This prepared statement could then be run again using different data without having to prepare it again. For example:

$stmt = $pdo->prepare("SELECT * FROM fruit WHERE name = ?");
$stmt->execute(array("Apple"));
// do something
$stmt->execute(array("Banana"));
// do something else

The data can then be fetched either using $stmt->fetch() and then looping through one row at a time, or all in one hit with the $stmt->fetchAll() function.

Using fetch()

The variable returned from PDO::prepare() is a PDO Statement and ->execute(), ->fetch() and ->fetchAll() are all methods of the PDO Statement class. To fetch each row one at a time in a loop and echo the output using print_r(), do this:

while($row = $stmt->fetch()) {
    print_r($row);
}

The resulting output from the example table would be this:

Array
(
    [fruit_id] => 1
    [0] => 1
    [name] => Apple
    [1] => Apple
    [variety] => Red Delicious
    [2] => Red Delicious
)
Array
(
    [fruit_id] => 6
    [0] => 6
    [name] => Apple
    [1] => Apple
    [variety] => Cox's Orange Pippin
    [2] => Cox's Orange Pippin
)
Array
(
    [fruit_id] => 7
    [0] => 7
    [name] => Apple
    [1] => Apple
    [variety] => Granny Smith
    [2] => Granny Smith
)

Note that the returned array contains both numerically indexed and fieldname indexed data. To return just the numeric indexes pass PDO::FETCH_NUM as a parameter to the ->fetch() function; to return just the fieldname indexed data pass PDO::FETCH_ASSOC.

This next example fetches the associative array only:

while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    print_r($row);
}

The resulting output would be this:

Array
(
    [fruit_id] => 1
    [name] => Apple
    [variety] => Red Delicious
)
Array
(
    [fruit_id] => 6
    [name] => Apple
    [variety] => Cox's Orange Pippin
)
Array
(
    [fruit_id] => 7
    [name] => Apple
    [variety] => Granny Smith
)

Using ->fetchAll()

To fetch all records in one hit into a multi-dimension array use fetchAll(). For example:

$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
print_r($rows);

This would output a single array like so:

Array
(
    [0] => Array
        (
            [fruit_id] => 1
            [name] => Apple
            [variety] => Red Delicious
        )

    [1] => Array
        (
            [fruit_id] => 6
            [name] => Apple
            [variety] => Cox's Orange Pippin
        )

    [2] => Array
        (
            [fruit_id] => 7
            [name] => Apple
            [variety] => Granny Smith
        )

)

Further reading

That’s all for my examples. If you are interested in using the PDO for querying databases with PHP have a good read through the PDO Section of the PHP Manual.