Menu
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.
812
MySQL SELECT statement is used to retrieve rows from one or more tables. The statement can also include UNION statements and subqueries. SELECT statement is used to fetch rows or records from one or more tables.
Selection symbols to narrow row selection To select rows using selection symbols for character or graphic data, use the LIKE keyword in a WHERE clause, and the underscore and percent sign as selection symbols.
The SELECT query in MySQL offers two options. The first one is to define which tables the command should refer to. You specify the column names after the FROM clause and separate them by commas. The second option is to use the JOIN clause.
Displaying records using MySQLI functions php"; // MySQL connection string $count="SELECT name,id,class,mark,sex FROM student LIMIT 10"; if($stmt = $connection->query($count)){ echo "<table>"; while ($row = $stmt->fetch_assoc()) { echo "<tr><td><a href=details.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
