PHP PDO FetchAll vs Fetch

Question

I believe I am using the PDO fetch functions completely wrong. Here is what I am trying to do:

Query a row, get the results, use a helper function to process the results into an array.

Query

function userName($db){
  $q = $db->prepare("SELECT id, name FROM users WHERE id = :user");
  $q->bindParam(":user", $user);
  $q->execute();
  $qr = $q->fetchAll(PDO::FETCH_ASSOC);
  if ($qr->rowCount() > 0){
    foreach($qr as $row){
      $names[$row['id']] = buildArray($row);
    }
  return $names;
  }
}

My custom array building function

function buildArray($row){
 $usernames = array();
 if(isset($row['id'])) $usernames['id'] = $row['id'];
 if(isset($row['name'])) $usernames['name'] = $row['name'];
}

I'm actually getting exactly what I want from this, but when I echo inbetween I see that things are looping 3 times instead of once. I think I am misusing fetchAll.

Any help appreciated

Answer 1

If you're going to build a new array, there's not much point in having fetchAll() build an array. Write your own fetch() loop:

function userName($db){
    $q = $db->prepare("SELECT id, name FROM users WHERE id = :user");
    $q->bindParam(":user", $user);
    $q->execute();
    $qr = $q->fetchAll(PDO::FETCH_ASSOC);
    $names = array();
    while ($row = $q->fetch(PDO::FETCH_ASSOC)) {
        $names[$row['id']] = $row;
    }
    return $names;
}

There's also no need for buildArray(), since $row is already the associative array you want.