Title: How to sort (to group) unique data using SQL only, without PHP?
Tags: php;mysql;cakephp
Question: In order to retrieve some unique data from a table, I use the following code:
$query = $this-&gt;Controller-&gt;AnotherController-&gt;find('all', array('fields' =&gt; ['id', 'name', 'email']));
$query-&gt;hydrate(false);
$data = $query-&gt;distinct()-&gt;toArray();
This call gives me data in this format:
(int) 0 =&gt; [
'id' =&gt; '1',
'name' =&gt; 'somename',
'email' =&gt; 'dawnthompson@example.org'
],
(int) 1 =&gt; [
'id' =&gt; '2',
'name' =&gt; 'anothername',
'email' =&gt; 'dawnthompson@example.org'
I want to receive this data in such format:
(string) 'id' =&gt; ['1', '2'],
(string) 'name' =&gt; ['name', 'anothername'],
(string) 'email' =&gt; ['dawnthompson@example.org', 'dawnthompson@example.org']
Is it possible to do that using SQL only? I mean without post-sorting.
Comment: Maybe, but I don't think it's that important because you understood what I meant

Comment: I don't think "sort(ing)" is the correct term here... more like "group(ing)"!?

Comment: I don't think that's possible, there will always be something on PHP level that needs to do post processing in order to receive such results... what is it that makes you want to do that on SQL level?


Here is another answer: Something like group_concat is the way to go here.
https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html
What it basically does, is to concat a field with a separator (default is comma) while grouping results based on a certain field. In your case you can add a pseudo-field with a value, which you can use to group the whole query.
SELECT GROUP_CONCAT(id) as id, GROUP_CONCAT(email) as email, 1 as pseudo_field from yourtable group by pseudo_field;
Now your part: You have to find out, how to implement it nicely into your PHP-framework model, if you need to ;-)

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 3, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2017:00:03/31/17', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<php><mysql><cakephp>', 'Title': 'How to sort (to group) unique data using SQL only, without PHP?', 'ViewCount': 52, 'id': 43149290, 'user_AboutMe': None, 'user_AccountId': 9076348.0, 'user_DisplayName': 'egorik', 'user_Reputation': 759.0, 'user_Views': 56.0, 'user_id': 6757795.0}}