0

I have:

enum('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun')<BR>

I'd like to be able to sort, starting with 'today' or date(date_format(%a))

Is there a way to address the index of enum and kind-of do a ROL? (sorry, old-school assembly language there) Or, is it easier to do in PHP first? currently I just have a regular 

SORT BY students.day (the enum field)
jrbedard
  • 3,662
  • 5
  • 30
  • 34
sukebe7
  • 89
  • 6

1 Answers1

0

you can do it in SQL like this:

select * from sortday
order by 
   (myday + DAYOFWEEK(NOW())) MOD 7;

sample

mysql> select * from sortday;
+----+-------+----------------+
| id | myday | val            |
+----+-------+----------------+
|  1 | Mon   | Text from mo   |
|  2 | Tue   | text from Tue  |
|  3 | Wed   | Text from Wed  |
|  4 | Thu   | day Tue        |
|  5 | Fri   | this is friday |
|  6 | Sat   | its sat        |
|  7 | Sun   | ist sunday     |
+----+-------+----------------+
7 rows in set (0,00 sec)

mysql> select * from sortday order by (myday + DAYOFWEEK(NOW())) MOD 7;
+----+-------+----------------+
| id | myday | val            |
+----+-------+----------------+
|  3 | Wed   | Text from Wed  |
|  4 | Thu   | day Tue        |
|  5 | Fri   | this is friday |
|  6 | Sat   | its sat        |
|  7 | Sun   | ist sunday     |
|  1 | Mon   | Text from mo   |
|  2 | Tue   | text from Tue  |
+----+-------+----------------+
7 rows in set (0,00 sec)

mysql>
Bernd Buffen
  • 14,525
  • 2
  • 24
  • 39
  • Thanks so much. I tried both a number and "Fri" for 'myday' but the order came up the same. Perhaps it's the way I have it set up, which I'm not married to: – sukebe7 Jan 13 '17 at 11:35
  • `CREATE TABLE `days` ( `daynum` tinyint(1) NOT NULL auto_increment, `day` enum('Mon','Tue','Wed','Thu','Fri','Sat','Sun') NOT NULL, PRIMARY KEY (`daynum`) ) INSERT INTO `days` VALUES(1, 'Mon'); INSERT INTO `days` VALUES(2, 'Tue'); INSERT INTO `days` VALUES(3, 'Wed'); INSERT INTO `days` VALUES(4, 'Thu'); INSERT INTO `days` VALUES(5, 'Fri'); INSERT INTO `days` VALUES(6, 'Sat');` – sukebe7 Jan 13 '17 at 11:41