i have a database with 6 columns/fields.
Each col/field will contain a number between 1 to 49.
for each member in my database.
I want to create a form/script that I will be able to place in the 6 lotto balls plus 1 bonus ball that have been drawn and then submit these to the database to see which members have any 4,5,6 or 5 plus bonus ball within their selected 6 numbers.
I know this will be a complex php script and have found an article on the web that contained this smaall piece of script which might partialy solve some of the problem. Has anyone any idea of how to create the complete script.
let's start with the sql for just one number, say test1
select foo from yourtable
where 0 = test1-pick1 * test1-pick2
* test1-pick3 * test1-pick4
* test1-pick5 * test1-pick6
the result of all those muliplications is that you end up with a number,
and this number will be equal to zero whenever test1 is equal to at least
one of the six numbers (actually one or more, but if they are lottery
numbers, they'll all be different) -- as long as one of the multiplicands
is zero, the answer will be zero
so if test1 is equal to at least one of the 6 columns, the where clause is
satisfied
can we test six numbers that way? yes, but it's not quite what you want
select foo from yourtable
where 0 = test1-pick1 * test1-pick2
* test1-pick3 * test1-pick4
* test1-pick5 * test1-pick6
or 0 = test2-pick1 * test2-pick2
* test2-pick3 * test2-pick4
* test2-pick5 * test2-pick6
or 0 = test3-pick1 * test3-pick2
* test3-pick3 * test3-pick4
* test3-pick5 * test3-pick6
or 0 = test4-pick1 * test4-pick2
* test4-pick3 * test4-pick4
* test4-pick5 * test4-pick6
or 0 = test5-pick1 * test5-pick2
* test5-pick3 * test5-pick4
* test5-pick5 * test5-pick6
or 0 = test6-pick1 * test6-pick2
* test6-pick3 * test6-pick4
* test6-pick5 * test6-pick6
for this sql sentence, the where clause is satisfied when *at least one* of
the 6 test numbers is equal to one of the 6 table columns
but you wanted at least 4, not at least 1
and this time you can't multiply them all together, because all it takes is
one zero to make the answer come out to zero
now there is a way to do this with similar sql, using some more arithmetic,
but there is also a very nice function in mysql that will do the job -- the
logical NOT function, which returns 1 if the argument is zero, and returns
0 if the argument is not zero
so all we gotta do is apply a logical NOT to all six multiplications, add
them up, and the answer has to be 4 or more...
select foo from yourtable
where 4 <=
NOT ( test1-pick1 * test1-pick2
* test1-pick3 * test1-pick4
* test1-pick5 * test1-pick6 )
+ NOT ( test2-pick1 * test2-pick2
* test2-pick3 * test2-pick4
* test2-pick5 * test2-pick6 )
+ NOT ( test3-pick1 * test3-pick2
* test3-pick3 * test3-pick4
* test3-pick5 * test3-pick6 )
+ NOT ( test4-pick1 * test4-pick2
* test4-pick3 * test4-pick4
* test4-pick5 * test4-pick6 )
+ NOT ( test5-pick1 * test5-pick2
* test5-pick3 * test5-pick4
* test5-pick5 * test5-pick6 )
+ NOT ( test6-pick1 * test6-pick2
* test6-pick3 * test6-pick4
* test6-pick5 * test6-pick6 )
Each col/field will contain a number between 1 to 49.
for each member in my database.
I want to create a form/script that I will be able to place in the 6 lotto balls plus 1 bonus ball that have been drawn and then submit these to the database to see which members have any 4,5,6 or 5 plus bonus ball within their selected 6 numbers.
I know this will be a complex php script and have found an article on the web that contained this smaall piece of script which might partialy solve some of the problem. Has anyone any idea of how to create the complete script.
let's start with the sql for just one number, say test1
select foo from yourtable
where 0 = test1-pick1 * test1-pick2
* test1-pick3 * test1-pick4
* test1-pick5 * test1-pick6
the result of all those muliplications is that you end up with a number,
and this number will be equal to zero whenever test1 is equal to at least
one of the six numbers (actually one or more, but if they are lottery
numbers, they'll all be different) -- as long as one of the multiplicands
is zero, the answer will be zero
so if test1 is equal to at least one of the 6 columns, the where clause is
satisfied
can we test six numbers that way? yes, but it's not quite what you want
select foo from yourtable
where 0 = test1-pick1 * test1-pick2
* test1-pick3 * test1-pick4
* test1-pick5 * test1-pick6
or 0 = test2-pick1 * test2-pick2
* test2-pick3 * test2-pick4
* test2-pick5 * test2-pick6
or 0 = test3-pick1 * test3-pick2
* test3-pick3 * test3-pick4
* test3-pick5 * test3-pick6
or 0 = test4-pick1 * test4-pick2
* test4-pick3 * test4-pick4
* test4-pick5 * test4-pick6
or 0 = test5-pick1 * test5-pick2
* test5-pick3 * test5-pick4
* test5-pick5 * test5-pick6
or 0 = test6-pick1 * test6-pick2
* test6-pick3 * test6-pick4
* test6-pick5 * test6-pick6
for this sql sentence, the where clause is satisfied when *at least one* of
the 6 test numbers is equal to one of the 6 table columns
but you wanted at least 4, not at least 1
and this time you can't multiply them all together, because all it takes is
one zero to make the answer come out to zero
now there is a way to do this with similar sql, using some more arithmetic,
but there is also a very nice function in mysql that will do the job -- the
logical NOT function, which returns 1 if the argument is zero, and returns
0 if the argument is not zero
so all we gotta do is apply a logical NOT to all six multiplications, add
them up, and the answer has to be 4 or more...
select foo from yourtable
where 4 <=
NOT ( test1-pick1 * test1-pick2
* test1-pick3 * test1-pick4
* test1-pick5 * test1-pick6 )
+ NOT ( test2-pick1 * test2-pick2
* test2-pick3 * test2-pick4
* test2-pick5 * test2-pick6 )
+ NOT ( test3-pick1 * test3-pick2
* test3-pick3 * test3-pick4
* test3-pick5 * test3-pick6 )
+ NOT ( test4-pick1 * test4-pick2
* test4-pick3 * test4-pick4
* test4-pick5 * test4-pick6 )
+ NOT ( test5-pick1 * test5-pick2
* test5-pick3 * test5-pick4
* test5-pick5 * test5-pick6 )
+ NOT ( test6-pick1 * test6-pick2
* test6-pick3 * test6-pick4
* test6-pick5 * test6-pick6 )