1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

SQL Sort, Group, then sort again?

Discussion in 'PHP' started by medialab, Mar 13, 2018.

0
  1. #1
    Hey Everyone,

    I am trying to create a basic messaging system so users can message each other back in forth, I have a table called "messages" and 7 columns in the table -

    message_id - default, auto_increment
    sender_id - senders ID
    receiver_id - receivers ID
    thread_id - to group conversations together I think?
    opened - 0 or 1 if opened or not
    message - message content
    date_time - DATETIME stamp

    The problem I am having is trying to group the messages then sort them.

    SELECT * FROM messages WHERE sender_id = 4279 OR receiver_id = 4279 GROUP BY thread_id ORDER BY date_time DESC
    Code (SQL):
    Is there an easy way to group them into threads with the newest message in the thread being the result and then also sort them by the date_time?

    I was thinking somehow to use the message_ID as they auto increment and they would be in order?

    Not sure how to do this, any help would be greatly appreciated!
     
    medialab, Mar 13, 2018 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #2
    have a play around with calling the table twice

    select * from messages as M1, messages as M2 where M1.thread_id = M2.thread_id
     
    sarahk, Mar 13, 2018 IP
  3. medialab

    medialab Well-Known Member

    Messages:
    366
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    138
    Digital Goods:
    1
    #3
    I'm not sure what you mean? I am just good enough at PHP and SQL to be dangerous, but this is getting into pro territory lol
     
    medialab, Mar 13, 2018 IP
  4. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #4
    It's often helpful to set up a "fiddle" to show us a sample of your data. I imagine your situation is something like this: http://sqlfiddle.com/#!9/263a7/1/0

    select M1.thread_id, max(M2.message_id)
from messages as M1, messages M2
where M1.thread_id = M2.thread_id
group by M1.thread_id
    Code (markup):
    The message_id acts as a proxy for the date sort since message_id reflects the order that they're added anyway.

    If this isn't right (and it probably isn't) add more data into the insert statement and give us an idea of the kind of data you'd expect to see in the result.
     
    sarahk, Mar 13, 2018 IP
  5. JEET

    JEET Notable Member

    Messages:
    3,825
    Likes Received:
    502
    Best Answers:
    19
    Trophy Points:
    265
    #5
    I think this might work for you:
    haven't checked...

    select
    distinct( thread_id ),
    sender_id, reciever_id, opened, message, date_time
    from
    messages WHERE sender_id = 4279 OR receiver_id = 4279
    ORDER BY date_time DESC
     
    JEET, Mar 29, 2018 IP