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

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.

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'];
    }
}

SELECT smsFrom + smsTo - 5 as partner
    FROM `table`
    WHERE smsFrom = 5 or smsTo = 5
    GROUP BY partner

Using your sample data and sample desired output, it doesn't appear as though you want to necessarily group your data so much as you want to organize it.

However, either way, you can do the following:

Ordering

SELECT IF(smsFrom = 5, smsTo, smsFrom) AS `targetId` FROM sms
WHERE 5 IN (smsFrom, smsTo)
ORDER BY targetId ASC;

Grouping

SELECT IF(smsFrom = 5, smsTo, smsFrom) AS `targetId`, COUNT(*) FROM sms
WHERE 5 IN (smsFrom, smsTo)
GROUP BY targetId;

EDIT: Select the fields you want as necessary for your needs. COUNT(*) included just to demonstrate the grouping

Something like this should work.

SELECT
     smsFrom
    ,smsTo
    ,CONCAT(LEAST(smsFrom),'-',GREATEST(smsTo)) AS `col`
FROM my_table
WHERE smsFrom=5 OR smsTo=5
GROUP BY col
ORDER BY col

Idea is to create new column col which is unique for every pair of smsFrom/ smsTo.