How can I store data from MySQL database into array indexed by id?

Question

I store some data from the MySQL database into an array:

$p = $db->query('SELECT * FROM comments;');
while ($row = $p->fetch(PDO::FETCH_ASSOC)) {
   $commentlist[$row['id']] = $row['text'];
}  


  foreach($commentlist as $key => $value) {
      echo $key;
      echo $value;
  }

Now I have access to the values id and text. But I also need to have access to the value image (which is another row in the MySQL database). But how can I store it if I have only two available elements: key and value?

Answer 1

If you need other fields, then you should store the whole row in $value.

However, PDO is a little more than everyone think. It can give you the desired result in a single call thanks to, fetchAll() method with PDO::FETCH_UNIQUE modifier:

$commentlist = $db->query('SELECT * FROM comments;')->fetchAll(PDO::FETCH_UNIQUE);

Now you can foreach over comments

foreach($commentlist as $id => $value) {
    echo $id;
    echo $value['text'];
    echo $value['image'];
}