MySQL Group rows that have the same values in one column

Question

I have a table of names & data with a category column, what's the best way to generate a query result that will group all rows under the same category?

Category	Name	Other Data
First	Steve	Other Data
First	Alex	Other Data
Second	John	Other Data
Second	Nicole	Other Data
Second	Carrie	Other Data
Third	Michael	Other Data

This is the result array I want:

array[
  'First' => [
    ['Name' => 'Steve', 'Other Date' => 'Other Data'],
    ['Name' => 'Alex', 'Other Date' => 'Other Data'],
  ],
  'Second' => [
    ['Name' => 'John', 'Other Date' => 'Other Data'],
    ['Name' => 'Nicole', 'Other Date' => 'Other Data'],
    ['Name' => 'Carrie', 'Other Date' => 'Other Data'],
  ],
  'Third' => [
    ['Name' => 'Michael', 'Other Date' => 'Other Data'],
  ]
]

Thank you!

Answer 1

You can use PDO and query it with your Category as the first result column, then combining the FETCH_ASSOC with the FETCH_GROUP mode, the later on fetchAll():

$array = $pdo
    ->query(
        "SELECT Category, Name, `Other Data` FROM `Your Table` ORDER BY Category",
        PDO::FETCH_ASSOC
    )
    ->fetchAll(
        PDO::FETCH_GROUP
    );

This gives you the array you have in your question.

var_dump($array);

array(3) {
  ["First"]=>
  array(2) {
    [0]=>
    array(2) {
      ["Name"]=>
      string(5) "Steve"
      ["Other Data"]=>
      string(10) "Other Data"
    }
    [1]=>
    array(2) {
      ["Name"]=>
      string(4) "Alex"
      ["Other Data"]=>
      string(10) "Other Data"
    }
  }
  ["Second"]=>
  array(3) {
    [0]=>
    array(2) {
      ["Name"]=>
      string(4) "John"
      ["Other Data"]=>
      string(10) "Other Data"
    }
    [1]=>
    array(2) {
      ["Name"]=>
      string(6) "Nicole"
      ["Other Data"]=>
      string(10) "Other Data"
    }
    [2]=>
    array(2) {
      ["Name"]=>
      string(6) "Carrie"
      ["Other Data"]=>
      string(10) "Other Data"
    }
  }
  ["Third"]=>
  array(1) {
    [0]=>
    array(2) {
      ["Name"]=>
      string(7) "Michael"
      ["Other Data"]=>
      string(10) "Other Data"
    }
  }
}