Have table with lotto numbers, need HELP pls...

Hi new to the forums. Been looking to find a solution for about a week now and thought I would try here to find the answer.

Have a table with fields Ball1,Ball2,Ball3,Ball4,Ball5,Ball6,BonusBall

They are all populated with random numbers between 1 to 49 and have 25 records in the table.

I am trying to create a select query that will order the winning results from some external values which a person picks... They will make there own pick of Ball1 to Ball6 and BonusBall selecting numbers between 1 to 49.

So these values are pass to the query string.

I want to order by the first results haven 7 wining numbers and then 6 down to 1 and then continue with results that did not have any numbers

Just like a lotto hehehe.... Winner at the top with 7 number and all below with combination below 7 like 6,5,4,3,2,1 and no numbers matching...

Hope you understand...

 

select * from tbl order by match() descending;
where match() is a function that returns the number of matches, perhaps designed along the lines:

total=0
if ball1 in (val1, val2, ..., val7) then total=total+1;
if ball2 in (val1, val2, ..., val7) then total=total+1;
...
if bonusball in (val1, val2, ..., val7) then total=total+1;
return total;

where val1..val7 are the numbers the user picks.

Are you sure you have the correct bonus rules? In the UK lottery you pick six numbers, not seven as in your spec, and the jackpot goes to someone who picks Ball1..Ball6; the next highest prize goes to someone with five matches and the bonus, then to five, then four, then three.

 

Thank you for your reply. This sounds good.. I will need to learn a little about functions in MySQL, but be good learning and a much quicker way to deal with the data as its server side It will not be a UK lottery game or the like. I just need a way to sort data in the database in a different way.. HEHEHE...

 

Hi xpi0t0s, I tried what you said. Took a while to work things out but you can see the query string and the function below I created. I am running on a Windows 2003 server with MySQL 5.1.34. So had to change it a little to make it work and not break.. LOL...But it still does not order the results with the record with all 7 balls at the top and then and record with only 6 balls below and so on... I can see how the code works and it really makes sence! It checks all the balls in the coloums and if found increment total. If total = 7 then top. So total will be between 7 to 0 and Order By sorts it all.. Strange why it not work.. The numbers 43,25,16,48,49,34,36 has got a record with these numbers. Maybe your trained eye will spot something. For now I'll keep at it and let you know if I get it..


QueryString="select * from lottosites ORDER BY ballmatch(43,25,16,48,49,34,36) DESC"

------------------------------------

CREATE DEFINER=`user`@`localhost` FUNCTION `ballmatch`(pBall1 INT, pBall2 INT, pBall3 INT, pBall4 INT, pBall5 INT, pBall6 INT, pBonusBall INT) RETURNS int(11)
BEGIN
DECLARE total int(11);
SET total=0;
if @Ball1 in (pBall1, pBall2, pBall3, pBall4, pBall5, pBall6, pBonusBall) then SET total=total + 1; end if;
if @Ball2 in (pBall1, pBall2, pBall3, pBall4, pBall5, pBall6, pBonusBall) then SET total=total + 1; end if;
if @Ball3 in (pBall1, pBall2, pBall3, pBall4, pBall5, pBall6, pBonusBall) then SET total=total + 1; end if;
if @Ball4 in (pBall1, pBall2, pBall3, pBall4, pBall5, pBall6, pBonusBall) then SET total=total + 1; end if;
if @Ball5 in (pBall1, pBall2, pBall3, pBall4, pBall5, pBall6, pBonusBall) then SET total=total + 1; end if;
if @Ball6 in (pBall1, pBall2, pBall3, pBall4, pBall5, pBall6, pBonusBall) then SET total=total + 1; end if;
if @BonusBall in (pBall1, pBall2, pBall3, pBall4, pBall5, pBall6, pBonusBall) then SET total=total + 1; end if;
RETURN total;
END

 

I have been here for hours and hours trying to work this out and have no more ideas where to take it... The closest I have got is as you can see below: But stuck with limit due to not being able to feed it a value....

Code:

CREATE DEFINER=`xxx` FUNCTION `ballmatch`(pBall1 INT, pBall2 INT, pBall3 INT, pBall4 INT, pBall5 INT, pBall6 INT, pBonusBall INT) RETURNS int(11)
BEGIN
DECLARE xBall1 int(11);
DECLARE xBall2 int(11);
DECLARE xBall3 int(11);
DECLARE xBall4 int(11);
DECLARE xBall5 int(11);
DECLARE xBall6 int(11);
DECLARE xBonusBall int(11);
DECLARE BallCount int(11);
SET BallCount=0;

SET xBall1 = (SELECT Ball1 FROM lottosites LIMIT 1 OFFSET 0);
SET xBall2 = (SELECT Ball2 FROM lottosites LIMIT 1 OFFSET 0);
SET xBall3 = (SELECT Ball3 FROM lottosites LIMIT 1 OFFSET 0);
SET xBall4 = (SELECT Ball4 FROM lottosites LIMIT 1 OFFSET 0);
SET xBall5 = (SELECT Ball5 FROM lottosites LIMIT 1 OFFSET 0);
SET xBall6 = (SELECT Ball6 FROM lottosites LIMIT 1 OFFSET 0);
SET xBonusBall = (SELECT BonusBall FROM lottosites LIMIT 1 OFFSET 0);

if xBall1 in (pBall1, pBall2, pBall3, pBall4, pBall5, pBall6, pBonusBall) then SET BallCount=BallCount+1; end if;
if xBall2 in (pBall1, pBall2, pBall3, pBall4, pBall5, pBall6, pBonusBall) then SET BallCount=BallCount+1; end if;
if xBall3 in (pBall1, pBall2, pBall3, pBall4, pBall5, pBall6, pBonusBall) then SET BallCount=BallCount+1; end if;
if xBall4 in (pBall1, pBall2, pBall3, pBall4, pBall5, pBall6, pBonusBall) then SET BallCount=BallCount+1; end if;
if xBall5 in (pBall1, pBall2, pBall3, pBall4, pBall5, pBall6, pBonusBall) then SET BallCount=BallCount+1; end if;
if xBall6 in (pBall1, pBall2, pBall3, pBall4, pBall5, pBall6, pBonusBall) then SET BallCount=BallCount+1; end if;
if xBonusBall in (pBall1, pBall2, pBall3, pBall4, pBall5, pBall6, pBonusBall) then SET BallCount=BallCount+1; end if;

RETURN BallCount;
END