Get distinct recent updates

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Weiry
Forum Contributor
Posts: 323
Joined: Wed Sep 09, 2009 5:55 am
Location: Australia

Get distinct recent updates

Post by Weiry »

I currently have a function which returns messages directed to a user that were created within the past 2 days of the current date.
This part of the code works fine, although if a multiple messages are created to a particular user, all the dates within the past 2 days will be displayed.
I am trying to return only the newest distinct result per conversation.
Example:
Todays date: 15/02/2010
To user1: Message 1 - 12/02/2010
From user2: Message 2 - 13/02/2010
To user1: Message 3 - 13/02/2010
To user1: Message 4 - 14/02/2010

In the above example only records within the past 2 days of todays date will show to user1 (Message 3/4)
However, because Message 3 is NOT the newest message, only Message 4 should be shown.

This is the current code:
I have tried a couple of things, but now when i stare at it, i can't seem to think how to accomplish this easily without exploding the amount of code required.

Code: Select all

    public function getRecentUpdate($userID){
        $userID = mysql_real_escape_string($userID);
        $q = "SELECT `user_id` FROM ".TBL_ADMIN." WHERE `user_id` = '{$userID}'";
        if($this->database->query($q)){
            //$q = "SELECT DISTINCT `jobID`,`topic`,`dateTime` FROM ".TBL_JOBS_POSTS." ORDER BY `dateTime` ASC";
            $q = "SELECT * FROM ".TBL_JOBS_POSTS." WHERE `toUser` = '{$userID}' ORDER BY `dateTime` DESC";
            $r = $this->database->query($q);
            if($r){
                $i=0;
                while($row = $this->database->fetchArray($r)){
                    $twoDaysAgo = date( "Y-m-d G:i:s",strtotime ( '-2 day' .date ( "Y-m-d G:i:s" )));
                    if($row['dateTime'] > $twoDaysAgo){
                        $recentArr[] = $row;
                    }
                }
                //print "<pre>";print_r($recentArr);print "</pre>";
                if(empty($recentArr)){
                    print "No Recent Updates.";
                    return false;
                }
                    //print "<pre>";print_r(array_unique($recentArr));print "</pre>";
                foreach($recentArr as $recent){
            
                    $search = array_search($recent['topic'],$recentArr[$i]);
                    if($search){
                        //print " I FOUND ONE ";
                        //print $search."<br/>";
                        $recentArr[$i] = $recent;
                    }
                    $i++;
                }
                return array_reverse($recentArr);
            }else{
                print "No Recent Updates.";
                return false;
            }
        }
        return false;
    }
cheers.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Get distinct recent updates

Post by Christopher »

I think you need to both ORDER BY DESC and LIMIT 1.
(#10850)
User avatar
Weiry
Forum Contributor
Posts: 323
Joined: Wed Sep 09, 2009 5:55 am
Location: Australia

Re: Get distinct recent updates

Post by Weiry »

If i were to use LIMIT 1, wouldn't that only return 1 result regardless on how many unique updates there were?
I am trying to eliminate duplicate jobID's appearing in the list, not limit the list itself.

The example i posted are messages for only 1 job.. however there can be many jobs, say 20 unique jobs each to be displayed with their own unique update. The same job should only appear once.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Get distinct recent updates

Post by Christopher »

Then you probably want to GROUP BY jobID.
(#10850)
User avatar
Weiry
Forum Contributor
Posts: 323
Joined: Wed Sep 09, 2009 5:55 am
Location: Australia

Re: Get distinct recent updates

Post by Weiry »

Ok that seems to have cleared up part of the problem, however it creates a new problem.
This is the updated query:
[sql]SELECT * FROM ".TBL_JOBS_POSTS." WHERE `toUser` = '{$userID}' GROUP BY `jobID` ORDER BY `dateTime` DESC[/sql]

This returns a list of what i need just fine except for one small problem.
GROUP BY will only return the the first entry of the group in order of appearance in the database.

As i explained in the example above:
because Message 3 is NOT the newest message, only Message 4 should be shown.
Currently the query only returns Message 3, not Message 4.

So if i have an entry like this:

Code: Select all

`id`     `jobID`     `toUser`     `fromUser`     `dateTime`          `topic`     `message`
1          4          2               1     '2010-02-15 10:53:27'     'RE: Blah'     'Message 1'
2          4          1               2     '2010-02-15 13:02:32'     'RE: Blah'     'Message 2'
3          4          1               2     '2010-02-15 13:33:33'     'RE: Blah'     'Message 3'
 
The query will return the entry with `id` 2 rather than 3 because it appears first in the list. And changing ASC/DESC only applies after the grouping has been completed.
User avatar
Weiry
Forum Contributor
Posts: 323
Joined: Wed Sep 09, 2009 5:55 am
Location: Australia

Re: Get distinct recent updates

Post by Weiry »

I really don't want to have to bump my own thread here.

Short of creating two separate functions to gather all the data and then somehow grouping the `jobID`'s in an php function, does anyone know how to only GROUP BY in reverse order?
So that only the most recent posts are grouped first rather than the older ones.

As i mentioned previously, adding ORDER BY **** DESC doesn't fix the problem because it only reverses the order after the grouping is completed.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Get distinct recent updates

Post by John Cartwright »

I've come across this issue many times before: a quick search reveals these links.. which led me to this link.
User avatar
Weiry
Forum Contributor
Posts: 323
Joined: Wed Sep 09, 2009 5:55 am
Location: Australia

Re: Get distinct recent updates

Post by Weiry »

i took me a little bit to get it working, partly due to being pretty tired but it works wonders. here is the updated SQL query:

Code: Select all

$q = "SELECT *
      FROM ".TBL_JOBS_POSTS."
      WHERE `id` = (
          SELECT max(`id`) 
          FROM ".TBL_JOBS_POSTS." AS f 
          WHERE f.`jobID` = `jobs_posts`.`jobID`
        )";
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Get distinct recent updates

Post by Eran »

While this will work, it is a correllated sub-query which can have bad performance on MySQL (at least, currently). You might to rewrite this condition as a join, as is shown in the link John posted.
User avatar
Weiry
Forum Contributor
Posts: 323
Joined: Wed Sep 09, 2009 5:55 am
Location: Australia

Re: Get distinct recent updates

Post by Weiry »

While in dev, the correlated sub query will do while the whole app gets finished.
After it is finished i still have to go back over all the code and remove redundant and depreciated methods. This is partly due to using a modified version of classes from another project.
Not to mention i haven't worked with joins yet and would rather learn about it first before i implement it (which is yes, on my to do list)
Post Reply