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

Question

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?

How does the `mysql_*` version work at all? You're using `$query` before you are declaring it... — deceze, Jun 06 '12 at 09:46
The "regular" code won't work. You set `$query` after you set `$sql`. — Polynomial, Jun 06 '12 at 09:47
possible duplicate of [How can I properly use a PDO object for a Select query](http://stackoverflow.com/questions/767026/how-can-i-properly-use-a-pdo-object-for-a-select-query) — Polynomial, Jun 06 '12 at 09:48
ah yeah it works and ive tested it, i just mucked it up copying it to use on here, but on my real script its all in order — Bundy, Jun 06 '12 at 09:49
I've edited the title of your question, since it had little to do with the real question, and was therefore misleading. Normally I'd cast a downvote for duplicate questions like this, but I'm refraining from doing so since it's very rare to see someone actually take our advice and use parameterised queries! — Polynomial, Jun 06 '12 at 09:56
Also, keep in mind that you are not using LIKE in the WHERE clause. I would do the following: $stmt = $pdo->prepare('SELECT * FROM auction WHERE name LIKE ?'); $stmt->bindValue(1, '%'. trim($_GET['searchdivebay']) .'%'); The difference between bindParam and bindValue is that bindParam will bind the variable while bindValue binds the actual vaule of the variable. — Vincent, Jun 06 '12 at 09:54

score 106 · Accepted Answer · edited Nov 14 '17 at 10:42

106

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
        )
)

edited Nov 14 '17 at 10:42

Your Common Sense

156,878
40
214
345

answered Jun 06 '12 at 09:49

Polynomial

27,674
12
80
107

19

`\PDO::FETCH_ASSOC` and `\PDO::FETCH_NUM` allow you to define fetching mode. `\PDO::FETCH_ASSOC` will return only `field => value` array, whilst `\PDO::FETCH_NUM` return array with numerical keys only and `\PDO::FETCH_BOTH` will return result like in the answer. This constant should be passed to `->fetchAll()` method in this case. – Paul T. Rawkeen Apr 28 '15 at 13:47
What if I just wanted to output an array of them now? I just want to say let's output array [0] with the values of name. How can that be done? – mKeey Sep 17 '20 at 01:50

score 21 · Answer 2 · edited Oct 14 '21 at 18:48

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.

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

2 Answers2

Linked

Related