{"id":138,"date":"2010-04-02T12:45:44","date_gmt":"2010-04-02T11:45:44","guid":{"rendered":"http:\/\/www.cotsweb.com\/blog\/?p=138"},"modified":"2010-04-02T12:45:44","modified_gmt":"2010-04-02T11:45:44","slug":"changing-the-sort-order-in-mysql-to-give-a-rolling-month","status":"publish","type":"post","link":"https:\/\/www.cotsweb.com\/blog\/changing-the-sort-order-in-mysql-to-give-a-rolling-month-138.html","title":{"rendered":"Changing the sort order in mySQL to give a rolling month"},"content":{"rendered":"

I have been working on a new website for Sophie’s Baked Delights of Bourton-on-the-Water<\/a>, 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.<\/p>\n

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.\u00a0 Each month subscribers can choose from\u00a0 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.<\/p>\n

The cake details are stored in a mySQL database so Sophie can go in and change them as she changes her menu.\u00a0 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.\u00a0\u00a0\u00a0 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).<\/p>\n

Specifying a sort sequence in mySQL<\/h3>\n

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

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\n<\/pre>
\nThis selects the records we want and sorts them on the field menu_month but in the sequence specified.\u00a0 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.<\/p>\n
Using PHP to Create the ORDER BY FIELD statement<\/h3>\n
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\u00a0 statement, hopefully the following code is pretty clear
\n
&lt;?php\n\/\/\n\/\/ PHP functions for Sophies Baked Delights\n\/\/\nfunction orderByFieldText()\n{\n\/\/\n\/\/ Dynamically create text for Order By Field mySQL statement\n\/\/\u00a0\u00a0 This allows us to list items in month order with the current month first and rolling in to next year\n\/\/\n\/\/\u00a0 First get the current month\n\/\/\n$thisMonth =(date("n"));\n\/\/\n\/\/\u00a0 Now set the base text for the statement - menu_month is the database field we want to order by\n\/\/\n$orderByField = "ORDER BY FIELD(menu_month,";\n\/\/\n\/\/ Now loop through 12 times to create the list of month numbers for the statement\n\/\/\nfor ($i = 1; $i &lt;= 12; $i +=1 )\n {\n $orderByField .= $thisMonth;\u00a0\u00a0 \/\/ Add this month to the statement\n if ($i &lt;&gt; 12) {\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ If this isn't the last item in the list insert a comma\n $orderByField .= ',';\n }\n else {\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ Else insert a closing bracket\n $orderByField .= ')';\n }\n if ($thisMonth == 12)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ If we have got to December (12) next month is January (1)\n {\n $thisMonth = 1;\n }\n else {\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/\u00a0 Else next month is this month +1\n $thisMonth += 1;\n }\n }\nreturn $orderByField;\n}\n?&gt;\n<\/pre><\/p>\n
Reading the database and displaying the menu<\/h3>\n
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. \u00a0 As you can see from the following code I used a table with a row for each month.
\n
&lt;table id="cakeClubMenu"&gt;\n &lt;tbody&gt;\n&lt;?php\n\/\/\n\/\/ Populate the order by field for the database lookup\n\/\/\n$orderByField = orderByFieldText();\n\/\/\n\/\/ Read the cake club menu file to populate the table\n\/\/\n$sql = "SELECT * FROM cake_club_menu " . $orderByField ." , menu_sequence_nbr";\n$menu_array = mysql_query($sql);\n$i = 0;\n\/\/\n\/\/ Now write each line of the table in turn\n\/\/\nwhile($menu = mysql_fetch_array($menu_array)) {\n \/\/ 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>\n $monthNum = $menu['menu_month'];\u00a0 \u00a0\n $monthName = date("F", mktime(0, 0, 0, $monthNum, 10));\n\/\/\n\/\/\n switch ($menu['menu_sequence_nbr'])\n {\n case 1 :\u00a0 \/\/ 1st cake for the month starts the row\n echo("&lt;tr&gt;\\n");\n echo('&lt;td&gt;'.$monthName.'&lt;\/td&gt;'."\\n");\n echo('&lt;td&gt;&lt;strong&gt;'.$menu['menu_cake_name'].'&lt;\/strong&gt; - '.$menu['menu_cake_description'].'&lt;\/td&gt;'."\\n");\n break;\n case 2 :\u00a0 \/\/ 2nd cake finishes the row\n echo('&lt;td&gt;OR&lt;\/td&gt;'."\\n");\n echo('&lt;td&gt;&lt;strong&gt;'.$menu['menu_cake_name'].'&lt;\/strong&gt; - '.$menu['menu_cake_description'].'&lt;\/td&gt;'."\\n");\n echo('&lt;\/tr&gt;'."\\n");\n break;\n default : \/\/ If a 3rd cake is required then this will need modification - currently not allowed\n break;\n }\n }\necho('&lt;\/tbody&gt;'."\\n");\necho('&lt;\/table&gt;'."\\n");\n?&gt;\n<\/pre>
\nYou can see the finished product on the Sophie’s Baked Delights website<\/a>, and I can assure you that the cakes are very good indeed.<\/p>\n
Hopefully someone will find this method useful.\u00a0 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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[20,90,19,18],"tags":[146,145,91,93,92],"_links":{"self":[{"href":"https:\/\/www.cotsweb.com\/blog\/wp-json\/wp\/v2\/posts\/138"}],"collection":[{"href":"https:\/\/www.cotsweb.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.cotsweb.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.cotsweb.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.cotsweb.com\/blog\/wp-json\/wp\/v2\/comments?post=138"}],"version-history":[{"count":7,"href":"https:\/\/www.cotsweb.com\/blog\/wp-json\/wp\/v2\/posts\/138\/revisions"}],"predecessor-version":[{"id":145,"href":"https:\/\/www.cotsweb.com\/blog\/wp-json\/wp\/v2\/posts\/138\/revisions\/145"}],"wp:attachment":[{"href":"https:\/\/www.cotsweb.com\/blog\/wp-json\/wp\/v2\/media?parent=138"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.cotsweb.com\/blog\/wp-json\/wp\/v2\/categories?post=138"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.cotsweb.com\/blog\/wp-json\/wp\/v2\/tags?post=138"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}