How to group results in the query?


Problem :

I have MySQL query that selects user names and theirs knowledge (may be more than one).

It returns something like this...

array(5) {
  [0]=>
  array(5) {
    ["user_id"]=>
    string(2) "30"
    ["name"]=>
    string(6) "foo1"
    ["knowledge"]=>
    string(15) "Basic Materials"
  }
  [1]=>
  array(5) {
    ["user_id"]=>
    string(2) "33"
    ["name"]=>
    string(6) "foo2"
    ["knowledge"]=>
    string(15) "Basic Materials"
  }
  [2]=>
  array(5) {
    ["user_id"]=>
    string(2) "34"
    ["name"]=>
    string(10) "foo3"
    ["knowledge"]=>
    string(9) "Eating"
  }
  [3]=>
  array(5) {
    ["user_id"]=>
    string(2) "34"
    ["name"]=>
    string(10) "foo3"
    ["knowledge"]=>
    string(9) "Financial"
  }
  [4]=>
  array(5) {
    ["user_id"]=>
    string(2) "34"
    ["name"]=>
    string(10) "foo3"
    ["knowledge"]=>
    string(8) "Services"
  }
}

As you can see, in this example, it does returns five entries. However, three of them have duplicate IDs (and names). I'm looking for a way to return only three entries like this...

Is it possible to do in the query?

array(5) {
  [0]=>
  array(5) {
    ["user_id"]=>
    string(2) "30"
    ["name"]=>
    string(6) "foo1"
    ["knowledges"]=>
    array(1) {
        [0] => string(15) "Basic Materials"
    }
  }
  [1]=>
  array(5) {
    ["user_id"]=>
    string(2) "33"
    ["name"]=>
    string(6) "foo2"
    ["knowledges"]=>
    array(1) {
        [0] => string(15) "Basic Materials"
    }
  }
  [2]=>
  array(5) {
    ["user_id"]=>
    string(2) "34"
    ["name"]=>
    string(10) "foo3"
    ["knowledges"]=>
    array(1) {
        [0] => string(15) "Eating"
        [1] => string(15) "Financial"
        [2] => string(15) "Services"
    }
  }
}

Other option I see is to process results on server-side.

Here is how the query looks like:

SELECT `profiles`.`user_id`, `users`.`name`, `users`.`surname`, `users`.`country`, `profile_knowledges`.`knowledge`
FROM `profiles`
JOIN `users`
ON (`users`.`id` = `profiles`.`user_id`)
JOIN `profile_knowledges`
ON (`profile_knowledges`.`profile_id` = `profiles`.`id`)


Solution :

For this particular case you could write something like this:

$users = array();

while ($row = /* fetch a single row from result set */) {
    if (isset($users[$row['user_id']]) == false) {
        $users[$row['user_id']] = array(
            'id' => $row['user_id'],
            'name' => $row['name'],
            'knowledges' => array()
        );
    }

    $users[$row['user_id']]['knowledges'][] = $row['knowledge'];
}

However it's a better idea to use an ORM such as Doctrine to handle such situations.


Mysql Tutorials

Mysql Howto..

How to get all images which belongs to user in one “loop”

How to display the tables within MySQL? Keeps showing empty set

MySQL Row Ordering: How to set up and use a `position` column for row ordering?

MySQL how to update 325 matches inside table in one go?

How not to use set for ID but need it for constructor of Object

php, mysql, how to compare two strings?

How Many Days have occured during a Given Timespan

How to select many database rows using custom rule?

How to move one fields data from another field within same mysql database table?

How to aggregate/pivot MySQL data over timeline into matrix?

Mysql - how to insert a dot after the second character in each row?

How can I use a JSON encoded array without a URL?

Dreamfactory : How to use MySQL aggregate functions in queries

How to track down problematic MySQL queries?

how to set the variable in a mysql statement into user selection from a dropdown list

how to calculate total number of saturday and sunday between two dates in mysql

How to insert text into mysql having quotes using perl

How to add QBE filters to SQL statement

How can this SQL be improved? (MySQL database)

How can I get a record created time from mysql in to img title section?

MySQL Query, how to group and count in one row?

How to DO MYSQL TRIGGER FOR DELETE Entry Before UPDATE

Showing the progress of a tornado request

How to use “group by” before “join” on mysql?

How to get the total from 2 column in a week

How to order by field if it contains number and text in mysql

How to make MySQL command line works with MAMP?

How to escape “@” on REGEX mysql?

mysql - how to display count of a value in an extra column

How do I convert this from TSQL to MYSQL?