PHP PDO method to fetch simple list of matched elements

Question

What is the most simple way to fetch a list of elements using PHP PDO? There are so many fetch and fetch parameters that I'm not sure which is the right one.

Table Users

| id | username | age |
-----------------------
| 5  |  Joey    | 33  |
| 6  | Terry    | 44  |
| 7  | Billy    | 44  |

Query

$q = "SELECT username FROM Users WHERE age = 44";
$db->prepare($q)->execute();
$res = $db->fetch??(PDO::??);
echo json_encode($res);
// Should return, ideally, {"Terry", "Billy"}

If I use fetchColumn I seem to just get one result. If I use fetchAll with assoc_array, well I don't want to iterate over the array if I can avoid it.

try here http://stackoverflow.com/questions/2770630/pdofetchall-vs-pdofetch-in-a-loop — danjam, May 20 '15 at 15:41
I have to admit performance is usually a factor in which method I choose. For resultsets that will be small I use fetchAll and for larger ones I use fetch within a while loop. Aside from that it would be personal preference I think :) — danjam, May 20 '15 at 15:46

score 0 · Answer 1 · answered May 20 '15 at 15:55

0

Assuming $db is an instance of \PDO

$q = "SELECT username FROM Users WHERE age = :age";
$stmt = $db->prepare($q);
$stmt->bindParam(':age', 44, PDO::PARAM_INT);
$stmt->execute();

$objects = array();
while ($object = $stmt->fetch(PDO::FETCH_OBJ)) {
    $objects[] = $object;
}

echo json_encode($objects);

answered May 20 '15 at 15:55

geggleto

2,605
1
15
18

So there's really no simple fetch statement that just returns all the matched columns? I figured that's the most basic thing MySQL is supposed to do. – tdoakiiii May 20 '15 at 16:10
http://php.net/manual/en/pdostatement.fetchall.php You can fetch all in the same way. MySQL has nothing to do with PDO (PDO = PHP Data Objects) – geggleto May 20 '15 at 19:37

score 0 · Answer 2 · answered May 20 '15 at 16:15

0

I ended up using

$res = $stmt->fetchAll(PDO::FETCH_COLUMN);
return implode(",", $res);

answered May 20 '15 at 16:15

tdoakiiii

376
4
13

PHP PDO method to fetch simple list of matched elements

2 Answers2

Linked