How to schedule dates according to a specified schedule (from a list of times)?

Question

PHP

My user selects the time at which his posts will be published (screenshot):



All these times are stored in the database in a column in the following format: 09:20,09:40,11:20, 11:40(comma-separated list).

The problem is that when all these dates are planned for the current day, the time remains empty, since I delete the planned ones from the array. Therefore, when the array $datesis already empty, I have to take the last line from the base datetimeand add + 1 hour to it.

And it should be like this: if all these times are already planned for the current day, then you should start planning a new one the very next day:

This is how it should be planned:
2021-09-10 09:20 2021-09-10
09:40
10 11:20
2021-09-10 11:40
2021-09-11 09:20
2021-09-11 09:40
2021-09-11 11:20
2021-09-11 11:40
2021-09-12 09:20
2021-09-12 09:40

Here is part of this code:

$dateTime = date('Y-m-d H:i:s');

$wallGet = $main->requestVkAPI('wall.getById', "posts=-".$cal[0]['id_group']."_".$_POST['post_id']."&extended=1&access_token={$user[0]['token']}");

        foreach($wallGet['items'] as $wall) {

        // По расписанию
          if ($cal[0]['posting_date'] == 1) {

$dates_time = explode(",", mb_strtolower('09:20,09:40,11:20,11:40')); // указал примерно, тут до 50 таких времен может быть
$dates = array_merge($dates_time);

            // Если есть уже отложка на это время, то берем следующее
            $sql = $db->dbStream->prepare(" SELECT * FROM `autoposting_planned` WHERE `id_group` = ? ORDER BY `datetime` DESC ");
            $sql->bindValue(1, $wall['owner_id'], PDO::PARAM_INT);
            try {
              $sql->execute();
            } catch (PDOException $error) {
              trigger_error("Ошибка при работе с базой данных: {$error}");
            }
            $rowInfo = $sql->fetchAll(PDO::FETCH_ASSOC);

// Делаю выборку, если пост на это время уже сегодня запланирован, то время убираю из списка
            foreach ($rowInfo as $g) {
              $value_to_delete = date('H:i', strtotime($g['datetime']) );
              $dates = array_flip($dates);
              unset($dates[$value_to_delete]);
              $dates = array_flip($dates);
            }

            function closest($dates, $findate)
            {
              $newDates = array();

              foreach ($dates as $date) {
                $newDates[] = strtotime($date);
              }

              sort($newDates);
              foreach ($newDates as $a) {
                if ($a >= strtotime($findate))
                  return $a;
              }
              return strtotime( date('Y-m-d H:i', next($newDates)) . '+ 1 days');
            }

            $values = closest($dates, $dateTime);
            $timeposting = date('Y-m-d H:i', $values);

// Если дата этого времени прошла переводим на +1 час вперед от последней даты
// Вот это и добавил (это не нужно)!! Должно планировать на следующий день уже если времена закончились на сегодня
            if( strtotime($timeposting) < strtotime(date('Y-m-d H:i')) ) {
              $timeposting = date('Y-m-d H:i', strtotime( $rowInfo[0]['datetime']. '+ 1 hour'));
            }

// Заносим отложенный пост в базу
 $sql = $db->dbStream->prepare(" INSERT INTO `autoposting_planned` (`post_id`, `id_group`, `owner_id`, `datetime`) VALUES (?,?,?,?) ");
            $sql->bindValue(1, $wall['id'], PDO::PARAM_INT);
            $sql->bindValue(2, $wall['owner_id'], PDO::PARAM_INT);
            $sql->bindValue(3, $user[0]['id'], PDO::PARAM_INT);
            $sql->bindValue(4, $timeposting, PDO::PARAM_STR);
            try {
            $sql->execute();
            } catch (PDOException $error) {
            trigger_error("Ошибка при работе с базой данных: {$error}");
            }

}

}

Probably it can be done much easier and faster, BUT I CAN'T UNDERSTAND HOW TO DO IT. So far I've only thought of this.

Answer 1

A simplified example of how to insert a record with given values ​​into the table at 09:20, 09:40, 11:20 and 11:40 every day.
Tables:
1. Values ​​to insert

CREATE TABLE data ( 
    data_id INT AUTO_INCREMENT PRIMARY KEY,
    data_for_insertion VARCHAR(255) );
INSERT INTO data VALUES (1, 'Новое сообщение по расписанию', DEFAULT);

2.Schedule.

CREATE TABLE schedule ( 
    schedule_id INT AUTO_INCREMENT PRIMARY KEY,
    data_id INT,
    FOREIGN KEY (data_id) REFERENCES data (data_id) ON DELETE CASCADE ON UPDATE CASCADE,
    shedule_time TIME );
INSERT INTO schedule VALUES 
(NULL, 1, '09:20'), 
(NULL, 1, '09:40'), 
(NULL, 1, '11:20'), 
(NULL, 1, '11:40');

3. Table where records should be inserted

CREATE TABLE main ( 
    main_id INT AUTO_INCREMENT PRIMARY KEY,
    inserted_data VARCHAR(255),
    created_as DATETIME DEFAULT CURRENT_TIMESTAMP );

The procedure that will insert the data

CREATE EVENT schedule
ON SCHEDULE EVERY 1 MINUTE
DO
    INSERT INTO main (inserted_data)
    SELECT data.data_for_insertion
    FROM data
    JOIN schedule USING (data_id)
    WHERE schedule_time > CURRENT_TIME - INTERVAL 1 MINUTE
      AND schedule_time <= CURRENT_TIME;

Everything. It remains only to enable the use of the Event Scheduler and run it.
Now, just know for yourself in your PHP program, edit the data in tables 1 and 2. And at any time ... In terms of performing the right action at the right time, MySQL will do everything itself, and it does not need any PHP.