CODEWITHSUNDEEP
phpmysql
Brett Taylor
Brett Taylor

Reputation: 107

How to select specific rows from MYSQL using PHP?

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.

Upvotes: 5

Views: 98

Answers (3)

Steven Martin
Steven Martin

Reputation: 3272

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

Upvotes: 0

Chuksy
Chuksy

Reputation: 290

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;");

Upvotes: 0

Qirel
Qirel

Reputation: 26450

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

Upvotes: 4

Related Questions