Page 1 of 1

php / mysql complex (?) search question.

Posted: Wed Feb 25, 2004 4:27 pm
by herrin
I have a database with multiple number fields per row, contents are two digit integers, they can be in any order. (can be more number fields per DB row.)

database rows example:
id num1 num2 num3 num4 num5
1 01 23 14 78 59
2 11 15 98 21 66
3 59 78 14 23 01
4 22 33 44 55 67

etc.

I'd like to search through the database and match against 5 (or however many) numbers that I provide.

Input:
23 78 59 14 01

output:
id num1 num2 num3 num4 num5
1 01 23 14 78 59
3 59 78 14 23 01

the database numbers and the input numbers can be in any order and still need to match the row. Trying to do this with php/mysql.

Can anyone provide some ideas on how to do this? I'm a bit stuck.

Thanks!

Posted: Thu Feb 26, 2004 4:17 pm
by Weirdan
You can try to use FIND_IN_SET and CONCAT_WS MYSQL functions to do this (and generate query automatically in PHP). Just construct something like

Code: Select all

.... WHERE 
find_in_set($pnum1,concat_ws(',',num1,num2,num3,num4,num5)) and 
find_in_set($pnum2,concat_ws(',',num1,num2,num3,num4,num5)) and 
so on...

Posted: Fri Feb 27, 2004 11:45 am
by herrin
Very interesting, i'll give those a better look. Thanks for responding!