Damien Damien - 3 years ago 113
MySQL Question

Search on multiple columns a serie of value in mysql

In my database, every user can have 5 tags, which are int number from 1 to 37.

If an user has only one tag for example, the database shows that :

tag1 : 36
tag2 : 0
tag3 : 0
tag4 : 0
tag5 : 0


On y "search user" page, you can choose from 1 to 5 tags (the input name is skills1 to skills5), and the page must shows the profil accordingly.

For now, it works if you choose only one tag, with this request :

WHERE tag1 REGEXP '$skill1|$skill2|$skill3|$skill4|$skill5'


But i can't figure how to make it work for 1 to 5 tags.

My goal is to have a request who seems :

WHERE (tag1 or tag2 or tag3 or tag4 or tag5) REGEXP '$skill1|$skill2|$skill3|$skill4|$skill5'


And is there a way to ponderate the results, to have first the users who have for example 4 tags of your search, then 3 tags, etc.

Is there a simple way to do that ?
Thanks !

Answer Source

You have a poor data structure. You should have a junction table with one row per entity and per tag.

You can do what you want as:

where tag1 in ($skill1, $skill2, $skill3, $skill4, $skill5) or
      tag2 in ($skill1, $skill2, $skill3, $skill4, $skill5) or
      tag3 in ($skill1, $skill2, $skill3, $skill4, $skill5) or
      tag4 in ($skill1, $skill2, $skill3, $skill4, $skill5) or
      tag5 in ($skill1, $skill2, $skill3, $skill4, $skill5)
order by ( (tag1 in ($skill1, $skill2, $skill3, $skill4, $skill5)) + 
           (tag2 in ($skill1, $skill2, $skill3, $skill4, $skill5)) + 
           (tag3 in ($skill1, $skill2, $skill3, $skill4, $skill5)) + 
           (tag4 in ($skill1, $skill2, $skill3, $skill4, $skill5)) + 
           (tag5 in ($skill1, $skill2, $skill3, $skill4, $skill5))
          ) desc

MySQL treats a boolean in a numeric context as a value of 1 for true and 0 for false.

Note: You can do the same thing with regexp if you want the user input to allow regular expression pattern matches.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download