How to archive data by month?

[rolyestemonio]

Hi Guys,

I have a problem regarding of archiving. How we are going to archives data by month?
I have 4 fields(id, dates, title,description). I want to archives my data by month in which you can see the Month automatically from database MySQL.

Example

Month retrieved from database

News for November
News for October
News for September

when you click each of this month it will directly open a page archives for each month.

Can anyone help me please. I really confuse how to do it. Thanks for advance guys.

[wilch]

Well, i have solved such a problem differently before.
Method 1
- add 2 extra columns to your database table: month and year (but keep the 'dates' field)
- each time your save a News article in the table, also extract the month and year from the date, and save them in their respective fields.
- Now, when you click on a news article link
eg. <a href="news.php?month=11&year=2008">November 2008 News</a> You will then need to query your database with the 2 conditions
eg $sSQL = "select * from tblNews where month = '$_GET[month]' and year = '$_GET[year]'" Method 2
No new fields are required
- suppose on your news article you have the link <a href="news.php?month=6&year=2007">June 2007 News</a> - after the link is clicked and page redirected, to news.php, then in news.php do this:
-> form 2 dates, one starting from the beginning of the month in the GET variable, and the other ending with the last date in that month
eg.

$startdate = "$_GET[year]-$_GET[month]-1"; // gives date such as 2007-6-1
$enddate = "$_GET[year]-$_GET[month]-".date('t', $_GET['month']); 

//now make a query from the start date to the end date
//eg if you are storing your mysql dates using php time stamps ie integer data type
$iStartdate = strtotime($startdate);
$iEnddate = strtotime($enddate);
$sSQL = "select * from tblNews where dates between $iStartdate and $iEnddate"

It might not be accurate but i am sure you get the idea.

[lili.edryana]

or you can use month() built-in function to get month value from date field.

SELECT * FROM `testdate` WHERE month(date)=$_GET['month']

[rolyestemonio]

Well, i have solved such a problem differently before.
Method 1
- add 2 extra columns to your database table: month and year (but keep the 'dates' field)
- each time your save a News article in the table, also extract the month and year from the date, and save them in their respective fields.
- Now, when you click on a news article link
eg. <a href="news.php?month=11&year=2008">November 2008 News</a> You will then need to query your database with the 2 conditions
eg $sSQL = "select * from tblNews where month = '$_GET[month]' and year = '$_GET[year]'" Method 2
No new fields are required
- suppose on your news article you have the link <a href="news.php?month=6&year=2007">June 2007 News</a> - after the link is clicked and page redirected, to news.php, then in news.php do this:
-> form 2 dates, one starting from the beginning of the month in the GET variable, and the other ending with the last date in that month
eg.

$startdate = "$_GET[year]-$_GET[month]-1"; // gives date such as 2007-6-1
$enddate = "$_GET[year]-$_GET[month]-".date('t', $_GET['month']); 

//now make a query from the start date to the end date
//eg if you are storing your mysql dates using php time stamps ie integer data type
$iStartdate = strtotime($startdate);
$iEnddate = strtotime($enddate);
$sSQL = "select * from tblNews where dates between $iStartdate and $iEnddate"

It might not be accurate but i am sure you get the idea.

Can you please give me a sample code to this one?