Get a list of todays events from a recurrence column

[SamJ]

Hi,

I haven't used PHP to create my own code for a few decades. Please go easy on me.

I am working with a MySQL database that runs a schedule. The schedule works from the recurrence column, with seven numbers (0 or 1). These numbers represent the days of the week (Monday to Sunday). 1s will trigger an event, 0s will not.

For example, if the recurrence column has 1000000, the task will only trigger on a Monday.

1100100 will trigger on Mondays, Tuesdays and Fridays

There are also fields for start date and end dates.

I would like to use PHP to extract the current days schedule. Can anyone provide advice on how this can achieved?

Example table

Recurrence     Startdate          Enddate              Descrption
1000000        20/02/2024      20/02/2030        Task1
1100100         28/02/2024      02/03/2024        Task 2

I'm confident that if someone can point me in a direction of "best approach", I can muddle my way through and learn along the way.

Thanks.

Edited February 25 by SamJ

[requinix]

Is there any chance whatsoever that you can change the nature of that column? As you've found out, storing the recurrence information with a number like that makes it very hard to work with, and there are far simpler and easier schemes you could make use of instead...

[SamJ]

Unfortunately, this is not possible. This is software created by a company, who's core software is based on the data. 

[gizmola]

Without an actual spec for what the format of the output would be, here's a simple function that returns an array of the days indicated.

 

function toDayofWeekArray(string $schedule) {

    $days = [
      'Monday',
      'Tuesday',
      'Wednesday',
      'Thursday',
      'Friday',
      'Saturday',
      'Sunday'
    ];
    
    return array_combine($days, str_split($schedule));
}

 

It's important to note that this must be a string in the database, because, if you for example have this:   0000100 from the database, and PHP turns that into an integer, the function above won't work, because your leading zeros will be lost.  It must remain a string for this to work correctly.

 

Little test:

$i = "0001001";
var_dump(array_filter(toDayOfWeekArray($i)));

// Should return this
array(2) {
  ["Thursday"]=>
  string(1) "1"
  ["Sunday"]=>
  string(1) "1"
}

 

This is a simplified and combined version, that includes the filtration, and removes the left over array values:

 

function toDayofWeekArray(string $schedule) {

    $days = [
      'Monday',
      'Tuesday',
      'Wednesday',
      'Thursday',
      'Friday',
      'Saturday',
      'Sunday'
    ];
    
    return array_keys(array_filter(array_combine($days, str_split($schedule))));
}

 

[SamJ]

Thank you @gizmola, that worked!

I've been reading information on the array functions you used; I feel like I am running before I have learned to walk!

I can print the days of the week each row will be trigged.

To extract the list of events the will be trigged today; my initial thought is to search the array against the current day. If today is in the array, then it will be trigged and should be displayed. The same logic can apply for the following days. 

I have successfully searched against an array without using a function with arraykeys.  But I am struggling to search the code you provided. Can you provide any advice how this can be achieved?

Thanks again. 

[gizmola]

28 minutes ago, SamJ said:

Thank you @gizmola, that worked!

I've been reading information on the array functions you used; I feel like I am running before I have learned to walk!

I can print the days of the week each row will be trigged.

To extract the list of events the will be trigged today; my initial thought is to search the array against the current day. If today is in the array, then it will be trigged and should be displayed. The same logic can apply for the following days. 

I have successfully searched against an array without using a function with arraykeys.  But I am struggling to search the code you provided. Can you provide any advice how this can be achieved?

 

I would need some example data to understand what this list of events looks like.  There is no problem with you using a foreach loop or something like that, if it accomplishes your goal.  There are also functions like array_search that might be part of a solution.  

[SamJ]

Thank you for the pointer. I will keep chipping away.

I have uploaded the SQL export of the table. If you have the time and energy any further help would be appreciated.

Cheers   

sql.txt

[Barand]

An easier approach is to search for those records with today's char in the recurrence column set to "1"

$sql = "SELECT id
             , recurrence
             , description
             , begindate
             , enddate
        FROM schedule
        WHERE ? BETWEEN begindate AND enddate
          AND SUBSTRING(recurrence, WEEKDAY(?)+1, 1)
          AND active
       ";
$res = $pdo->prepare($sql);

$searchDate = '2024-02-28';
$res->execute([$searchDate, $searchDate]);

Example outputs

searchdate = Feb 26 2024 (Monday)
+----+------------+-------------+------------+------------+
| id | recurrence | description | begindate  | enddate    |
+----+------------+-------------+------------+------------+
| 39 | 1010100    | Event1      | 2023-08-31 | 3000-01-01 |
+----+------------+-------------+------------+------------+


searchdate = Feb 28 2024 (Wednesday)
+----+------------+-------------+------------+------------+
| id | recurrence | description | begindate  | enddate    |
+----+------------+-------------+------------+------------+
| 39 | 1010100    | Event1      | 2023-08-31 | 3000-01-01 |
| 51 | 0010000    | Event2      | 2023-08-31 | 3000-01-01 |
+----+------------+-------------+------------+------------+

 

[gizmola]

As usual Barand to the rescue with a really elegant solution.  Given what I understand about your needs, this is what I'd do.  I'd assume you need a job that takes an Event id and actually performs some action. 

• Use Cron to run Barand's job 1x daily.
• Loop through results and exec the event handler script(s) as required.