I have this message table that allows users to send messages to one another. sender_id is the id of the person who sent the message. receiver_id is the id of the person who received the message. message_id adds the sender_id and receiver_id together. This is a sample below.

           id   sender_id receiver_id message_id message time
           1        2         7          9         hi     -
           2        7         2          9         hello
           3        4         2          6         good
           4        2         4          6         bad

Now, I am trying to group the messages together using message_id by doing this.

Note: $_SESSION['id'] is '2'

$query = "SELECT *  FROM message WHERE sender_id = :id OR receiver_id = :id GROUP BY message_id HAVING COUNT(*) >=1 ORDER BY time DESC";
foreach ($db->query($query, array('id' => $_SESSION['id'])) AS $output)
                $message = $output['message'];

What displays is:

But I want the query to show the latest messages and not the first messages i.e.

Please, how do I do this?

2 Years
Discussion Span
Last Post by Taywin

Time does not have a value in the sample table you showed. I would make sure that you are populating the time field. If you are then we need to see that data also. And are you storing the date as part of the time or in a separate field?
If you are just storing the time and not the date then 10PM yesterday will always be later than 9PM today because the day is not considered.
You can also take a look at using TIMESTAMP since values are stored as the number of seconds since the epoch ('1970-01-01 00:00:00' UTC). by using this date you will always know which record were created last.


The time column uses the TIMESTAMP format but it doesn't still work.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.