John84 Posted October 20, 2020 Share Posted October 20, 2020 Hi all I have a database table that looks like the attached image This is basically a table of original messages and their replies, the `message_id` is the ID of the original message sent (the master) How can I perform a group by on both the 'id' and 'message_id' and the sort by ID (newest to oldest) So in my example it would return the ID's 7 - Message 4 6 - Message 3 Reply 1 4 - Message 1 - Reply 2 3 -Message 2 Any suggestions? I tried GROUP BY id,message_id but that didnt seem to work Quote Link to comment Share on other sites More sharing options...
Barand Posted October 21, 2020 Share Posted October 21, 2020 With hierarchical data like this, I find it useful to store the records in an array indexed by parent. For example $res = $db->query("SELECT id , message_id as parent , subject FROM message ORDER BY message_id, id DESC "); $msgs = []; foreach ($res as $r) { $msgs[$r['parent']][] = [ 'id'=>$r['id'], 'subject'=>$r['subject'] ]; // store in array by parent } $results = []; foreach ($msgs[0] as $m) { // loop through parent 0 if (!isset($msgs[$m['id']])) { $latest = $m; // no child messages } else { $latest = $msgs[$m['id']][0]; // last child message } $results[$latest['id']] = $latest['subject']; } krsort($results); // sort ids descending echo '<pre>', print_r($results, 1), '</pre>'; // show results Results: Array ( [7] => Message 4 [6] => Message 3 Reply 1 [4] => Message 1 Reply 2 [3] => Message 2 ) Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.