MySQL SortBy Month and or Year

[jarvis]

Hi,

I'm  :'( to sort this one. It's driving me mad literally - 

 

I'm pulling a list of articles out of a DB with the following query

$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') = '$month' 
AND DATE_FORMAT(articles.date_entered,'%Y') = '$year') 
ORDER BY date_entered DESC";

I then use 2 functions to create loops - one for months (current -> january):

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

One for the years

$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	

Although I've got articles with dates back to Aug 08, it simply wont work. I then thought about combining them:

$startYear = "1 january 2006";	//get the starting year
function printCombi($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) .'&month=' . date("F", $now) .' ">'.date("M Y", $now).'</a>';
		echo " | ";
		$now = strtotime("-1 month", $now);	// subtract a month from the start timestamp
	}			
}	
printCombi($startYear);	//execute the function	

Although I'd rather they were seperate, i think it will need to be done like the latter, unless I alter the query.

 

What I need is simply the current month -> Jan of the current year. Then ideally to select a year, it will then show say, Dec -> Jan 2008.

 

Any help is much appreciated!

 

Thanks

[perrij3]

I am still pretty new to php, but maybe this will help. On a from I created, I have a drop down box that a user can select the month for a birth date. When the form loads, the month is automatically set to the current month. Maybe you can edit the code I used to do this to work for what you are trying to do. The current month is being set by the $thisMonth = date('n') line.

 

 

        	<?php
			$birthmonth = array('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec');
			ini_set('date.timezone', 'America/Detroit');
			$thisMonth = date('n');
			for ($i=1;$i<=12;$i++) { ?>
        <option value="<?php echo $i < 10 ? '0'.$i : $i; ?>">
        <?php echo $birthmonth[$i-1]; ?>
        </option>
        <?php } ?>
        </select>

[jarvis]

Thanks perrij3,

 

I think im sort of there(?), I use:

// Set the sorting order by months
if (isset($_GET['month'])) {
// $month will be appended to the links
$month = $_GET['month'];
} else { // Set a default sorting month to current month
$month= 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'); 
}

They pass the selected value into the query. It seems that the months one works for August only and if you select the years, nothing happens.

Hmmm....

[jarvis]

Sorry to bump this one but am going nuts trying to work it out!

 

Thanks