Member Avatar for 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.

  • 3 Contributors
  • 3 Replies
  • 454 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by rolyestemonio

Dani AI

Generated

Good progress so far from and — two common patterns are (A) precompute/store month+year for faster lookup, and (B) compute on the fly from your dates column. Trade-offs: storing month/year makes queries cheap but duplicates data and needs keeping in sync; using functions like MONTH()/YEAR() is simple but prevents use of an index and will be slow on large tables and will match across all years unless you also filter by year.

To build the archive list (months with counts) from the DB:

SELECT
  YEAR(dates) AS yr,
  MONTH(dates) AS mo,
  DATE_FORMAT(dates, '%M %Y') AS label,
  COUNT(*) AS cnt
FROM tblNews
GROUP BY yr, mo
ORDER BY yr DESC, mo DESC;

To show posts for a clicked month, prefer a safe date-range query (this lets MySQL use an index on dates). Example PHP (PDO) using DateTime to compute start/end and prepared statements:

$year = (int)$_GET['year'];
$month = (int)$_GET['month'];

$start = (new DateTime("$year-$month-01"))->setTime(0,0,0)->format('Y-m-d H:i:s');
$end = (new DateTime("$year-$month-01"))->modify('first day of next month')->modify('-1 second')->format('Y-m-d H:i:s');

$stmt = $pdo->prepare("SELECT id, title, description, dates FROM tblNews WHERE dates BETWEEN :start AND :end ORDER BY dates DESC");
$stmt->execute([':start' => $start, ':end' => $end]);
$posts = $stmt->fetchAll(PDO::FETCH_ASSOC);

Tips and gotchas:

  • Validate and cast $_GET values to integers to avoid injection and bad input.
  • If you store UNIX timestamps as INT, compute start/end as timestamps (use ->getTimestamp()) and query against that int column.
  • For large tables avoid WHERE MONTH(dates)=x; use range queries or add indexed columns (or generated/virtual columns) if monthly grouping is frequent.
  • Mind timezones: ensure PHP and DB interpret dates the same way.

This approach gives correct month boundaries, safe queries, and good performance as your archive grows.

Recommended Answers

All 3 Replies

Member Avatar for 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.

Edited by wilch because: n/a
Member Avatar for 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']
Member Avatar for 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?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.