[SOLVED] sorting results by the week

[bschultz]

I have a "recipe of the day" section on a site.  All of the recipes are in the database.  I'd like to sort them by the week...

 

so like this:

 

friday week 2

thursday week 2

wednesday week 2

tuesday week 2

monday week 2

 

friday week 1

thursday week 1

wednesday week 1

tuesday week 1

monday week 1

 

So, after each Monday recipe, I want an extra line break.  The dates are in the db as type "date"...and I currently sort them date the date_column DESC.

 

I'm guessing that the easiest way would be to figure out the day of the week of each db entry, and if it's Monday, add the extra <br />. 

 

Any other ways (easier) to do this?

[kickstart]

Hi

 

Think I would do it in the php code, and output the line break (or new table row, or new DIV, or new paragraph) when the week number is different from the previous week number.

 

All the best

 

Keith

[bschultz]

Thanks kickstart...

 

<?php
$sql = "SELECT *, DATE_FORMAT(recipe_day, '%M %e %Y') as newrecipeday, DAYOFWEEK(recipe_day) as dayoftheweek FROM bitk ORDER BY recipe_day DESC";   	
$rs = mysql_query($sql,$dbc);  
$matches = 0; 
while ($row = mysql_fetch_assoc($rs))  {
$matches++; 

echo "<a href='/recipes.php?recipe_day=$row[recipe_day]'>".$row[newrecipeday]." "$row[title]"</a><br />"; 
if ($row[dayoftheweek] == 2){
echo "<br />";
}
}  
if (! $matches) { 
echo ("no matches"); 
}  
?>

 

did the trick.