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.
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};
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With