每个循环在PHP中重复两次日期

Question

I've got a for each loop which is echoing results from a mysql query. Then I have a separate query inside the loop to find other records with a date value. I need to compare the dates to print relevant data however inside the loop the date is echoing twice like this:

echo $oneweeksbefore = date("d/m/Y", strtotime('-1 weeks'));

results in:

04/05/2014 04/05/2014

I'm having trouble comparing dates as below because of inaccurate dates.

foreach($data as $row) {
    $student_id = $row['student_id'];
    $sql = "SELECT end_date FROM table WHERE id = :id ORDER BY end_date DESC LIMIT 1";
    $q = $conn->prepare($sql);
    $q->execute(array(':id' => $student_id));
    $table = $q->fetch();
    $end_date = date("d/m/Y", strtotime($table[0]));
    $todaydate = date("d/m/Y");
    if (($end_date > $oneweeksbefore) && ($end_date < $todaydate))
    {
        $show_end_date = '<span class="label label-warning">'.$end_date.'</span>';
    }
    else
    {
        $show_end_date = '<span class="label label-info">'.$end_date.'</span>';
    }
}

The $oneweekbefore variable is outside of the foreach loop. I need to do the query inside of the loop because it needs to take the student_id from the first query for each row. And I can't incorporate it in one query because of more than one rows that needs to be limit to 1 as you can see above.

Answer 1

The repeated queries could be avoided by a rewritten query, that will get the latest end_date for every student_id:

SELECT 
    id, 
    MAX(end_date) as max_end_date 
FROM 
    thetable 
GROUP BY 
    id 
WHERE 
    id IN (<outer query goes here>)