select according to particular order

[peppericious]

I teach 6 different class groups, named "A" to "F", respectively.

 

I have a small homework database from which I want to display the most recent 1 item of homework (record) for the class groups I have on any particular day of the week.

 

So, for example, if it's Monday today, I want to display 5 items of homework, in order, for groups "A", "B", "C", "D" and "E".

 

On the other hand, if it's Thursday today, I want to display 3 items of homework, in the order "F", "E" and "D", as that is the way my class groups are ordered on Thursday.

 

I've created a switch statement and a select query as follows but the query isn't pulling the records out as I want them. Can anyone tell me what I'm doing wrong in the select query? How should I combine the auto-incrementing id column and the classgroups column so that the records are displayed as I want them?...

 

(Right now, Tuesday, it's displaying D, C, B, A but it should be displaying A, B, C, D…)

 

TIA

 

switch (date("l")) {
    case 'Monday':
        $ord =  "`id` DESC, FIELD(`classgroup`, 'A', 'B', 'C', 'D', 'E')";
        $lim = 5;
        break;
    case 'Tuesday':
        $ord = "`id` DESC, FIELD(`classgroup`, 'A', 'B', 'C', 'D')";
        $lim = 4;
        break;
    case 'Wednesday':
        $ord = "`id` DESC, FIELD(`classgroup`, 'F', 'C', 'E')";
        $lim = 3;
        break;
    case 'Thursday':
        $ord = "`id` DESC, FIELD(`classgroup`, 'F', 'E', 'D')";
        $lim = 3;
        break;
    case 'Friday':
        $ord = "`id` DESC, FIELD(`classgroup`, 'F', 'B', 'A')";
        $lim = 3;
        break;
}


$q = "SELECT `id`, `classgroup`, `body`, `pointcles`, `arevoir`, DATE_FORMAT(created, '%a, %b %D') as `cr` FROM `homework` ORDER BY $ord LIMIT $lim";

 

[Barand]

As you need the "most recent" then there should be a date field in there instead of relying on id sequences. I would also ad a "daygroup" table to define the groups you require on each day and the sequence you want them in.

+------------+
|  daygroup  |
+------------+
| id         |
| day        |
| classgroup |
| seq        |
+------------+

eg:

+----+------+------------+------+
| id | day  | classgroup | seq  |
+----+------+------------+------+
|  1 |    2 | A          |    1 |
|  2 |    2 | B          |    2 |
|  3 |    2 | C          |    3 |
|  4 |    2 | D          |    4 |
|  5 |    2 | E          |    5 |
|  6 |    3 | F          |    1 |
|  7 |    3 | E          |    2 |
|  8 |    3 | D          |    3 |
| etc                           |
+----+------+------------+------+

Then use a subquery to determine the latest date for each classgroup. So now you have

SELECT 
    h.date
  , h.classgroup
FROM homework h
    INNER JOIN daygroup dg USING (classgroup)
    INNER JOIN (
        SELECT classgroup, MAX(date) as date
        FROM homework
        GROUP BY classgroup
        ) as latest USING (classgroup, date)
WHERE dg.day = DAYOFWEEK(CURDATE())
ORDER BY dg.seq

[peppericious]

Perfect, Barand. Greatly appreciated. Thank you.