mysql select to check lottery ticket combination

The name of the pictureThe name of the pictureThe name of the pictureClash 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...





Do you want to find all combinations of previously matched balls (starting with 2 main and going upwards to 5 main and 2 extra)?
– Nick
Aug 10 at 3:58




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





Great Nick, this works, I relized something, if the lines are too much, I can't distinguish the numbers... is there a way to colorize (simple html color code) those matching numbers in the same line... just a thought to improve the output.. thanks again
– Mike
Aug 10 at 14:47





Never mind, i managed it through php output using html colors.. thanks Nick
– Mike
Aug 10 at 15:30





Hi Mike if this did answer your question I'd appreciate it if you could mark it accepted. Thanks
– Nick
Aug 10 at 20:58





I did.. actually didn't notice that, thought its only the vote :) thanks Nick
– Mike
Aug 11 at 0:12



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
....





Hi Strawberry, didn't understand what you mean but Nick above has nailed it actually, that is what I wanted, I just want to improve the outcome to colorize the matching numbers, perhaps in an elegant table and so.. thanks for your time.
– Mike
Aug 10 at 14:53





@Mike Yes, but normalisation is fundamental to efficient database design. If you're able to restructure your schema along the lines described above, the likelihood is that your queries will be much faster (and simpler) - especially on large data sets.
– Strawberry
Aug 10 at 16:55






Got your point & will try so, thanks.
– Mike
Aug 10 at 16:56







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.

Popular posts from this blog

Firebase Auth - with Email and Password - Check user already registered

Dynamically update html content plain JS

Creating a leaderboard in HTML/JS