Clash Royale CLAN TAG#URR8PPP

mysql select to check lottery ticket combination

I spent the last night trying different mysql queries to simply check lottery combination using mysql/php but still not able to make it work fine! Google gives many attempts by users, especially here in stackoverflow, nothing found that can help all the way..

I have a table with all previous winner numbers, i simply want to check 5 main numbers and 2 extra numbers to point out any winning happened earlier for the given numbers

table name: win_archive (9 columns)
table structure:

id | main_b1 | main_b2 | main_b3 | main_b4 | main_b5 | extra_b1 | extra_b2 | win_date
1 . 22 | 10 | 5 | 59 | . 61 . | 1 | 9 . | 2/1/2011
2 . 43 | 23 | 11 | 76 | . 25 . | 13 | 9 . | 30/3/2015
3. ...


Tried to use mysql IN clause

SELECT id,
main_b1,
main_b2,
main_b3,
main_b4,
main_b5,
extra_b1,
extra_b2,
win_date
WHERE main_b1 IN ( 21, 44, 55, 22, 11, 1, 4 )


didn't work if there is more than 1 number then i can't check them, also we have the extra balls are different set than main balls, which means there maybe the same number in the combination but- like one in main balls set and one within the extra 2...

expecting to give 5+2 numbers to a php then by this mysql query to have something like:

Case A: Matching 2 main balls: 3 wins found in archive

date: line was: xx-xx-xx-xx-xx-y-y

date: line was: xx-xx-xx-xx-xx-y-y

date: line was: xx-xx-xx-xx-xx-y-y

Case B: Matching 2 main balls and 1 extra balls: 1 win found in archive

Case C: Matching 3 main balls and 2 extra balls: 1 win found

Is it really complicated? there are hundreds of sites online can check the lottery ticket combinations, thought the check/query will be all over..

Any hints will be highly appreciated...

2 Answers
2

This is a sample query that I think will achieve what you want. You just need to change the values in the IN expressions according to the result you want to search for:

IN

SELECT
main_b1, main_b2, main_b3, main_b4, main_b5, extra_b1, extra_b2,
((main_b1 IN (22, 23, 10, 11, 76)) +
(main_b2 IN (22, 23, 10, 11, 76)) +
(main_b3 IN (22, 23, 10, 11, 76)) +
(main_b4 IN (22, 23, 10, 11, 76)) +
(main_b5 IN (22, 23, 10, 11, 76))) AS main,
((extra_b1 IN (5, 9)) +
(extra_b2 IN (5, 9))) AS extra,
win_date
FROM win_archives
HAVING main >= 3 OR main = 2 AND extra > 0
ORDER BY main DESC, extra DESC, win_date DESC


I created a bit more sample data in this SQLFiddle:

INSERT INTO win_archives
(`id`, `main_b1`, `main_b2`, `main_b3`, `main_b4`, `main_b5`, `extra_b1`, `extra_b2`, `win_date`)
VALUES
(1, 22, 10, 5, 59, 61, 1, 9, '2011-01-02'),
(2, 43, 23, 11, 76, 25, 13, 9, '2015-03-30'),
(3, 22, 10, 5, 76, 61, 1, 4, '2014-06-02'),
(4, 43, 9, 11, 76, 25, 5, 9, '2012-08-07'),
(5, 22, 10, 5, 59, 61, 5, 12, '2016-12-02'),
(6, 22, 23, 11, 76, 10, 5, 6, '2017-07-19'),
(7, 22, 10, 5, 59, 61, 1, 9, '2018-09-02'),
(8, 43, 23, 11, 76, 22, 13, 8, '2005-04-11')
;


With this sample data the output is:

main_b1 main_b2 main_b3 main_b4 main_b5 extra_b1 extra_b2 main extra win_date
22 23 11 76 10 5 6 5 1 2017-07-19
43 23 11 76 22 13 8 4 0 2005-04-11
43 23 11 76 25 13 9 3 1 2015-03-30
22 10 5 76 61 1 4 3 0 2014-06-02
43 9 11 76 25 5 9 2 2 2012-08-07
22 10 5 59 61 1 9 2 1 2018-09-02
22 10 5 59 61 5 12 2 1 2016-12-02
22 10 5 59 61 1 9 2 1 2011-01-02


Not an answer. Too long for a comment...

A normalised schema might look something like this:

Draw

draw_id | date
1 | 2011-01-02
2 | 2015-03-30


Draw_detail

draw_id | ball_ord | ball_number
1 | 1 | 22
1 | 2 | 10
Etc...
2 | 1 | 43
2 | 2 | 23
....


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.