mysql query return first match of each result

Question

I have a chat system that works fine, but the message list I've just realised pulls every message per person then displays the last result, which obviously is a massive waste of resources and stress on the server to return only one result per user.

This is the query to retrieve the message list, and afterwards there is a while loop that cleans the array to show only the latest message per user, I know there is a much better way to do this using JOIN but I can't get my head around it.

$result = dbconstruct("SELECT messages.id, messages.msgfrom, messages.msg, 
messages.active, messages.replied, users.username, users.online, users.admin, 
users.imagename FROM messages, users WHERE msgto='$_SESSION[id]' 
AND users.id=msgfrom ORDER by messages.id DESC");

I have looked through other answers, but as stated, finding it quite hard to grasp the join techniques.

update

I don't think I've explained what I need very well. Currently I run the above query, return about 2000 results and then use this to filter each users message to a single message.

$clean = array(); while($result->fetch_assoc()) { if(in_array($result[msgfrom],     
$clean)) { }
else { //print message }

$usercheck = array_push($clean, $result[msgfrom]); }

This displays the message list fine and it shows only the last message from each user, but is a very bad way to do so. There must be a way to do this inside the SQL query.

how php code cleanups this result? – Iłya Bursov Oct 22 '13 at 01:38 — Iłya Bursov, Oct 22 '13 at 01:38
You don't need join but a `LIMIT` or `ORDER` clause or both – hjpotter92 Oct 22 '13 at 01:40 — hjpotter92, Oct 22 '13 at 01:40

Filipe Silva · Accepted Answer · 2013-10-22T10:34:46.250

2

Assuming that message.id is auto_incremented, the MAX is the latest. Otherwise if you have a column as a timestamp you can use that and apply the same principle.

I think the most accurate form of replicating what you are trying in your php is to select only the latest message from someone (subquery) and join it with your desired output query. I think it will return what you expect :

SELECT m.id,
  m.msgfrom,
  m.msg,
  m.active,
  m.replied,
  u.username,
  u.online,
  u.admin,
  u.imagename
FROM messages m 
INNER JOIN users u ON u.users.id = m.msgfrom
INNER JOIN (SELECT MAX(id) as id, msgfrom
            FROM messages
            GROUP BY msgfrom
           ) m2
    ON m2.id = m.id AND m2.msgfrom = m.msgfrom
WHERE messages.msgto = '$_SESSION[id]'

edited Oct 22 '13 at 10:34

answered Oct 22 '13 at 01:46

Filipe Silva

21,189
5
53
68

As I understand using the MAX function, it will only return one value, I need it to return the MAX messages.id result per every messages.msgto does that make sense? – Andrew Ward Oct 22 '13 at 02:31
@AndrewWard. I added a different approach that i think might be closer to what you want. – Filipe Silva Oct 22 '13 at 09:41
@AndrewWard. Did you managed to make it work? If not, you may want to show some sample data to make it easier to help. – Filipe Silva Oct 22 '13 at 23:22
I ended up using a while loop to retrieve only the last message per user message. But your solution would also work and technically speaking would be more efficient then using multiple database queries, so I'll mark as accepted solution. – Andrew Ward Oct 26 '13 at 23:02

score 0 · Answer 2 · edited May 23 '17 at 11:58

0

In the case of using joins, your query should look something like this:

SELECT 
    messages.id, 
    messages.msgfrom, 
    messages.msg,
    messages.active, 
    messages.replied, 
    users.username, 
    users.online, 
    users.admin, 
    users.imagename 
FROM 
    messages
INNER JOIN 
    users 
ON 
    users.id = messages.msgfrom
WHERE 
    msgto='$_SESSION[id]' 
ORDER by 
    messages.id 
DESC

Obviously it hasn't been tested, and you should definitely have a read of this to figure out which join to use.

edited May 23 '17 at 11:58

Community

1
1

answered Oct 22 '13 at 01:45

Darren

13,050
4
41
79

Say there is 1000 rows from user.a that have msg.to = '$_SESSION[id] and then the same for user.b. I want to display the very last message from user.a and user.b and so on.. not have to retrieve all 1000 messages to display the last message from each user... how do I limit each result to one row. – Andrew Ward Oct 22 '13 at 02:38
@AndrewWard you should be able to throw in a `LIMIT 1` to retrieve only the latest one? – Darren Oct 22 '13 at 03:15
Sorry I mustn't be clear enough, there is multiple messages from different users that will have msg.to = $_SESSION[id] I need to return the last message per each user, not just limit to one result. – Andrew Ward Oct 22 '13 at 03:31

mysql query return first match of each result

2 Answers2