像MWF，TThS这些缩短的日子（这是我的班级时间表）

Question

Need help on this: in my sql table I have this Schedule and is written in MWF, TThS, Sun. I want it to be filtered so that only the schedule for today will be the output...e.g Today is Tuesday so TThS schedule is being outputted. Hope you could help me.

Answer 1

The problem with the way your schedules times are stored is that you can't do a simple LIKE %T%, as it would get schedules for Th as well.

A possible solution would be to use MySQL REGEXP for the specific cases of Tuesday (T not followed by h) and Saturday (S not followed by un).

Basically you would build an array of the where clauses that should be used depends of the week day:

$where_date_clauses = array(
    1 => 'LIKE "M%"',
    2 => 'REGEXP "T(?!h)"',
    3 => 'LIKE "%W%"',
    4 => 'LIKE "%Th%"',
    5 => 'LIKE "%F%"',
    6 => 'REGEXP "S(?!un)"',
    7=> 'LIKE "%Sun"'
);

The regexp here use the negative lookahead pattern - it will match only if the character is not immediately followed by the lookahead content.

And then build your query like this:

$sql = 'SELECT * FROM schedules_table WHERE schedule ' . $where_date_clauses[date('N')];

Where date('N') return the number of the week day.

Side note

Here is an example of how to set up your query using PDO:

try {
    $dbh = new PDO('mysql:dbname=your_db_name;host=your_db_host', 'mysql_user_name', 'mysql_user_password');
    $sth = $dbh->prepare('SELECT * FROM block_subject2 where id_number = ? ORDER BY timed DESC LIMIT 1');
    $sth->execute(array(
        (int) $_SESSION['stud_id']
    ));
    $row = $sth->fetch();
} catch(PDOException $e) {
    echo 'Failed to connect to DB: ' . $e->getMessage();
}