The timestamps in my db are stored as integers. The query below retrieves them and formats them the way I'd like, but I can't figure out how to ensure that only unique month / year values are printed. Right now, it'll print:

May 2009
May 2009
June 2009
June 2009
June 2009

When, I only want it to print:

May 2009
June 2009

Here's the sql / code I'm using:

$sql = "select nid, DATE_FORMAT((FROM_UNIXTIME(n.created) + INTERVAL -18000 SECOND), '%M %Y') as printed_value from mytable";
if ($result = db_query($sql,$vars)) {
  while ($arr = db_fetch_array($result)) {
   print '<h2>Archives</h2><br /><a href="/blog/'.$arr['nid'].'">'.$arr['printed_value'].'</a><br />';  

  }
}
    jjfletch wrote:

    The timestamps in my db are stored as integers.

    There's your first problem. Why not store them in a DATETIME column instead so that you can actually manage them as dates instead of numbers?

      This is Drupal... not something I created. And I don't have control over how they decide to keep their structure. This is just something I inherited...

        Try adding a GROUP BY clause to group by the formatted date. I don't think you can use column aliases in GROUP BY clauses, so you'll have to copy the whole DATE_FORMAT() string into the GROUP BY clause (IIRC).

          SELECT DISTINCT nid, ...

          won't work? if not directly you can use it with a subquery, btw.

            Write a Reply...