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!
php / mysql complex (?) search question.
Moderator: General Moderators
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...