[thelist] Complicated search with PHP & MySQL

> I am trying to do a pretty complicated search.  I have a table
> with 6 columns of numbers.  I am trying to find all those that
> match 4, 5, or 6 numbers.  Problem is that any of the numbers
> can be in any of the columns.

hi chris

most interesting problem

sounds like you are testing lottery numbers  ;o)

i'm a little unsure of what you mean by "6 columns of numbers ...that match
4, 5, or 6 numbers" but i will assume you are going to bring in 6 test
numbers, let's call them test1 through test6, and you want at least 4 of
them to match one of the 6 columns pick1 through pick6

the sql will get awfully complex unless you can apply some "tricks" and i
think i gots a couple for you

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 )

please let me know if this helps

i am not able to test this for you as i don't have access to mysql

i am really curious to see how this will perform....

_____________
rudy limeback
http://r937.com/
http://evolt.org/