Display blog archives by month

[majocmatt]

It doesn't have to be a blog, but how would I go about making a script that grabs entries out of my mysql database by month, like current blogs do?

 

January 2007

December 2006

November 2006

etc,etc

 

I've searched on mysql's website, but i don't really understand how it works, still. I don't even care if there's nothing in one of those months, I would just like to display the last 3 months or so.

 

Any help?

[tom100]

It depends on how the date is being stored in the MySQL database.

 

I actually prefer my own timestamp sorting method which goes as so:

 

<?php
$now=date("YmdHis"); // Creates a string of the current date and time
?>

 

Store that variable in your mysql database, then if you want to find any entry in the last three months, search for something like this:

 

<?php
$search=date("YmdHis");
$search-=300000000;
?>

 

Now in your SQL where clause, say " WHERE `date` > '{$search}'

[majocmatt]

I store as a SQL DATE format (YYYY-MM-DD) ... so if I call something like this:

 

$month_now = date("m");

SELECT * FROM myTable WHERE date > DATEADD(m, -3, $month_now)

 

How do I get it to recognize each entires Month, and display as I listed above?

[hitman6003]

Look here:

 

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html