CODEWITHSUNDEEP
phpmysql
Sir Lojik
Sir Lojik

Reputation: 1429

How to group MySQL results based on field

Hi, I have a table called msgsubjects with a schema shown below. Basically it holds messages for users. The field viewstatus is used to show if a message has been read/not. if its 0, msg is unread and 1 if message has been viewed.

id  int(11)         No      auto_increment                          
    ownerid int(11)         No                                  
    posterid    int(11)         No                                  
    viewstatus  int(11)         No                                  
    bodyid  int(11)         No                                  
    subject varchar(255)    utf8_general_ci     No                                   
    date    int(11)

This is my current sql to pull these results.

$q=sprintf("SELECT * FROM msgsubjects WHERE ownerid=%d ORDER BY date DESC LIMIT %d,%d",$curid,$start,$end);

Want I want to do is have my results returned grouped having unread messages shown first regardless, I want the groups to still be ordered by date. Can this be done with sql? I know I can do this with php but sql would be more efficient.

Upvotes: 0

Views: 103

Answers (2)

Filip Roséen
Filip Roséen

Reputation: 63797

Yes, and it is a very simple solution to the problem.

ORDER BY accepts multiple fields so that you can sort using more than one column.

ORDER BY viewstatus ASC, date DESC

Link to documentation regarding sorting in MySQL:

Upvotes: 1

Luke Stevenson
Luke Stevenson

Reputation: 10341

You are wanting to sort the messages by the viewstatus column

$q=sprintf("SELECT * FROM msgsubjects WHERE ownerid=%d ORDER BY viewstatus ASC, date DESC LIMIT %d,%d",$curid,$start,$end);

Upvotes: 4

Related Questions