samedi 25 avril 2015

MySQL: Select unique field and return all other columns as well + if specific column is not 0 - return that


I can't figure out how to get this done. Basically I am making message system, I have this table: enter image description here

Basically I am trying to print out threads, like every distinct msgfrom is a new thread. Thread should basically say:

[(Sender Name) (Last Message (ORDER BY id DESC)) ([if at least one message has msgread=0 then say You have unread messages])

I've tried this so far:

SELECT id, msgfrom, message, date, 
IF(msgread='0', 'unread', 'read') AS msr 
FROM pmessages WHERE msgto=$userId GROUP BY msgfrom

But that returns:

message from: Username (this is msgfrom 1)
read
message from: Username (this is msgfrom 2)
unread

Even though msgfrom1 has 1 unread message, it says read (I assume it takes first result). So in basic:

SELECT UNIQUE msgrom
ORDER BY id DESC
IF at least ONE msgread = 0
   return * + set msgread=0 (unread)

It's a bit hard to explain, sorry if it's a bit unclear.


Aucun commentaire:

Enregistrer un commentaire