Selecting the lastest record from a resultset (mysql, php)

Question

I have written this code to get some info from my database.

       "SELECT 
        c.from AS user_id, 
        c.time AS time,
        u.user_firstname AS user_firstname, 
        u.user_lastname AS user_lastname, 
        u.user_profile_picture AS user_profile_picture       
        FROM chat c INNER JOIN users u ON u.user_id = c.from WHERE c.to = :id1 
        UNION SELECT 
        c.to  AS user_id,    
        c.time AS time,       
        u2.user_firstname AS user_firstname, 
        u2.user_lastname AS user_lastname, 
        u2.user_profile_picture AS user_profile_picture
        FROM chat c INNER JOIN users u2 ON u2.user_id = c.to WHERE c.from= :id2 
        ORDER BY time DESC"

it's work great except one thing. Since this is a inbox script there are many message from same user. but i only need to check if there is a message from this user or not.

array (size=28)
0 => 
array (size=5)
  'user_id' => int 6
  'time' => string '2014-05-13 19:53:58' (length=19)
  'user_firstname' => string 'john' (length=4)
  'user_lastname' => string 'doe' (length=3)
  'user_profile_picture' => string '6_user_profile.jpg' (length=19)
1 => 
array (size=5)
  'user_id' => int 2
  'time' => string '2014-05-13 16:59:50' (length=19)
  'user_firstname' => string 'james' (length=5)
  'user_lastname' => string 'bond' (length=4)
  'user_profile_picture' => string '2_user_profile.jpg' (length=19)
2 => 
array (size=5)
  'user_id' => int 6
  'time' => string '2014-05-13 02:15:44' (length=19)
  'user_firstname' => string 'john' (length=4)
  'user_lastname' => string 'doe' (length=3)
  'user_profile_picture' => string '6_user_profile.jpg' (length=19)
3 => 
array (size=5)
  'user_id' => int 6
  'time' => string '2014-05-13 02:13:21' (length=19)
  'user_firstname' => string 'john' (length=4)
  'user_lastname' => string 'doe' (length=3)
  'user_profile_picture' => string '6_user_profile.jpg'(length=19)
4 => 
array (size=5)
  'user_id' => int 2
  'time' => string '2014-05-13 01:58:59' (length=19)
  'user_firstname' => string 'james' (length=5)
  'user_lastname' => string 'bond' (length=4)
  'user_profile_picture' => string '2_user_profile.jpg'(length=19)

as you can see in the var_dump there are 3 john doe and 2 james bond. but I need only the last ones according to the time. so in this case john doe from 19:53:58, and james bond from 16:59:50. Like this:

array (size=2)
0 => 
array (size=5)
  'user_id' => int 6
  'time' => string '2014-05-13 19:53:58' (length=19)
  'user_firstname' => string 'john' (length=4)
  'user_lastname' => string 'doe' (length=3)
  'user_profile_picture' => string '6_user_profile.jpg' (length=19)
1 => 
array (size=5)
  'user_id' => int 2
  'time' => string '2014-05-13 16:59:50' (length=19)
  'user_firstname' => string 'james' (length=5)
  'user_lastname' => string 'bond' (length=4)
  'user_profile_picture' => string '2_user_profile.jpg' (length=19)

and if there are some other users i want to get their last records too. how can i do this? is this possible with only one query?

score 1 · Answer 1 · answered May 14 '14 at 03:39

1

Put the union in a subquery, order it in the main query, and limit that to the most recent row.

SELECT *
FROM (SELECT 
        c.from AS user_id, 
        c.time AS time,
        u.user_firstname AS user_firstname, 
        u.user_lastname AS user_lastname, 
        u.user_profile_picture AS user_profile_picture       
        FROM chat c INNER JOIN users u ON u.user_id = c.from WHERE c.to = :id1 
        UNION
      SELECT 
        c.to  AS user_id,    
        c.time AS time,       
        u2.user_firstname AS user_firstname, 
        u2.user_lastname AS user_lastname, 
        u2.user_profile_picture AS user_profile_picture
        FROM chat c INNER JOIN users u2 ON u2.user_id = c.to WHERE c.from= :id2 
    )
ORDER BY time DESC
LIMIT 1

answered May 14 '14 at 03:39

Barmar

741,623
53
500
612

thank you for your answer but i want to choose john doe from 19:53:58, and james bond from 16:59:50 and if there are other records, their last records too. in your script i only choose the last one which is only john doe from 19:53:58 – suyilmaz May 14 '14 at 03:46
So you want the latest message from each unique user? – Alex.Ritna May 14 '14 at 03:52
@Alex.Ritna yes that's correct. think like that: On facebook you have an inbox. Your friends name are there only one time. Even if you start another conversation with them they wont show up twice. And if you click their name you will see the full conversation. – suyilmaz May 14 '14 at 03:55
Instead of the `LIMIT 1` try `GROUP BY user_id`. This should provide you with the latest on a per user basis. – Alex.Ritna May 14 '14 at 04:01
See http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group for how to select the latest record in each group. – Barmar May 14 '14 at 08:22

score 1 · Accepted Answer · answered May 14 '14 at 04:20

1

try this one I tested.

 "SELECT *
    FROM (SELECT 
    c.from AS user_id, 
    c.time AS time,
    u.user_firstname AS user_firstname, 
    u.user_lastname AS user_lastname, 
    u.user_profile_picture AS user_profile_picture       
    FROM chat c INNER JOIN users u ON u.user_id = c.from WHERE c.to = :id1 
    UNION
  SELECT 
    c.to  AS user_id,    
    c.time AS time,       
    u2.user_firstname AS user_firstname, 
    u2.user_lastname AS user_lastname, 
    u2.user_profile_picture AS user_profile_picture
    FROM chat c INNER JOIN users u2 ON u2.user_id = c.to WHERE c.from= :id2 
) AS bynames
  GROUP BY user_id ORDER BY time ASC"

answered May 14 '14 at 04:20

Gorkem Yontem

398
1
6
11

yes thats it thank you very much – suyilmaz May 14 '14 at 04:21
I don't think this does what you want. It groups by user first, selecting random sets of data for each user. Then it sorts the per-user information by time. – Barmar May 14 '14 at 08:21
@Barmar i think Lennart's answer is the best – Gorkem Yontem May 15 '14 at 03:21

score 0 · Answer 3 · answered May 14 '14 at 03:49

0

Untested

SELECT c.user_id , MAX(c.time) , u.user_firstname , u.user_lastname , u.user_profile_picture FROM ( SELECT from AS user_id , time FROM chat WHERE to = :id1 UNION SELECT to AS user_id , time FROM chat WHERE from = :id2 ) as c JOIN users u ON u.user_id = c.user_id GROUP BY c.user_id , u.user_firstname , u.user_lastname , u.user_profile_picture

answered May 14 '14 at 03:49

Lennart - Slava Ukraini

6,936
1
20
32

@Lenmart thank you for your answer but i reveived many error – suyilmaz May 14 '14 at 03:56
Would you mind sharing the errors with us? – Lennart - Slava Ukraini May 14 '14 at 03:57
@lenmart sure> Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' in C:\wamp\www\Onuruna\httpdocs\includes\message.php on line 82 (which is the last line) – suyilmaz May 14 '14 at 03:59
Can you post create table statements for users and chat? – Lennart - Slava Ukraini May 14 '14 at 04:01
sorry but there is no create table statements. what do you want to learn? – suyilmaz May 14 '14 at 04:03
I need to create the tables in order to investigate the error – Lennart - Slava Ukraini May 14 '14 at 04:06
I tested it on 5.5.37-MariaDB. It complained on from so I renamed that column to from_ and then it worked. MariaDB [test]> SELECT c.user_id , MAX(c.time) , u.user_firstname , u.user_lastname , u.user_profile_picture FROM ( SELECT from_ AS user_id , time FROM chat WHERE to_ = 1 UNION SELECT to_ AS user_id , time FROM chat WHERE from_ = 1 ) as c JOIN users u ON u.user_id = c.user_id GROUP BY c.user_id , u.user_firstname , u.user_lastname , u.user_profile_picture; Empty set (0.01 sec) – Lennart - Slava Ukraini May 14 '14 at 04:08

Selecting the lastest record from a resultset (mysql, php)

3 Answers3