Dynamically Create MySQL Event

My site allows users to create events, and I need those events deleted from the MySQL database after they occur. Hence, when the user creates their event I am trying to schedule an event in the database that deletes the user's event information soon after the time the user provided for it.

For instance, I'd want the event to be like the others here:

enter image description here

Here is one attempt of what I tried:

$stmtstr = "
        CREATE EVENT event_" . $roomID . "_" . $current_time . "_DELETEPRAYER
        ON SCHEDULE AT '" . $timeToDeletePrayerFormatted . "'
        DO
        DELETE FROM prayer_rooms WHERE roomID = $roomID
        ";
$stmt3 = $conn->prepare(strval($stmtstr));

$roomID, $current_time, and $timeToDeletePrayerFormatted are all valid values. Even so, prepare() returns a bool here, indicating that something is wrong with the MySQL statement. I ran the statement manually with the exact same string that is going into prepare() here and it ran successfully without error.

I have also tried using ?s and bind_param() but without success.

Is there simply a limitation on what types of MySQL statements can be dynamic? Is there any way I can create a scheduled event to occur in the database based on a time provided by the user?

1 answer

  • answered 2021-06-19 19:52 Jim Jimmity

    Per Tangentially Perpendicular's comment, I stored all the expiry times of each even in their respective table and created one MySQL event that runs every few minutes and deletes those events based on their expiry time. I really appreciate the help!