Instructables

mysql matching?

i don't really know how to phrase the question.

i'm making a Lotto game in PHP.
i have numbers 1 - 100 grouped in lots of 10 (ie. 5-15-25-35-45-55-65-75-85-95)
and i want to match them against the "house" numbers (ie. 7-15-22-35-49-55-65-75-85-99) then count how many matches in each group and put that into a column (matches)
"USER" 5-15-25-35-45-55-65-75-85-95 vs "HOUSE" 7-15-22-35-49-55-65-75-85-99 = 6 matches.

then i can count how many matches to determine "payout"

i can do this in excel easily.
SQL is harder to do.

If you really want to do it in SQL, I would insert the values for both sets of numbers into tables, let's call them UserNumber and HouseNumber: 

create table UserNumber (
DrawingID integer auto_increment primary key
, Number integer
)

create table HouseNumber(
DrawingID integer auto_increment primary key
, Number integer
)

And then compare the data in the two columns for a particular DrawingID:

select count(user.DrawingID)
from UserNumber as user inner join HouseNumber as house
        on user.UserNumber = house.HouseNumber and user.DrawingID = house.DrawingID
where User.DrawingID = Your Drawing ID

If you're feeling froggy, you can actually consolidate those two tables into one and join the table on itself:

create table LottoDrawing(
DrawingID integer auto_increment primary key
, UserNumber integer
, HouseNumber integer)

select count(user.DrawingID)
from LottoDrawing as user inner join LottoDrawing as house
        on user.UserNumber = house.HouseNumber
where user.DrawingID = Your Drawing ID


The resulting value would then be inserted into your payout function (or table). This assumes that a number can only be picked once by the user, and will only show up once in the house numbers. Also assumed is that matches are not index specific, in other words user = [1, 2, 3] == house [2, 3, 1] (3 matches). If any of these assumptions are wrong, the code will need to change.

If you'd rather do it in PHP, check out the in_array(valuearray) function. This would be quicker because it doesn't demand inserting all of the values in the each of the sets of numbers in the database. If you want to keep track of all the numbers played by users and generated by the house for statistical or reporting purposes, I'd understand why you'd be aiming towards a SQL solution.

Let me know if this answers your question.

Happy coding!
furby (author)  paulcauchon1 year ago
thanks for the answer. i tried using your code (the first one)
this is what PHPMyadmin is giving me
//
 Error SQL query: Documentation
SELECT count( user.DrawingID )
FROM UserNumber AS user INNER JOIN HouseNumber AS house
ON user.UserNumber = house.HouseNumber AND user.DrawingID = house.DrawingID
WHERE User.DrawingID =1
MySQL said: Documentation #1054 - Unknown column 'User.DrawingID' in 'where clause'
\\

the tables were created using your script

i had to alter the numbers (ie. remove hyphen & space & shorten to 11 digits)

the reason i want to use SQL is that the Drawing is only going to be one per week & i want it to be as automated as possible (set & forget)
and like the real lottery it will be many to one
I believe you've upset MySQL by mixing your cases when referring to the table alias. By default, MySQL likes to see identifiers referenced in the same case throughout a single statement-- in other words: User <> user <> USER.

Try the code again, only this time use:

WHERE user.DrawingID = 1

Happy querying!
furby (author)  paulcauchon11 months ago
i finally (sorta) got it working i had to make some changes
/code

SELECT count( user.DrawingID )
FROM UserNumber AS user
INNER JOIN HouseNumber AS house ON user.Number = house.Number
AND user.DrawNum = house.DrawingID
WHERE house.DrawingID =1

/code
i had to add another column to the UserNumber Table "DrawNum"
it doesn't find any matches due to the number structure.
will this work with Text?
johnstanford11 months ago
MySQL databases can be used by mysql recovery tool

Apply http://www.mysql.recoverytoolbox.com