-4

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?

Jim Jimmity
  • 43
  • 3
  • Just to note , it's really unusual to 'hard delete' data in this way – Strawberry Jun 19 '21 at 18:28
  • 1
    please post the complete error message, if all variables are set this should, but there are many things that can cause an error – nbk Jun 19 '21 at 18:37
  • Your prepare call shouldn't return false unless you have mysqli error reporting disabled. Read [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) – Dharman Jun 19 '21 at 18:52
  • There's a whole mishigoss about setting delimiters in MySql stored code. (EVENTS are stored code, along with triggers, stored functions and stored procedures.) I don't see any of that in your sample code, so you may have to remediate that before your stuff will work. Read this https://stackoverflow.com/questions/10259504/delimiters-in-mysql – O. Jones Jun 19 '21 at 19:04
  • 1
    Don't do this. Store the expiry time in the table along with the other event data. Create one MySQL event that runs every few minutes that deletes all the expired events. No need to create events on the fly. – Tangentially Perpendicular Jun 19 '21 at 19:18

1 Answers1

1

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!

Jim Jimmity
  • 43
  • 3