mysql trigger / stored procedure - update another table row by row

Question

I've already, searched and read, many answers about this issue for 3 days, but couldn't get a clear answer on how to do this.

this will be a little long, be patient please.

First of all, I am trying to make a simple lottery app. I hope everyone of you know about the 6/49 lottery thing.

Well, according to game, first, for a week customers play lottery (lotteryTBL). Then 6 numbers between 1-49 are selected as lucky numbers (resultsTBL).

What I want to do is updating lotteryTBL after a new result row is inserted into resultsTBL.

Structure of lotteryTBL:

ID num1 num2 num3 num4 num5 num6 draw_date howmanyknew 
1 5 6 8 15 18 25 08-21-2015 0
2 7 15 18 30 40 45 08-21-2015 0

Structure of resultsTBL:

ID num1 num2 num3 num4 num5 num6 draw_date 
1 15 18 35 38 40 47 08-21-2015 

Now, here is the pseudocode of what i want:

after insert a new row into resultsTBL
 total <- 0
 for each row in the lotteryTBL where lotteryTBL.draw_date = resultsTBL.draw_date
 total <- calculate how many of them are the same with the resultsTBL's row.
 update lotteryTBL set howmanyknew = total where "some condition"

First, I tried to do it with trigger, some said "it is dynamic sql, you cannot use trigger", some said "use stored procedure". And I did it too. But, they never worked.

Anyway, Can someone type a good pseudocode for this? which method do I have to use? what is the logic here?

• oNare: Do you want a Stored Procedure that you can run like db1.sp_test(1) where 1 is the ID from resultsTBL?
• Who Cares: @oNare I don't want any exact approach. I just want something useful to solve this problem. I am open anything useful.
• oNare: Are num* fields fixed to be just 6?

Answer 1

I've made it in a TRIGGER calling a Stored Procedure inside resultsTBL table.

Table lotteryTBL and resultsTBL:

mysql> SELECT * FROM test.lotteryTBL;
+----+------+------+------+------+------+------+-------------+
| ID | num1 | num2 | num3 | num4 | num5 | num6 | howmanyknew |
+----+------+------+------+------+------+------+-------------+
| 1 | 1 | 5 | 10 | 15 | 20 | 26 | 0 |
| 2 | 4 | 8 | 12 | 16 | 25 | 40 | 0 |
+----+------+------+------+------+------+------+-------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM test.resultsTBL;
Empty set (0.00 sec)
mysql> 

Inserting on resultsTBL:

mysql> INSERT INTO test.resultsTBL(id, num1, num2, num3, num4, num5, num6)VALUES('1', '1', '5', '4', '8', '25', '40');
Query OK, 1 row affected (0.00 sec)
mysql> 

RESULT:

mysql> SELECT * FROM test.lotteryTBL;
+----+------+------+------+------+------+------+-------------+
| ID | num1 | num2 | num3 | num4 | num5 | num6 | howmanyknew |
+----+------+------+------+------+------+------+-------------+
| 1 | 1 | 5 | 10 | 15 | 20 | 26 | 2 |
| 2 | 4 | 8 | 12 | 16 | 25 | 40 | 4 |
+----+------+------+------+------+------+------+-------------+
2 rows in set (0.00 sec)
mysql> 

If you look in the INSERT:

• There are 2 values matching for the ID = 1: 1,5.
• There are 4 values matching for the ID = 2: 4,8,25,40.

TRIGGER:

USE `test`;
DELIMITER $$
DROP TRIGGER IF EXISTS test.resultsTBL_AFTER_INSERT$$
USE `test`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `test`.`resultsTBL_AFTER_INSERT` AFTER INSERT ON `resultsTBL` FOR EACH ROW
BEGIN
 CALL test.sp_lottery(NEW.id,NEW.num1,NEW.num2,NEW.num3,NEW.num4,NEW.num5,NEW.num6);
END$$
DELIMITER ;

STORED PROCEDURE:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_lottery`(
IN eID INT(5),
IN eBLAH01 INT(5),
IN eBLAH02 INT(5),
IN eBLAH03 INT(5),
IN eBLAH04 INT(5),
IN eBLAH05 INT(5),
IN eBLAH06 INT(5))
BEGIN
 # [ lotteryTBL ]
 SET @ID=1;
 # [ ITERATE FOR EVERY ROW IN lotteryTBL ]
 WHILE @ID IS NOT NULL DO
 SET @NUM1=(SELECT num1 FROM test.lotteryTBL AS lt WHERE lt.ID=@ID LIMIT 0,1);
 SET @NUM2=(SELECT num2 FROM test.lotteryTBL AS lt WHERE lt.ID=@ID LIMIT 0,1);
 SET @NUM3=(SELECT num3 FROM test.lotteryTBL AS lt WHERE lt.ID=@ID LIMIT 0,1);
 SET @NUM4=(SELECT num4 FROM test.lotteryTBL AS lt WHERE lt.ID=@ID LIMIT 0,1);
 SET @NUM5=(SELECT num5 FROM test.lotteryTBL AS lt WHERE lt.ID=@ID LIMIT 0,1);
 SET @NUM6=(SELECT num6 FROM test.lotteryTBL AS lt WHERE lt.ID=@ID LIMIT 0,1);
 SET @howmanyknew=0;
 # [ NUM1 ]
 IF (eBLAH01=@NUM1 || eBLAH02=@NUM1 || eBLAH03=@NUM1 || eBLAH04=@NUM1 || eBLAH05=@NUM1 || eBLAH06=@NUM1 ) THEN
 SET @howmanyknew=@howmanyknew+1;
 END IF;
 # [ NUM2 ]
 IF (eBLAH01=@NUM2 || eBLAH02=@NUM2 || eBLAH03=@NUM2 || eBLAH04=@NUM2 || eBLAH05=@NUM2 || eBLAH06=@NUM2 ) THEN
 SET @howmanyknew=@howmanyknew+1;
 END IF;
 # [ NUM3 ]
 IF (eBLAH01=@NUM3 || eBLAH02=@NUM3 || eBLAH03=@NUM3 || eBLAH04=@NUM3 || eBLAH05=@NUM3 || eBLAH06=@NUM3 ) THEN
 SET @howmanyknew=@howmanyknew+1;
 END IF;
 # [ NUM4 ]
 IF (eBLAH01=@NUM4 || eBLAH02=@NUM4 || eBLAH03=@NUM4 || eBLAH04=@NUM4 || eBLAH05=@NUM4 || eBLAH06=@NUM4 ) THEN
 SET @howmanyknew=@howmanyknew+1;
 END IF;
 # [ NUM5 ]
 IF (eBLAH01=@NUM5 || eBLAH02=@NUM5 || eBLAH03=@NUM5 || eBLAH04=@NUM5 || eBLAH05=@NUM5 || eBLAH06=@NUM5 ) THEN
 SET @howmanyknew=@howmanyknew+1;
 END IF;
 # [ NUM6 ]
 IF (eBLAH01=@NUM6 || eBLAH02=@NUM6 || eBLAH03=@NUM6 || eBLAH04=@NUM6 || eBLAH05=@NUM6 || eBLAH06=@NUM6 ) THEN
 SET @howmanyknew=@howmanyknew+1;
 END IF;
 # [ UPDATE ]
 IF @howmanyknew>0 THEN
 UPDATE test.lotteryTBL SET howmanyknew=@howmanyknew WHERE ID=@ID;
 END IF;
 SET @ID=(SELECT ID FROM test.lotteryTBL AS lt WHERE lt.ID>@ID ORDER BY ID ASC LIMIT 0,1);
 END WHILE;
END

• oNare: So, did you close the other question?
• Who Cares: I did. BTW, something is wrong, I am trying to fix it. Is there any do-while loop in sql? Some loop that we put the condition next? not while-do, but do-while?