php / mysql complex (?) search question.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
herrin
Forum Newbie
Posts: 3
Joined: Fri Jul 11, 2003 11:37 am

php / mysql complex (?) search question.

Post 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!
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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...
herrin
Forum Newbie
Posts: 3
Joined: Fri Jul 11, 2003 11:37 am

Post by herrin »

Very interesting, i'll give those a better look. Thanks for responding!
Post Reply