how to rotate order based on season

[turpentyne]

Maybe somebody knows a good way to do this... I have a simple query that seasonal categories - plus one special events header. then for each header I pull all the classes in that category. Right now it just pulls and orders by id. So, regardless of year, they come out in the same order: spring, winter, fall, special events. I'm trying to see if there's a way to make them show in order of relevance to the current time. right now, the "cat" table just has two fields: cat_id, cat_name.

 

In other words,

in autumn, I want them to order: Autumn, winter, summer, spring - then special events.

In summer, I want them to order: Summer, spring, autumn, winter - then special events.

 

any thoughts on how to do this? step one, order by relevence to current date. step two, special events is always last.

 

$query1 = "select * from tbl_cat ORDER by cat_id";
$result_cats = mysql_query($query1) or die(mysql_error());


while ($row = mysql_fetch_array($result_cats)) {

$query_selectall = "select *,workshop_date AS tester from tbl_workshops WHERE category={$row['cat_id']} and active_inactive = '1' ORDER by workshop_date";
$result_all = mysql_query($query_selectall) or die(mysql_error());
$catid = $row['cat_id'];

$catname = $row['cat_name'];
echo "<font face='georgia' size=5><b>".$catname." </b></font><br><div style='color:#fff;'>";

while ($c_row = mysql_fetch_array($result_all)){

// rest of code pulls items that match that specific category id.

}

 

[requinix]

A little short on details, and I think you mean spring->summer->autumn, but

 

If the seasons were numbered like Spring=1, Summer=2, Autumn=3, Winter=4 (which is #1 doesn't matter as long as they're all in sequential order) then you could use a little bit of math and a UNION.

# the non-special events first
SELECT ... ORDER BY MOD(season + 4 - N, 4)
UNION ALL
# special events
SELECT ...

If N is the number for the "first" season (ie, the current season) then the MOD() will turn the first season number into 0, the next one into 1, then 2, and 3 for the last season. Thus sorting.

An alternative would be a big CASE and could be more efficient.