1

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?

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

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
  • So, did you close the other question? – oNare Jul 21 '15 at 20:14
  • 1
    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? – Who Cares Jul 21 '15 at 20:24
  • ok, I found it repeat-until :) thanks again. – Who Cares Jul 21 '15 at 20:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.