PHP convention in retrieving results indexed by a particular column?

Question

I often need to retrieve results and access them by a given column.

Is there a way to write this without walking through the whole dataset each time?

I looked into various PDO fetch modes, but nothing jumped out as being simpler than that. Thanks.

function get_groups() {
  $stmt = $dbc->prepare("SELECT * FROM groups ORDER BY group_name");
  $stmt->execute();

  $groups = $stmt->fetchAll();

  $return = [];

  foreach($groups as $group) {
    $return[$group['id']] = $group;
  }

  return $return;
}

[no, not really](http://stackoverflow.com/questions/5361716/is-there-a-way-to-fetch-associative-array-grouped-by-the-values-of-a-specified-c), unless you have a simple key pair you want to create. — Wrikken, Feb 10 '14 at 20:24
@Wrikken, in your linked question, I honestly do not understand what advises against having db unique id's as array keys.. care to explain a bit more why that would be bad? — Félix Adriyel Gagnon-Grenier, May 05 '14 at 12:02
@FélixGagnon-Grenier: that was not what I was saying. What I was saying was: there is not a really easier method that is not needlessly unclear without much benefit to get this done. I did not say that doing this was not advisable, it's perfectly OK to want this output. In other words: the current code in the question is fine for its purpose. And I linked to an answer that just stated that. Note BTW, on that question, [there seems to be another answer the OP of this question was looking for](http://stackoverflow.com/a/22063422/358679). — Wrikken, May 06 '14 at 17:41

score 2 · Answer 1 · edited May 23 '17 at 12:30

My proposed solution was pretty obsolete. The right solution comes from this answer

$stmt = $pdo->query("SELECT foo,baz FROM bar")
$groupedByFooValuesArray = $stmt->fetchAll(\PDO::FETCH_GROUP|\PDO::FETCH_UNIQUE)

to group it by another column, change the first column you select

if your goal is to have your same array indexed by different column values, I think the only option is to actually index them by different column values.

you can do that by controlling by which field the array is returned

function get_groups($sql,$key,$values='') {
  if ($values != '') {
    $stmt = $dbc->prepare($sql);
    $stmt->execute($values);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
  }
  else {
    $rows = $dbc->query($sql)->fetchAll(PDO::FETCH_ASSOC);
  }
  foreach ($rows as $row) {
    $indexed[$row[$key]] = $row;
  }
  return $indexed;
}

then, use get_groups to build an indexed array:

$sql = 'SELECT * FROM groups ORDER BY group_name'
var_dump(get_groups($sql,'id'));

There might be a way to store a resultset in some way that you can fetchAll() it multiple times, that would be awesome. but I don't know it.

PHP convention in retrieving results indexed by a particular column?

1 Answers1