Filter results in MySQL with PHP

[lukwagomedia]

Hello everyone!
I working on a social network alike page whare I want to implement a messaging system.
I can do all that and I have it working.

What I want is, when I get two or more messages from someone. I only want to display the lates message from that member in the message list.
Right now it shows all three in the list like this.

Message from Willy
Message from Willy
Message from Tom
Message from Willy

But I only want to show one for all like this.

Message from Willy
Message from Tom

Even though Willy has sent many message.
Please help!

[rproffitt]

Without seeing your code this will be a possibly bad guess. I'd review my SQL and look at either the UNIQUE or LIMIT 1 options.

[Dani]

It's pretty impossible to debug what could be wrong with your code without you sharing your code with us.

However, keep the following in mind:

// This will remove duplicate rows
SELECT UNIQUE colA, colB
FROM table

// This will roll up rows with the same value for colA into a single row
// that you can then perform calculations on (e.g. COUNT(*), SUM(), AVG(), etc.)
// Here we are calculating how many of each value for column exist in the table
SELECT col, COUNT(*) AS total_rows
FROM table
GROUP BY col

[lukwagomedia]

Okay, let me provide my code.
The solution I got is to use a DISTINCT action in my SQL code.
And then limit the results to one sender I will try to be a brief as possible.

My code:

$this->db->query("SELECT DISTINCT mail_sentBy_id FROM member_mails ORDER BY mail_id DESC");
$this->sort = $this->resultset();
if(count($this->sort)>0){
    forearch($this->sort as $row){
        $this->db->query("SELECT * FROM member_mails WHERE (mail_sentBy_id=". $row['mail_sentBy_id'." AND mail_sentTo_id=".$my_id.") 
        OR (mail_sentBy_id=". $my_id." AND mail_sentTo_id=".$row['mail_sentBy_id'.") ORDER BY mail_id DESC LIMIT 1");
        $new_res = $this->db->resultset();
        if(count($new_res)>0){
            //The list of email senders goes here
        }
    }
}else{
    echo 'You have no mails yet!';
}

All that code is working very well, but with one problem I cannot solve.
I want the list to be ordered by the newest/highet mail_id "ORDER BY mail_id DESC LIMIT 1".
The list is followed in order at some point, but some results just appear randomly inbetween the list.

I hope I have been so clear and can be understood. English is not my main language, sorry.

[Dani]

The solution I got is to use a DISTINCT action in my SQL code.

OMG! I'm sooo sorry. I got brainwashed after reading rproffitt's post ... it's DISTINCT, not UNIQUE. And I knew that!!

LIMIT 1 just limits the query to a single row. What if you remove the LIMIT 1?

[lukwagomedia]

The problem with removing the LIMIT 1 defeats it's purpose, all messages from the same person appears in the list again.
The only problem I face right now is that the list is not ordered correctly. Either by ASC or by DESC.
Just some of them follow the order but not all, and I cannot figure out what causes this issue.

Thank you once again!

[Dani]

Basically what I am suggesting is just having one query where you don’t need the foreach loop. I’m in bed right now so it’s hard for me to type out from my phone, but try playing around with the group by clause.