populating multidimensional array from timestamp

Question

I have a list of mysql timestamps. I want to produce an html menu, which has got years as first LI and months as nested LI. for example:

2012 -> december, may, april. 2011 -> november, january. 2010 -> april, march, february.

as you see, I want the menu to be an array of years and within months, just the months (and years) which I have in my database.

It's simple for sure, but I can't realize how to perform this. I'm querying all my timestamps and cycling them, but then when I have to fill the array I can't manage.

please describe how these fields are actually stored in your database — Alnitak, Jun 26 '12 at 12:09
they're a field called "date", and are dates: **2012-06-14 10:59:06** php can extract the date use the string as an array, for example the year is `$string[0].$string[1].$string[2].$string[3]` — il_maniscalco, Jun 26 '12 at 12:24

score 2 · Answer 1 · answered Jun 26 '12 at 12:30

2

Have you tried something like this?

<?php
// this would've been pulled from mysql
$data = array(
    array('date' => "2010-11-12 12:34:56", 'some' => 'data'),
    array('date' => "2010-12-12 12:34:56", 'some' => 'data'),
    array('date' => "2010-12-13 12:34:56", 'some' => 'data'),
    array('date' => "2011-01-01 12:34:56", 'some' => 'data'),
);

$grouped = array();

foreach ($data as $row) {
    $year = substr($row['date'], 0, 4);
    $month = substr($row['date'], 6, 2);
    $grouped[$year][$month][] = $row;
}

var_dump($grouped);

answered Jun 26 '12 at 12:30

rodneyrehm

13,442
1
40
56

what's `'some' => 'data' please?` – il_maniscalco Jun 26 '12 at 12:39
that's supposed to represent any other data you might've pulled from the database. You asked to have your records grouped by year/month - which is what the above script does with disregard to any data you may handle besides the date. – rodneyrehm Jun 26 '12 at 12:41
Actually I like your $grouped method better :) – Wivlaro Jun 26 '12 at 12:42
thanks rodney I'll check it out although wivlaro solution seems more elegant to me as it uses a query. – il_maniscalco Jun 26 '12 at 12:45
1

While @Wivlaro's solution would surely perform better (as you're iterating the result set exactly once) - there is nothing "elegant" about it. The code is much less readable and thus less maintainable. (Not saying his solution is bad or wrong - it's not!) – rodneyrehm Jun 26 '12 at 12:49
Rodney I didn't check his php code, as the query is enough, then having my array I can do the php by myself according to my html needs. Sorry for misunderstanding. I was referring just to the sql. – il_maniscalco Jun 26 '12 at 12:52

Wivlaro · Accepted Answer · 2012-06-26T12:50:12.063

The SQL you want is probably something like this:

SELECT YEAR(date) AS year, MONTH(date) AS month, COUNT(*) AS entries FROM my_table
GROUP BY year,month ORDER BY year ASC, month ASC;

And the code would be:

$previous_year = null;
foreach ($result_rows as $row) {
    if ($previous_year == null || $row['year'] != $previous_year) {
        if ($previous_year != null) {
            echo '</ul>/li>';
        }
        echo '<li><span class="year">'.$row['year'].'</span><ul>';
        $previous_year = $row['year'];
    }
    echo '<li>'.$row['month'].' ('.$row['entries'].')</li>';
}
if ($previous_year != null) {
    echo '</ul></li>';
}

Edit:

Alternative PHP based on another answer, this is tidier:

$grouped = array();
foreach ($result_rows as $row) {
    $grouped[$row['year']][$row['month']] = $row;
}

foreach ($grouped as $year => $months) {
    echo "<li>$year";
    echo "<ul>";
    foreach ($months as $month => $row) {
        echo "<li>$month (".$row['entries'].")</li>";
    }
    echo "</ul></li>";
}

you're on the right lines, but you need to put in `
` tags around each inner list. — Alnitak, Jun 26 '12 at 12:40
+1 for a go-in-one solution. you should add some spacing to your operators (like `.`) to make the code a bit easier to read — rodneyrehm, Jun 26 '12 at 12:50

score 1 · Answer 3 · answered Jun 26 '12 at 12:10

1

You must group your queries using GROUP BY , and use loop in PHP which create suitable structure.

answered Jun 26 '12 at 12:10

Piotr Olaszewski

6,017
5
38
65

score 1 · Answer 4 · edited May 23 '17 at 10:34

In slight peusdo code.

$r = query(SELECT * FROM TBL_WHATEVER ORDER BY TS ASC);
$last_year = null;

while($row as mysql_fetch_assoc($r)){
    $year = date('Y', $r['ts']);

   if($year != $last_year){
     echo $year;
     $last_year = $year;
   }else{
       $month = 1;
       while($month < 12){
           echo date('F', $r['ts']);
       }
    }

}

There are many posts about it like this one:

PHP: Loop through all months in a date range?

populating multidimensional array from timestamp

4 Answers4