Monthly Archives using PHP/MySQL?

[Jack Franklin]

In a similar way to WP, I would like to display archives by monthly. Each post has a date in the form yyyy-mm-dd. I figured for each month it would be something like this for Jan:

PHP: Select all

$display_posts = @mysql_query("SELECT contentid, contenttitle, contentintro, content, author, category, date FROM content WHERE date < 2008-02-01 AND date >= 2008-01-01"); 

But is there away of, instead of manually typing that for each month, for it do create each month automatically when it enters the month? So, it would only display the link for January, but on 2008-02-01, display February as well.

Is it possible? It must be, because I've seen it done on WordPress

Thanks,

Jack

[c010depunkk]

Something like this: (untested...)

PHP: Select all

$month=mysql_escape_string($_GET['month']); // or wherever else the month is comin frum
$month=((($month>=12)||($month<1))?1:$month); // check value. and wrap it back to 1 if it's at 12
$month_plus_one=$month++;
$month_plus_one=($month_plus_one>=12?1:$month_plus_one); // check and wrap
$display_posts = @mysql_query("SELECT contentid, contenttitle, contentintro, content, author, category, date FROM content WHERE date < 2008-$month_plus_one-01 AND date >= 2008-$month-01"); 

[Jack Franklin]

Thanks c010!

I'll test it and report any errors I might find.

[Jack Franklin]

That code would work on the actual page archive.php.

But I need code that will list the months in the sidebar that have posts in. Can that be done?

Also - is the Month in your code Number(1-12) or name?

[Jack Franklin]

Code did not work I used this code:

PHP: Select all

<?php
$month=mysql_escape_string($_GET['month']); // or wherever else the month is comin frum
$month=((($month>=12)||($month<1))?1:$month); // check value. and wrap it back to 1 if it's at 12
$month_plus_one=$month++;$month_plus_one=($month_plus_one>=12?1:$month_plus_one); // check and wrap
$display_posts = @mysql_query("SELECT contentid, contenttitle, contentintro, content, author, category, date FROM content WHERE date < 2008-$month_plus_one-01 AND date >= 2008-$month-01"); 
while ($postsm = mysql_fetch_array($display_posts)) {
$contentidm = $postsm['contentid'];
$contenttitlem = $postsm['contenttitle'];
$contentintrom = $postsm['contentintro'];
$contentm = $postsm['content'];
$authorm = $postsm['author'];
$categorym = $postsm['category'];
$datem = $postsm['date'];
//display
echo '<div class="post">';
echo '<h4 class="catpageposttitle"><a href="single.php?contentid=' . $contentidm . '">' . $contenttitlem . '</a></h4>';
echo '<h5 class="catpagepostinfo">#' . $contentidm . ' | by ' . $authorm . ' On: ' . $datem . '</h5>';
echo '<p class="catpagepostintro">' . $contentintrom . '</p></div>';
}
?>

And went to the address archive.php?month=1

Just get a blank page. It's as if it is working, but not using month=1.

[Jack Franklin]

I managed to get them to display on the page using this code:

PHP: Select all

$month = $_GET['month'];
$display_posts = @mysql_query("SELECT contentid, contenttitle, contentintro, content, author, category, date FROM content WHERE MONTH(date)='$month'"); 

Surely I can use if else statements? I'm gonna try.

Phew! Ok, I have a way of doing it, but there must be an easier way, because this is almost ridiculous!

PHP: Select all

$currentmonth = date("m");
if ($currentmonth==12) {
echo '<li><a href="archive.php?month=1">January</a></li>';
echo '<li><a href="archive.php?month=2">February</a></li>';
echo '<li><a href="archive.php?month=3">March</a></li>';
echo '<li><a href="archive.php?month=4">April</a></li>';
echo '<li><a href="archive.php?month=5">May</a></li>';
echo '<li><a href="archive.php?month=6">June</a></li>';
echo '<li><a href="archive.php?month=7">July</a></li>';
echo '<li><a href="archive.php?month=8">August</a></li>';
echo '<li><a href="archive.php?month=9">September</a></li>';
echo '<li><a href="archive.php?month=10">October</a></li>';
echo '<li><a href="archive.php?month=11">November</a></li>';
echo '<li><a href="archive.php?month=12">December</a></li>';
} else {
if ($currentmonth==11) {
echo '<li><a href="archive.php?month=1">January</a></li>';
echo '<li><a href="archive.php?month=2">February</a></li>';
echo '<li><a href="archive.php?month=3">March</a></li>';
echo '<li><a href="archive.php?month=4">April</a></li>';
echo '<li><a href="archive.php?month=5">May</a></li>';
echo '<li><a href="archive.php?month=6">June</a></li>';
echo '<li><a href="archive.php?month=7">July</a></li>';
echo '<li><a href="archive.php?month=8">August</a></li>';
echo '<li><a href="archive.php?month=9">September</a></li>';
echo '<li><a href="archive.php?month=10">October</a></li>';
echo '<li><a href="archive.php?month=11">November</a></li>';
} else {
if ($currentmonth==10) {
echo '<li><a href="archive.php?month=1">January</a></li>';
echo '<li><a href="archive.php?month=2">February</a></li>';
echo '<li><a href="archive.php?month=3">March</a></li>';
echo '<li><a href="archive.php?month=4">April</a></li>';
echo '<li><a href="archive.php?month=5">May</a></li>';
echo '<li><a href="archive.php?month=6">June</a></li>';
echo '<li><a href="archive.php?month=7">July</a></li>';
echo '<li><a href="archive.php?month=8">August</a></li>';
echo '<li><a href="archive.php?month=9">September</a></li>';
echo '<li><a href="archive.php?month=10">October</a></li>';
} else {
if ($currentmonth==9) {
echo '<li><a href="archive.php?month=1">January</a></li>';
echo '<li><a href="archive.php?month=2">February</a></li>';
echo '<li><a href="archive.php?month=3">March</a></li>';
echo '<li><a href="archive.php?month=4">April</a></li>';
echo '<li><a href="archive.php?month=5">May</a></li>';
echo '<li><a href="archive.php?month=6">June</a></li>';
echo '<li><a href="archive.php?month=7">July</a></li>';
echo '<li><a href="archive.php?month=8">August</a></li>';
echo '<li><a href="archive.php?month=9">September</a></li>';
} else {
if ($currentmonth==8) {
echo '<li><a href="archive.php?month=1">January</a></li>';
echo '<li><a href="archive.php?month=2">February</a></li>';
echo '<li><a href="archive.php?month=3">March</a></li>';
echo '<li><a href="archive.php?month=4">April</a></li>';
echo '<li><a href="archive.php?month=5">May</a></li>';
echo '<li><a href="archive.php?month=6">June</a></li>';
echo '<li><a href="archive.php?month=7">July</a></li>';
echo '<li><a href="archive.php?month=8">August</a></li>';
} else {
if ($currentmonth==7) {
echo '<li><a href="archive.php?month=1">January</a></li>';
echo '<li><a href="archive.php?month=2">February</a></li>';
echo '<li><a href="archive.php?month=3">March</a></li>';
echo '<li><a href="archive.php?month=4">April</a></li>';
echo '<li><a href="archive.php?month=5">May</a></li>';
echo '<li><a href="archive.php?month=6">June</a></li>';
echo '<li><a href="archive.php?month=7">July</a></li>';
} else {
if ($currentmonth==6) {
echo '<li><a href="archive.php?month=1">January</a></li>';
echo '<li><a href="archive.php?month=2">February</a></li>';
echo '<li><a href="archive.php?month=3">March</a></li>';
echo '<li><a href="archive.php?month=4">April</a></li>';
echo '<li><a href="archive.php?month=5">May</a></li>';
echo '<li><a href="archive.php?month=6">June</a></li>';
} else {
if ($currentmonth==5) {
echo '<li><a href="archive.php?month=1">January</a></li>';
echo '<li><a href="archive.php?month=2">February</a></li>';
echo '<li><a href="archive.php?month=3">March</a></li>';
echo '<li><a href="archive.php?month=4">April</a></li>';
echo '<li><a href="archive.php?month=5">May</a></li>';
} else {
if ($currentmonth==4) {
echo '<li><a href="archive.php?month=1">January</a></li>';
echo '<li><a href="archive.php?month=2">February</a></li>';
echo '<li><a href="archive.php?month=3">March</a></li>';
echo '<li><a href="archive.php?month=4">April</a></li>';
} else {
if ($currentmonth==3) {
echo '<li><a href="archive.php?month=1">January</a></li>';
echo '<li><a href="archive.php?month=2">February</a></li>';
echo '<li><a href="archive.php?month=3">March</a></li>';
} else {
if ($currentmonth==2) {
echo '<li><a href="archive.php?month=1">January</a></li>';
echo '<li><a href="archive.php?month=2">February</a></li>';
} else {
if ($currentmonth==1) {
echo '<li><a href="archive.php?month=1">January</a></li>';
}
}
}
}
}
}
}
}
}
}
}
} 

It basically checks what the month is and, depending on that, displays x amount of links.

But there is so much code! Surely there is an easier way?

[dab42pat]

try adapting this code.

PHP: Select all

<?php  
$curr_month = date("m");  
$month = array (1=>"January",  "February", "March", "April", "May", "June", "July", "August",  "September", "October",  "November", "December");  
$select = "<select  name=\"month\">\n"; 
foreach  ($month as $key => $val) { 
     $select .= "\t<option  val=\"".$key."\""; 
    if  ($key ==  $curr_month) { 
        $select .=  " selected>".$val."\n"; 
    } else { 
        $select .=  ">".$val."\n"; 
    } 
} 
$select .=  "</select>"; 
echo $select;  
?>

hope it helps

[Jack Franklin]

You mean use that code instead of my tremendously long code?

[dab42pat]

yes

But as i said before im no expert

[Jack Franklin]

Hey, I get the drop down, but nothing happens when I click on them. Would I need to use javascript?