How to store a list of weekdays in MySQL?

Question

I'm writing an application using PHP and I need to store a list of unique weekdays in MySQL.

In the application, I have an array to store the weekdays, like this:

$days = [
    'Wed',
    'Thu',
    'Sat',
];

I know I can use a SET column, but I prefer to not use this type due incompatibility with the framework I'm using (Laravel).

How can I save this list using a format that I can still search within the stored values? I'd like to retrieve all rows that have a Sunday, for instance.

Answer 1

To solve this problem, I end up using a bitmask to store the weekdays.

I changed the weekdays array contents to something like this:

$days = [
     8, // Wednesday
    16, // Thursday
    64, // Saturday
];

Using this list as reference:

 1 : Sunday
 2 : Monday
 4 : Tuesday
 8 : Wednesday
16 : Thursday
32 : Friday
64 : Saturday

Then I added a TINYINT column to hold the weekdays bitmask. When storing the values in the database I can simply use the following:

$valueToMySQL = array_sum($days); // 88, with the sample above

To search for rows with a specific weekday, Saturday for example, I can use this condition:

... WHERE `weekdays` & 64;

Retrieve the weekdays from database as an array is a little less simple. I'm using the following logic:

$days = [];
$bitmask = strrev(decbin($valueFromMySQL));
for ($i = 0, $s = strlen($bitmask); $i < $s; $i++) {
    if ($bitmask{$i}) {
        $days[] = pow(2, $i);
    }
}

If I need to retrieve all rows with the same weekday as the current date, I can pass the current weekday to the previous SQL condition as follows:

$weekday = (int) date('w'); // 0 for Sunday, to 6 for Saturday
$weekdayToMySQL = pow(2, $weekday); // 1 - 64

Then:

... WHERE `weekdays` & {$weekdayToMySQL};