There is a table of events with dates in the TIMESTAMP format (for example, 2016-02-10 19:17:01) .
Is it possible to get a sample of values ​​grouped by month of creation? Those. at the output, get an array with nested arrays, in which there will be events related to a specific month of a particular year.
I need to implement an archive of events with the conclusion of the month and year of creation (the last 6 months of data publication). Here I broke my head how to do it correctly, avoiding heaps of cycles with conditions and iterations of a huge number of base values.

  • 2
    You can give the group by extract(year_month from date_field) and you can do the nested arrays with php - Mike

1 answer 1

avoiding enumeration of a huge number of base values.

to avoid going through a huge number of values, you need to request from the database only the data you want to show. In this case, it is necessary to limit the sample to the last six months. This is done using SQL.

avoiding heaps of cycles with conditions

It should be understood that the cycle can not be avoided in any case. It is impossible to get data from a database into an array without a loop.

However, no “heaps” of loops are needed here - only one loop is needed, which reads data from the database and shoves them through arrays. One cycle with one condition - nothing complicated.

how to do it correctly

It will be competent to use PDO and one of its magic constants, namely PDO :: FETCH_GROUP , for which the first column will need to select a value containing a specific month of a particular year. In this case, it is not necessary to write the loop yourself, the PDO will execute it itself. For mysql, the code will be something like this:

 $sql = "SELECT DATE_FORMAT(date, '%Y-%m'), * FROM t WHERE date > DATE_SUB(NOW(), INTERVAL 6 MONTH) ORDER BY date"; $data = $pdo->query($sql)->fetchAll(PDO::FETCH_GROUP);

And PDO will group the data obtained by the first field, returning the same "array with nested arrays, in which there will be events related to a particular month of a particular year," which was ordered. The keys of the array are the values ​​of the form 2016-03 , and the values ​​are the arrays with all entries for the specified month.