Archive for the ‘Content Management’ Category

Changing the sort order in mySQL to give a rolling month

Friday, April 2nd, 2010

I have been working on a new website for Sophie’s Baked Delights of Bourton-on-the-Water, Sophie creates the yummiest home-baked cakes (a sample was one of the perks of this particular job) and wanted a website to market her delicious wares.

One of the pages displays the monthly menu for Sophie’s Baked Delights Club, a cake club where subscribers get a different home-baked cake delivered to them once per month.  Each month subscribers can choose from  two different cakes made with seasonal ingredients. Quite simple really but we wanted the page to display the menu in monthly order with the current month at the top.

The cake details are stored in a mySQL database so Sophie can go in and change them as she changes her menu.  But we only store the month rather than the full date so that the menu can just roll over from year to year without unneccesary maintenance being required.    This meant that we couldn’t just read through the file by date nor (except in January) could we just read the months in their normal (Jan, Feb, Mar…) order, if the current month is April we want to display the months as Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, Jan, Feb, Mar (stored as 4,5,6,7,8,9,10,11,12,1,2,3).

Specifying a sort sequence in mySQL

Fortunately mySQL has a mechanism for doing this, you can use ORDER BY FIELD in your select statement to specify the order you require.  So the code we need is;

SELECT * FROM cake_club_menu ORDER BY FIELD(menu_month,4,5,6,7,8,9,10,11,12,1,2,3) , menu_sequence_nbr

This selects the records we want and sorts them on the field menu_month but in the sequence specified.  The menu_sequence_nbr is a secondary sort field because we have 2 cakes per month and need to keep them in the right column.

Using PHP to Create the ORDER BY FIELD statement

Of course we can’t hard code the ORDER BY FIELD statement as the sequence will change every month, so I created a PHP function to create the  statement, hopefully the following code is pretty clear

<?php
//
// PHP functions for Sophies Baked Delights
//
function orderByFieldText()
{
//
// Dynamically create text for Order By Field mySQL statement
//   This allows us to list items in month order with the current month first and rolling in to next year
//
//  First get the current month
//
$thisMonth =(date("n"));
//
//  Now set the base text for the statement - menu_month is the database field we want to order by
//
$orderByField = "ORDER BY FIELD(menu_month,";
//
// Now loop through 12 times to create the list of month numbers for the statement
//
for ($i = 1; $i <= 12; $i +=1 )
 {
 $orderByField .= $thisMonth;   // Add this month to the statement
 if ($i <> 12) {                // If this isn't the last item in the list insert a comma
 $orderByField .= ',';
 }
 else {                         // Else insert a closing bracket
 $orderByField .= ')';
 }
 if ($thisMonth == 12)          // If we have got to December (12) next month is January (1)
 {
 $thisMonth = 1;
 }
 else {                         //  Else next month is this month +1
 $thisMonth += 1;
 }
 }
return $orderByField;
}
?>

Reading the database and displaying the menu

Then we just need to call the function in the main PHP module and loop through the returned menu records writing the data to the screen as we go.   As you can see from the following code I used a table with a row for each month.

<table id="cakeClubMenu">
 <tbody>
<?php
//
// Populate the order by field for the database lookup
//
$orderByField = orderByFieldText();
//
// Read the cake club menu file to populate the table
//
$sql = "SELECT * FROM cake_club_menu " . $orderByField ." , menu_sequence_nbr";
$menu_array = mysql_query($sql);
$i = 0;
//
// Now write each line of the table in turn
//
while($menu = mysql_fetch_array($menu_array)) {
 // convert month number to month name for display - thanks to <a title="An easy way to convert a month number to a month name in PHP" href="http://www.gilbertpellegrom.co.uk/php-month-number-to-month-name-the-easy-way/">www.gilbertpellegrom.co.uk</a>
 $monthNum = $menu['menu_month'];   
 $monthName = date("F", mktime(0, 0, 0, $monthNum, 10));
//
//
 switch ($menu['menu_sequence_nbr'])
 {
 case 1 :  // 1st cake for the month starts the row
 echo("&lt;tr&gt;\n");
 echo('&lt;td&gt;'.$monthName.'&lt;/td&gt;'."\n");
 echo('&lt;td&gt;&lt;strong&gt;'.$menu['menu_cake_name'].'&lt;/strong&gt; - '.$menu['menu_cake_description'].'&lt;/td&gt;'."\n");
 break;
 case 2 :  // 2nd cake finishes the row
 echo('&lt;td&gt;OR&lt;/td&gt;'."\n");
 echo('&lt;td&gt;&lt;strong&gt;'.$menu['menu_cake_name'].'&lt;/strong&gt; - '.$menu['menu_cake_description'].'&lt;/td&gt;'."\n");
 echo('&lt;/tr&gt;'."\n");
 break;
 default : // If a 3rd cake is required then this will need modification - currently not allowed
 break;
 }
 }
echo('&lt;/tbody&gt;'."\n");
echo('&lt;/table&gt;'."\n");
?&gt;

You can see the finished product on the Sophie’s Baked Delights website, and I can assure you that the cakes are very good indeed.

Hopefully someone will find this method useful.  As with most problems the answers are all on the internet, but sometimes you need to find the answer before you know what you should have been searching for.