php/mysql sort by month and year

[jarvis]

Hi All,

 

I'm in the process of building an article CMS and I'm after some advice please.

 

At present, all articles are stored in the mysql db and have a date_entered column (type=timestamp). I then use PHP to reference the DB.

 

What I'd like to do is have a page which sorts articles by month with the most relevant month first, then the other months listed underneath, so it would be August at present, any articles from Aug listed or message 'no articles this month' under this would be July, June, May etc.. and then an option to view previous years which would be in month order. So 2008, 2007, selecting that would then be Dec - Jan

 

Where's the best place to start with this? Sorry if it's a daft question!

 

Many thanks!

[JonnoTheDev]

If the article date is a unix timestamp then look at the FROM_UNIXTIME() function in mysql. You may also want to look at the MONTH() and YEAR() functions. This should help you get the articles for a given month / year.

[jarvis]

Thanks for the reply. I've been playin around and have nearly got it working how I need it too. The current months shows and any articles from that month are listed.

The previous months for that year are then shown, i.e. it shows August, then July -> January

I'm stuck on getting the same to work for years. Below's my code if someone can assist?

<?php 

// This page displays all articles within a specified category

$page_title = 'View Featured News Articles';
require('includes/header.html');

require_once('../mysql_connect.php'); // Connect to the db

// Set the category id
$id="18";

// Set the sorting order by months
if (isset($_GET['month'])) {
// $month will be appended to the links
$order_by = $_GET['month'];
} else { // Set a default sorting month to current month
$order_by= date('F'); 
}

// Set the sorting order by years
if (isset($_GET['year'])) {
// $year will be appended to the pagination links
$year = $_GET['year'];
} else { // Set a default sorting year to current year
$year= date('Y'); 
}

$query = "
SELECT articles.article_id, articles.title, LEFT(articles.description, 50) AS abbrev, articles.status, article_associations.article_id, article_categories.category, DATE_FORMAT(articles.date_entered,'%M') as month,DATE_FORMAT(articles.date_entered,'%Y') as year, DATE_FORMAT(articles.date_entered,'%d %M %Y') as date 
FROM articles 
INNER JOIN (article_categories 
		INNER JOIN article_associations ON article_categories.article_category_id = article_associations.article_category_id) ON articles.article_id = article_associations.article_id 
WHERE articles.article_id = article_associations.article_id 
AND article_associations.article_category_id=$id 
AND article_associations.approved = 'Y' 
AND status='1' 
AND DATE_FORMAT(articles.date_entered,'%M') = '$order_by' 
AND DATE_FORMAT(articles.date_entered,'%Y') = '$year' 
ORDER BY date_entered DESC";

$result = @mysql_query ($query); // Run the query.
$num = mysql_num_rows ($result); // How many users are there?

if ($num > 0) { // If it ran OK, display the records.

echo "<h1>There is currently $num new articles for the month of $order_by</h1>";
echo '<table cellpadding="3" cellspacing="3" border="0" align="center" width="100%">';
echo '<tr>
<td><p><b>Title:</b></p></td>
<td><p><b>Description:</b></p></td>
<td> </td>
<td> </td>
</tr>';
// Fetch and print all the records.
$bg = '#CCCCCC'; // Set the background color.

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
	$category = '' .$row['5']. '';
	$bg = ($bg=='#CCCCCC' ? '#FFFFFF' : '#CCCCCC'); // Switch the background color.

		echo '<tr bgcolor="' . $bg . '">
			<td>' .$row['1']. '</td>
		  	<td>';
			   $extract = $row['2'];
			   // find position of last space in extract
			   $lastSpace = strrpos($extract, ' ');
			   // use $lastSpace to set length of new extract and add ...
			   echo substr($extract, 0, $lastSpace).'... ';		
		echo '</td>';
		echo '<td>'.$row['7'].'</td>';
		echo "<td><a href=\"articles.php?aid={$row['0']}\">Read More...</a></td>";
		echo '</tr>';	
}
echo '</table>';

echo '<h1 style="text-align: right; color: #AE0026;">'.$category.'</h1>';
echo '<p> </p>';


} else { // Not records in related to that category ID.
echo '<p class="error">There are currently no news articles!</p>';
}

echo '<p>Sort By:</p>';

$year = date("Y");	//get the current year
$startDate = "1 january".$year;	// set the end date to current year

function printMonths($var)
{
$id="18";	//include the category id for the links	
$start = strtotime($var);	//timestamp of the entered date
$now = strtotime("Now");	//timestamp of now so it does not write anything in the future	
	while ($now > $start)	//while the start is less than now
	{
		echo '<a href="news_archive.php?s=&id=' . $id . '&month=' . date("F", $now) .'">'.date("F", $now).'</a>';
		echo " | ";
		$now = strtotime("-1 month", $now);	// subtract a month from the start timestamp
	}			
}	
printMonths($startDate);	//execute the function


### YEARS - TEST ###
echo '<hr>';
$startYear = "2005";	//get the current year
echo $startYear;

function printYears($var)
{
$id="18";	//include the category id for the links	
$start = strtotime($var);	//timestamp of the entered date
$now = strtotime("Now");	//timestamp of now so it does not write anything in the future	
	while ($now > $start)	//while the start is less than now
	{
		echo '<a href="news_archive.php?s=&id=' . $id . '&year=' . date("Y", $now) .'">'.date("Y", $now).'</a>';
		echo " | ";
		echo date("Y", $now);
		$now = strtotime("-1 year", $now);	// subtract a month from the start timestamp
	}			
}	
printYears($startYear);	//execute the function	

mysql_close(); // Close the database connection.

require('includes/footer.html');
?>

Thanks in advanced!

[jarvis]

EDIT - APOLOGIES - wrong code!

<?php 

// This page displays all articles within a specified category

$page_title = 'View Featured News Articles';
require('includes/header.html');

require_once('../mysql_connect.php'); // Connect to the db

// Set the category id
$id="18";

// Set the sorting order by months
if (isset($_GET['month'])) {
// $month will be appended to the links
$order_by = $_GET['month'];
} else { // Set a default sorting month to current month
$order_by= date('F'); 
}

// Set the sorting order by years
if (isset($_GET['year'])) {
// $year will be appended to the pagination links
$year = $_GET['year'];
} else { // Set a default sorting year to current year
$year= date('Y'); 
}

$query = "
SELECT articles.article_id, articles.title, LEFT(articles.description, 50) AS abbrev, articles.status, article_associations.article_id, article_categories.category, DATE_FORMAT(articles.date_entered,'%M') as month,DATE_FORMAT(articles.date_entered,'%Y') as year, DATE_FORMAT(articles.date_entered,'%d %M %Y') as date 
FROM articles 
INNER JOIN (article_categories 
		INNER JOIN article_associations ON article_categories.article_category_id = article_associations.article_category_id) ON articles.article_id = article_associations.article_id 
WHERE articles.article_id = article_associations.article_id 
AND article_associations.article_category_id=$id 
AND article_associations.approved = 'Y' 
AND status='1' 
AND DATE_FORMAT(articles.date_entered,'%M') = '$order_by' 
AND DATE_FORMAT(articles.date_entered,'%Y') = '$year' 
ORDER BY date_entered DESC";

$result = @mysql_query ($query); // Run the query.
$num = mysql_num_rows ($result); // How many users are there?

if ($num > 0) { // If it ran OK, display the records.

echo "<h1>There is currently $num new articles for the month of $order_by</h1>";
echo '<table cellpadding="3" cellspacing="3" border="0" align="center" width="100%">';
echo '<tr>
<td><p><b>Title:</b></p></td>
<td><p><b>Description:</b></p></td>
<td> </td>
<td> </td>
</tr>';
// Fetch and print all the records.
$bg = '#CCCCCC'; // Set the background color.

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
	$category = '' .$row['5']. '';
	$bg = ($bg=='#CCCCCC' ? '#FFFFFF' : '#CCCCCC'); // Switch the background color.

		echo '<tr bgcolor="' . $bg . '">
			<td>' .$row['1']. '</td>
		  	<td>';
			   $extract = $row['2'];
			   // find position of last space in extract
			   $lastSpace = strrpos($extract, ' ');
			   // use $lastSpace to set length of new extract and add ...
			   echo substr($extract, 0, $lastSpace).'... ';		
		echo '</td>';
		echo '<td>'.$row['7'].'</td>';
		echo "<td><a href=\"articles.php?aid={$row['0']}\">Read More...</a></td>";
		echo '</tr>';	
}
echo '</table>';

echo '<h1 style="text-align: right; color: #AE0026;">'.$category.'</h1>';
echo '<p> </p>';


} else { // Not records in related to that category ID.
echo '<p class="error">There are currently no news articles!</p>';
}

echo '<p>Sort By:</p>';

$year = date("Y");	//get the current year
$startDate = "1 january".$year;	// set the end date to current year
function printMonths($var)
{
$id="18";	//include the category id for the links	
$start = strtotime($var);	//timestamp of the entered date
$now = strtotime("Now");	//timestamp of now so it does not write anything in the future	
	while ($now > $start)	//while the start is less than now
	{
		echo '<a href="news_archive.php?s=&id=' . $id . '&month=' . date("F", $now) .'">'.date("F", $now).'</a>';
		echo " | ";
		$now = strtotime("-1 month", $now);	// subtract a month from the start timestamp
	}			
}	
printMonths($startDate);	//execute the function

echo '<p> </p>';

$startYear = "1 january 2006";	//get the starting year
function printYears($var)
{
$id="18";	//include the category id for the links	
$start = strtotime($var);	//timestamp of the entered date
$now = strtotime("Now");	//timestamp of now so it does not write anything in the future	
	while ($now > $start)	//while the start is less than now
	{
		echo '<a href="news_archive.php?s=&id=' . $id . '&year=' . date("Y", $now) .'">'.date(" Y", $now).'</a>';
		echo " | ";
		$now = strtotime("-1 year", $now);	// subtract a month from the start timestamp
	}			
}	
printYears($startYear);	//execute the function	

mysql_close(); // Close the database connection.

require('includes/footer.html');
?>

The years show but cannot get the sql to work - sorry!