我如何从我的mysql查询中分组数据

Question

I am building a basic SMS to web gateway where one person on my web site will be able to chat via SMS with multiple phone users.

My table of messages is setup as such:

id | message | smsFrom | smsTo |time

For simplification some sample data would something like:

smsFrom | smsTo

4 | 5
3 | 5
5 | 4
3 | 5
5 | 3
7 | 5
9 | 5    
8 | 5

Where 5 represent a chat operator and the other numbers represent people talking vis SMS to them.

What is the best way, either via SQL or PHP group my data by operator AND number so that I can then display my conversations in different tabs or however I decide to display them.

My expected output would be something like:

4 | 5
5 | 4

3 | 5
3 | 5
5 | 3

7 | 5

9 | 5

8 | 5

I want to select all of the rows where the to or from fields match my operators number, then group all of those results by the numbers that are not my operators number to create conversations which I can then format as I please.

Answer 1

Don't know how big the table would be so no word on optimization yet. But this may work for you in small DB size:

SQL="SELECT * FROM table;"
// Do what ever you need to get the result as an array "$result"

// First create a sorting array, so grouping would be easy.
$sort=array();
$new=array();
foreach ($result as $r){
    if ($r["smsFrom"] > $r["smsTo"]){
        $sort[$r["smsFrom"]][$r["smsTo"]][]=$r["id"];
    }else{
        $sort[$r["smsTo"]][$r["smsFrom"]][]=$r["id"];
    }
    $new[$r["id"]]=array($r['smsFrom'], $r['smsTo']);
}

// Then based on the grouping, take the id and retrieve the original value.
foreach($sort as $a=>$c){
    foreach($c as $b=>$id){
        echo $new[$id]['smsFrom']." | ".$new[$id]['smsTo'];
    }
}