Why Does My PHP Script Fail to Create a MySQL Event with a 'Syntax Error'?

Mysql Event Error Using PHP: Resolving the Syntax Issue

When attempting to create a MySQL Event using a PHP script, users may encounter the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DELIMITER' at line 1

To resolve this error when initiating a MySQL Event via PHP:

1. Modify the SQL Query:

   • Replace the DELIMITER | with DELIMITER $$.

2. Ensure Event Scheduler is Enabled:

   • Before creating the event, ensure the event scheduler is turned on by executing: SET GLOBAL event_scheduler = ON;

3. Use Correct Syntax:

   • The correct syntax for creating an event using PHP should be as follows:

     $sql = "CREATE EVENT myevent21222
             ON SCHEDULE EVERY 5 MINUTE STARTS '2016-01-01 00:00:00'
             ON COMPLETION PRESERVE
             DO
               BEGIN
                 UPDATE `team` SET `reg` = '0' WHERE `id` = '1';
               END
             DELIMITER $$";

Alternative to Scheduled Data Modification:

If the desired functionality is to change data in the database after a specific time interval following a user action, an alternative approach is to use a combination of PHP and MySQL triggers. This allows for data modification to occur automatically upon specific database operations, such as insertions or updates.

The above is the detailed content of Why Does My PHP Script Fail to Create a MySQL Event with a 'Syntax Error'?. For more information, please follow other related articles on the PHP Chinese website!