[PHP/MySQL] monthly archives

[FaderMonkey]

I have a blog and I want to setup archive links for my entries split up by month. My first thought on how to do this would be to use an IF condition with my date timestamp for each entry, but I'm using a DATE_FORMAT function in my query to format the date like this:

PHP Code:

$query ="SELECT entryid, entrytext,";
$query.=" DATE_FORMAT(entrydate, '%M %d, %Y %T') AS date";
$query.=" FROM blog ORDER BY entryid DESC LIMIT 10";
$result=mysql_query($query); 


How ideas on how best to go about setting this up?

[bacon]

Well, there is no "IF" in SQL, just WHERE...

Aren't you just wanting a WHERE clause like:

WHERE entrydate>'somedate' AND entrydate<'someotherdate'

[welshbyte]

Not quite sure what you're trying to do but maybe something like:

PHP Code:

$query ="SELECT entryid, entrytext,";
$query.=" DATE_FORMAT(entrydate, '%M %d, %Y %T') AS date";
$query.=" FROM blog ORDER BY entrydate DESC LIMIT 10";
$query.=" WHERE MONTHNAME(date) = '$required_month'";
$result=mysql_query($query); 


Is that what you were getting at?

[FaderMonkey]

Originally Posted by bacon

Well, there is no "IF" in SQL, just WHERE...

Aren't you just wanting a WHERE clause like:

WHERE entrydate>'somedate' AND entrydate<'someotherdate'

I assumed that because I'm using the DATE_FORMAT fuction that I wouldn't be able to do it that way, but you're right, it works.

Thanks!

[zigx]

u know... what you could try is installing phpmyadmin


then do queries with the script until it gives back the results u want and have it convert the query to php and walah... u have ur querey ready togo