Can I use PDO::FETCH_GROUP to group results by two values

Question

I'm using PHP and PDO to retrieve a set of values from a database and then group by the first column, with this code:

$result = $sth->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_NUM);

This means that if my data is in this format:

|Column1    |Column2    |Column3|
|1          |2          |a|
|1          |2          |b|

It returns it as:

1: {
     [Column2: 2, Column3: a],
     [Column2:2, Column3:b]
    }

How would I group by Column1 and Column2 so I get something like:

1: 
   {
    2:
      {
        Column3: a,
        Column3:b
      }
    }

Is that possible to do with a combination of PDO constants?

Thanks

Answer 1

As you saw, it is possible easy to rebuild array easily by grouping it with one specified column with PDO::FETCH_GROUP. The thing you are need is to convert 2-dimension array (result from database) to 3-dimensional array, but it is not possible to rebuild it this way.

You need to do it manually via nested loops. It's pretty simple, like these:

// let's assume $result is:
$result = [
    [1, 2, 'a'],
    [1, 2, 'b']
];

$newResult = [];
foreach( $result as $row ) {
    $newResult[$row[0]][$row[1]][] = $row[2];
}

var_dump($newResult);

It returns:

array(1) {
  [1]=>
  array(1) {
    [2]=>
    array(2) {
      [0]=>
      string(1) "a"
      [1]=>
      string(1) "b"
    }
  }
}

and it look like what you needed.

Answer 2

You better ORDER BY Column1 in your query and group result set in your code like this:

$grouped = [];
$lastIdx = null;
foreach ($query->fetchAll() as $result) {
    if ($lastIdx !== $result["Column1"]) {
        $grouped[$result["Column1"]] = [$result];
    } else {
        $grouped[$result["Column1"]][] = $result;
    }
    $lastIdx = $result["Column1"];
}