phpmyadmin sql event getting error when trying to declare variable

Question

I'm new to sql so bear with me, but I'm trying to create an event that will populate the row of a table at a specific time with values from 1 to x where x is a pre-declared variable. I'm attempting to use a delimiter for the first time and I'm not really sure where all my semi-colons should be at but the following is what I assumed after a while of reading:

DELIMITER $$
    DECLARE day_m INT DEFAULT 0;
    SET day_m = DATE_FORMAT(CURRENT_DATE, '%e');

    WHILE day_m <= DATE_FORMAT(LAST_DAY(CURRENT_DATE), '%e')
        BEGIN
            INSERT INTO monthly_scheduling (days) values (day_m)
            SET day_m = day_m + 1
        END;
$$
DELIMITER ;

The error I'm getting is as follows: MySQL said: #1064 - 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 '$$ DECLARE day_m INT DEFAULT 0; SET day_m = DATE_FORMAT(CURRENT_DATE, ' at line 1

I've been trying all sorts of things but I can't get the event to create because of the error I seem to keep getting at the DECLARE section. Any help would be much appreciated.

Do you need an @ symbol when setting your variable? Also your WHILE line has the variable as "days_m" instead of "day_m", not sure if that is a typo in your code or copy paste. — Jacob H, May 11 '17 at 20:44
From what I understand you only need the @symbol for session specific variables rather than local variables. Actually the @ symbol was how I was first declaring the variables until I read further into it. Thanks for pointing out the typo, I removed it from the code but I still get the error at the same position in the code: right at the DECLARE statement. — cstmxyz, May 11 '17 at 20:48
The error is including your $$ delimiter as part of the syntax. Check this thread: http://stackoverflow.com/questions/20313481/mysql-delimiter-syntax-error-at-line-1 — Jacob H, May 11 '17 at 20:51
Yea I've removed the delimiter from the syntax but I still get an error at the same spot. I checked that thread but it seems that it directs you to setting a delimiter in the "Run SQL Query/Queries" section of phpmyadmin, whereas I'm trying to create an event and that delimiter option isn't available within the event creation. — cstmxyz, May 11 '17 at 21:03

phpmyadmin sql event getting error when trying to declare variable

0 Answers0