Question 58187 in Mysql Question posted in
The official MySQL documentation can be found here.

Php loop mysql result by year from date field

I’m getting data from the database and want to present the data by year.
Like this:

2013

Event A

Event B

2012

Event X

Event Y
Etc.

I’ve got this mysql query and PHP code which presents all the data in one list:

SQL:

SELECT * FROM table a
INNER JOIN posts b
ON a.event_id = b.ID
WHERE a.user_id = 14
ORDER BY a.event_date DESC

PHP:

$results = $wpdb->get_results($query);

    if ($results) {
        echo '<h3>Title</h3>';
        echo '<ul>';
        foreach ($results as $row) {
            $event_id = $row->event_id;
            $rawDate = $row->event_date;
            $newDate = date("d-m-Y", strtotime($rawDate));

            echo '<li>' . $newDate . ': <a href="' . tribe_get_event_link($row->event_id) . '" target="_blank">' . $row->post_title . '</a> (' . $row->points . ' PE)</li>';
        }
        echo '</ul>';
    }
}

So I like to get this data by year from the field event_date or $newDate which is a date field in Y-m-d H:i:s format, but I can’t get this done. I guess I need an extra foreach, but how?

Thanks in advance!

Tags:

2

Answers


  1. 0

    Addition to query:

    $query = "SELECT *,YEAR(event_date) as title FROM table a INNER JOIN posts b ON a.event_id = b.ID WHERE a.user_id = 14 ORDER BY a.event_date DESC";

    Update to loop:

    $results = $wpdb->get_results($query);
$events=array();
foreach($results as $key=>$value){
   $events[$value['title']][]=$value;
}

    Display:

    if ($events) {
        foreach ($events as $title=>$list) {
           echo '<h3>'.$title.'</h3>';
           echo '<ul>';
           foreach($list as $row){
              //your-existing-code-here...
           }
           echo '</ul>';
        }        
}
    Login or Signup to reply.
  2. 0

    As CBroe commented, save the previous value and compare with the current value inside your loop:

        if ($results) {
        $pre = '';                                      // previous year
        foreach ($results as $row) {
            $current = date("Y", strtotime($row->event_date));
            if ($current != $pre) {
                echo empty($pre) ? '' : '</ul>';       // close list, but not on 1st run
                echo '<h3>'.$current.'</h3>';          // output if year changed
                echo '<ul>';
                $pre = $current;                       // set new previous year
            }


            $event_id = $row->event_id;
            $rawDate = $row->event_date;
            $newDate = date("d-m-Y", strtotime($rawDate));

            echo '<li>' . $newDate . ': <a href="' . tribe_get_event_link($row->event_id) . '" target="_blank">' . $row->post_title . '</a> (' . $row->points . ' PE)</li>';
        }
        echo '</ul>';                                 // final list close
    }
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search