PHP/MySQL

How do you create an archive list of dates?

[mike73]

Suppose you have a database with a Date column (format: YYYY-MM-DD)that has an entry for many days, spanning several years. Maybe sometimes entire weeks or even entire months are skipped, but there are lots of entries.

*********************
* myDate * myEntry *
*********************
2001-02-22 asdasdas
2001-02-28 ssdsdwww
2001-03-10 dfgdfgdf
...
2005-06-03 sdfsdfdg

What is the most effiecient way (low CPU, fewest queries) to extract each month and year that contains at least one entry? Sort of like the archive list in blogspot.

Thanks in advance for any help,

Mike

[mike73]

Shouldn't this work?

$result = mysql_query("SELECT DISTINCT YEAR(dDate) AS 'nYear', MONTH(dDate) AS 'nMonth', FROM " . $db . " WHERE dDate < DATE() GROUP BY YEAR(dDate), MONTH(dDate) ORDER BY dDate DESC");

while(list($nYear,$nMonth)=mysql_fetch_row($result)){
echo $nYear . $nMonth ."<br>";
}

[mike73]

Final answer:

$result = mysql_query("SELECT DISTINCT YEAR(dDate) AS 'nYear', MONTH(dDate) AS 'nMonth' FROM " . $db . " GROUP BY YEAR(dDate), MONTH(dDate) ORDER BY dDate DESC")
or RedirectTo("msg.php?msg=1");
while(list($nYear,$nMonth)=mysql_fetch_row($result)){
echo "<a href='diary.php?year=" . $nYear . "&month=" . $nMonth . "&id=" . $ID . "'>" . strftime("%B %Y",strtotime($nYear . "-" . $nMonth . "-01")) . "</a><br />";
}

Thanks for the help, nobody ;-)

[coopster]

Glad you got it sorted ;-)

You really only need either the DISTINCT keyword here or the GROUP BY clause though, but not both. I tried to find you some form of reference, here is the best I could find:

[dev.mysql.com...]

[mike73]

Thanks! I removed the GROUP BY and it still works so the same, so I assume it's also faster now too.