Select last record from same day

Question

Heres the problem... I have a loop looking like this selecting stuff from my database.

$query = "SELECT * FROM table WHERE publish <= CURDATE() AND active = 'Yes' AND (YEAR(begin) = YEAR(CURDATE()) OR YEAR(begin) = YEAR(CURDATE()) + 1) ORDER BY begin ASC";
                        $resultSet = mysql_query($query);

                        if (mysql_num_rows($resultSet))
                        {
                        $newsArray = array();


                        while ($newsResult = mysql_fetch_array($resultSet))
                        {                                   
                        $newDate =  $newsResult['begin'] ;   
                        $timePeriod = date('F  Y ',strtotime($newDate));

                        $timePeriodY = date('Y',strtotime($timePeriod));
                        $timePeriodM = date('F',strtotime($timePeriod));                            

                        if (!isset($newsArray[$timePeriod]))
                        {
                        $newsArray[$timePeriod] = array();
                        }       
                        $newsArray[$timePeriod][] = $newsResult;                            
                        }                        


                        foreach ($newsArray as $timePeriod => $newsItems)
                        {  

                        $timePeriodY = date('Y',strtotime($timePeriod));

                        if ($timePeriodY == $thisYear){


                            }
                        else if ($timePeriodY == $nextYear){


                        }

                        echo '<h2 class="year>'.$timePeriodY.'</h2>';
                        echo '<ul class="column">';

                        foreach ($newsArray as $timePeriod => $newsItems)
                        { 
                        $timePeriodMonth = date('Y',strtotime($timePeriod));
                        if ($timePeriodY == $timePeriodMonth) {


                        $moSe = strftime('%B',strtotime($timePeriod));
                        $MonthSe = ucfirst($moSe);


                        $seMonth = str_replace($dateSearch,$dateReplace,date('F',strtotime($timePeriod)));

                        echo $seMonth;                     

                        foreach ($newsItems as $item)
                        {



                        $ad_event = date("Y-m-d",strtotime($item['event_end']));
                        $today = date("Y-m-d");




                            $dayMod = strftime('%e',strtotime($item['event_begin']));

                            $seDay = str_replace($dateSearch,$dateReplace,date('D',strtotime($item['event_begin'])));




                        echo '<a href="javascript:gotoDotBottom('.$item['event_id'].',\''.$item['event_url'].'\');" id="e'.$item['event_id'].'" class="eventLink '.$markedEvent.'" overhead="'.$item['event_title'].'" overdate="'.date("Y-m-d",strtotime($item['event_begin'])).'" overtext="'. strip_tags($showSum) .'">'.$seDay.' '.$dayMod.' - '.$item['event_title'].'</a>';
                        echo '</div>';
                        }  



                                    }            
                            }

                                                   }


                        }   

                        else
                            {
                        echo '';
                        }

This code outputs the data for each row and separates it for year month day etc. However some of the data is on the same day. Thats fine BUT when data is on the same day i want a variable that holds only the last of the items on that same day.

EX.

DB

PUBLISH      ID    NAME
2011-05-05   1     test 1
2011-05-05   2     test 2
2011-05-06   3     test 3

The result I would like from this is something like this

RESULT

test 1 ID 1 PUBLISH 2011-05-05 VARIABLE 2 <<-- see the variable that comes from test 2 row
test 2 ID 2 PUBLISH 2011-05-05 VARIABLE 2
test 3 ID 3 PUBLISH 2011-05-06 VARIABLE 3

Meaning test 1 in this case holds information about test 2 since test 2 is after test 1 and they both have the same date. If there is only one on the specific date as test 2 and 3 just echo the same id as the variable.

Any ideas? PLease tell me if i need to explain this even more :)

Thanks a lot for any help!

Answer 1

Group by date(begin)as you want only 1 record on the same date(begin) and since you want the latest order by dsc on column 'begin' (I believe the 'begin' is stored as datetime else you need to have dsc on table.ID)

$query = "SELECT * 
               FROM table 
               WHERE publish <= CURDATE() 
                AND active = 'Yes' 
                  AND (YEAR(begin) = YEAR(CURDATE()) 
                  OR YEAR(begin) = YEAR(CURDATE()) + 1) 
                 GROUP BY  begin
                 ORDER BY begin DSC";