How can I use PDO to fetch a results array in PHP?

I'm just editing my search script after reading up on SQL injection attacks. I'm trying to get the same functionality out of my script using PDO instead of a regular MySQL connection. So I've been reading other posts about PDO, but I am unsure. Will these two scripts give the same functionality?

With PDO:

$pdo = new PDO('mysql:host=$host; dbname=$database;', $user, $pass);
$stmt = $pdo->prepare('SELECT * FROM auction WHERE name = :name');
$stmt->bindParam(':name', $_GET['searchdivebay']);
$stmt->execute(array(':name' => $name);

With regular MySQL:

$dbhost = @mysql_connect($host, $user, $pass) or die('Unable to connect to server');

@mysql_select_db('divebay') or die('Unable to select database');
$search = $_GET['searchdivebay'];
$query = trim($search);

$sql = "SELECT * FROM auction WHERE name LIKE '%" . $query . "%'";

if(!isset($query)){
    echo 'Your search was invalid';
    exit;
} //line 18

$result = mysql_query($trim);
$numrows = mysql_num_rows($result);
mysql_close($dbhost);

I go on with the regular example to use

while($i < $numrows){
    $row = mysql_fetch_array($result);

to create an array of matching results from the database. How do I do this with PDO?

Solution 1:

Take a look at the PDOStatement.fetchAll method. You could also use fetch in an iterator pattern.

Code sample for fetchAll, from the PHP documentation:

<?php
$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();

/* Fetch all of the remaining rows in the result set */
print("Fetch all of the remaining rows in the result set:\n");
$result = $sth->fetchAll(\PDO::FETCH_ASSOC);
print_r($result);

Results:

Array
(
    [0] => Array
        (
            [NAME] => pear
            [COLOUR] => green
        )

    [1] => Array
        (
            [NAME] => watermelon
            [COLOUR] => pink
        )
)

Solution 2:

There are three ways to fetch multiple rows returned by a PDO statement.

The simplest one is just to iterate over the PDO statement itself:

$stmt = $pdo->prepare("SELECT * FROM auction WHERE name LIKE ?")
$stmt->execute(array("%$query%"));
// iterating over a statement
foreach($stmt as $row) {
    echo $row['name'];
}

Another one is to fetch rows using the fetch() method inside a familiar while statement:

$stmt = $pdo->prepare("SELECT * FROM auction WHERE name LIKE ?")
$stmt->execute(array("%$query%"));
// using while
while($row = $stmt->fetch()) {
    echo $row['name'];
}

But for the modern web application we should have our database interactions separated from the output and thus the most convenient method would be to fetch all rows at once using the fetchAll() method:

$stmt = $pdo->prepare("SELECT * FROM auction WHERE name LIKE ?")
$stmt->execute(array("%$query%"));
// fetching rows into array
$data = $stmt->fetchAll();

Or, if you need to preprocess some data first, use the while loop and collect the data into an array manually:

$result = [];
$stmt = $pdo->prepare("SELECT * FROM auction WHERE name LIKE ?")
$stmt->execute(array("%$query%"));
// using while
while($row = $stmt->fetch()) {
    $result[] = [
        'newname' => $row['oldname'],
        // etc
    ];
}

And then output them in a template:

<ul>
<?php foreach($data as $row): ?>
    <li><?=$row['name']?></li>
<?php endforeach ?>
</ul>

Note that PDO supports many sophisticated fetch modes, allowing fetchAll() to return data in many different formats.