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;
}