Gabriel Gabriel - 1 year ago 64
MySQL Question

Creating agenda view with PHP MySQL - Divide fetched MySQL rows by year and month

Good afternoon everyone.

I've got a MySQL table organized like this:

| ID | Date | Some_Information |
| 1 | 2016-03-02 | A note about this day... |
| 2 | 2016-03-22 | A note about that day... |
| 3 | 2016-04-05 | Another note... |

I need to display this data in a similiar way to Google Calendar app's agenda view. To do this I should:

  1. Divide fetched rows, already ordered by date, by month;

  2. Before displaying data corresponding to each month (let's say march), display a
    with some heading for the corresponding month.

Can someone please tell me if this is possible, and how?

Answer Source

I made some research. I solved this in a simple way.

I used this query to fetch result for the last month, mixing it with an incremental variable, named $offset, and putting it in a for cycle:

$offset = 1;
$sub_offset = $offset - 1;
$query = "SELECT * FROM table WHERE (date >= last_day(now()) + interval 1 day - interval ".$offset." month and date <= last_day(now()) + interval 1 day - interval ".$sub_offset." month) ORDER BY date DESC";

At the beginning of the for cycle, I declare once $offset, echo the month heading, execute the query and display its results via while and mysqli_fetch_assoc(). Then I increment the $offset variable, and the for cycle repeats itself, just until I want it to display months.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download