How to select specific rows from MYSQL using PHP?

Question

I have a chat app and I'm using PHP to get the data from MYSQL. I don't know how to word this the best way so I'm going to illustrate it the best way I can. Below is the an example the database setup.

DATABASE

ID       MESSAGE       DATETIME 
------------------------------------------
1        Hello1.        2019-04-23 23:04:31
1        Hello2.        2019-03-23 10:04:31
1        Hello3.        2019-04-26 22:04:31
1        Hello4.        2019-04-23 13:01:10
2        Hello5.        2019-04-09 23:04:31
2        Hello6.        2019-04-23 23:04:31
2        Hello7.        2019-04-12 23:04:12
2        Hello8.        2019-04-11 20:04:31
3        Hello9.        2019-05-18 19:04:29
3        Hello10.       2019-02-22 23:04:31
3        Hello11.       2019-03-25 23:04:30
4        Hello12.       2019-04-23 15:04:31
4        Hello13.       2019-04-10 23:04:31
5        Hello14.       2019-01-14 23:04:31

SHOULD SELECT

ID       MESSAGE       DATETIME 
------------------------------------------
1        Hello3.        2019-04-26 22:04:31
2        Hello6.        2019-04-23 23:04:31
3        Hello9.        2019-05-18 19:04:29
4        Hello12.       2019-04-23 15:04:31
5        Hello14.       2019-01-14 23:04:31

What I am trying to do is select the newest ID once for each different ID. So for ID 1 I would only be selecting the one with the newest DATETIME and so on and so forth.

mysql_query("SELECT * FROM messages WHERE ____?____ ORDER BY __?__");

So in this case there should only be 5 results. One of each id with the newest dateline. Any help would be greatly appreciated. Thanks.

All datetimes appear to be identical. What behavior do you expect should two or more records be tied for the latest datetime, for a given id? — Tim Biegeleisen
– Tim Biegeleisen, Commented May 2, 2019 at 5:16
What do you mean by "with the best datetime"? Which one is best? — Qirel
– Qirel, Commented May 2, 2019 at 5:17
There will be no datetimes that are similar with the same id. This is just an example of how the database is setup. — Brett Taylor
– Brett Taylor, Commented May 2, 2019 at 5:18
When I select I want to select each id once. The ids I select will be the ones with the newest date time. — Brett Taylor
– Brett Taylor, Commented May 2, 2019 at 5:19

Qirel · Accepted Answer · 2019-05-02 05:38:47Z

4

You want the MAX(date) value, but one per ID. Because you also want the message, which does not appear in the GROUP BY clause, you should run a join on the table towards itself.

In the joined table, you get the maximum date and the ID, grouped by the ID - this gives you the highest date for each ID. Join that on your table on the ID and date, which allows you to get other columns that do not exist in the GROUP BY (as this GROUP BY is in the joined table, you don't need it in the main table).

SELECT m.id, m.message, m.date
FROM messages m
JOIN (
  SELECT id, MAX(date) AS date
  FROM foo
  GROUP BY id
) AS t
ON m.id=t.id AND m.date=t.date

SQL fiddle shows the live result http://sqlfiddle.com/#!9/c70c3/7

edited May 2, 2019 at 5:38

answered May 2, 2019 at 5:31

Qirel

26.6k7 gold badges54 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chuksy · Accepted Answer · 2019-05-02 05:54:53Z

0

You could try sorting them in a subquery before grouping by the ID as follows:

SELECT * FROM (SELECT * FROM messages ORDER BY DATETIME DESC) AS sortedMessages GROUP BY ID;

OR

mysql_query("SELECT * FROM (SELECT * FROM messages ORDER BY DATETIME DESC) AS sortedMessages GROUP BY ID;");

answered May 2, 2019 at 5:54

Chuksy

2902 silver badges6 bronze badges

Comments

Steven Martin · Accepted Answer · 2019-05-07 14:12:20Z

0

Or you could build a second table with conversations , that could have a subject and the latest message in the row , and then you would just return that full table, when opening the conversation you would pull the indivual messages from the messages table based on the conversationID

answered May 7, 2019 at 14:12

Steven Martin

3,3201 gold badge23 silver badges29 bronze badges

Collectives™ on Stack Overflow

How to select specific rows from MYSQL using PHP?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related