PHPMyAdmin keeps telling me, that I got an error at line 41 (third last line) . I can't seem to locate it, any help/advice will be appreciated. I've double-checked the IF-THEN syntax, and cursors.
BEGIN
DECLARE `done` BOOLEAN DEFAULT false;
DECLARE `a` TEXT;
DECLARE `cur` CURSOR FOR SELECT `mana_cost` FROM `cards`;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET `done`=TRUE;
OPEN `cur`;
read_loop : LOOP
FETCH `cur` INTO `a`;
IF `done`
THEN LEAVE read_loop;
END IF;
IF `a` LIKE '%{B}%'
THEN
UPDATE `cards` SET `color` = 'BLACK '
WHERE `mana_cost` = `a`;
ELSE IF `a` LIKE '%{G}%'
THEN
UPDATE `cards` SET `color` = 'GREEN '
WHERE `mana_cost` = `a`;
ELSE IF `a` LIKE '%{U}%'
THEN
UPDATE `cards` SET `color` = 'BLUE '
WHERE `mana_cost` = `a`;
ELSE IF `a` LIKE '%{W}%'
THEN
UPDATE `cards` SET `color` = 'WHITE '
WHERE `mana_cost` = `a`;
ELSE IF `a` LIKE '%{R}%'
THEN
UPDATE `cards` SET `color` = 'RED '
WHERE `mana_cost` = `a`;
END IF;
END LOOP; //line 41
CLOSE `cur`;
END
Your problem is very common, it's because MySQL stop to create the procedure when it sees a ";"
you need to use a DELIMITER to wrap your procedure ;)
DELIMITER $
BEGIN
DECLARE done
BOOLEAN DEFAULT false;
DECLARE a
TEXT;
DECLARE cur
CURSOR FOR SELECT mana_cost
FROM cards
;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done
=TRUE;
OPEN cur
;
read_loop : LOOP
FETCH `cur` INTO `a`;
IF `done`
THEN LEAVE read_loop;
END IF;
IF `a` LIKE '%{B}%'
THEN
UPDATE `cards` SET `color` = 'BLACK '
WHERE `mana_cost` = `a`;
ELSE IF `a` LIKE '%{G}%'
THEN
UPDATE `cards` SET `color` = 'GREEN '
WHERE `mana_cost` = `a`;
ELSE IF `a` LIKE '%{U}%'
THEN
UPDATE `cards` SET `color` = 'BLUE '
WHERE `mana_cost` = `a`;
ELSE IF `a` LIKE '%{W}%'
THEN
UPDATE `cards` SET `color` = 'WHITE '
WHERE `mana_cost` = `a`;
ELSE IF `a` LIKE '%{R}%'
THEN
UPDATE `cards` SET `color` = 'RED '
WHERE `mana_cost` = `a`;
END IF;
END LOOP; //line 41
CLOSE cur
;
END $
*DELIMITER ;*
You should try something like that and look the documentation about DELIMITER and MySQL jokes :P