CODEWITHSUNDEEP
phpmysqlsql
tim-we
tim-we

Reputation: 1279

Searching by name through multiple fields

I have a table that, besides other information, contains names. I want to search for a name and get the field 'unique_id'.

The fields that could contain the name (or part of it) are:

f_name1, l_name1, f_name2, l_name2, prop_name

Now let's say the database contains this entry:

unqiue_id = 1, 
f_name1   = 'Barack',
l_name1   = 'Obama', 
f_name2   = 'Michelle', 
l_name2   = 'Obama', 
prop_name = 'White House',
...

and I have this string given in php:

$q = 'Barack Obama'; //or 'Michelle' or 'White House' or ...

What's the sql to get the 'unqiue_id' from that row?

SELECT unique_id FROM members WHERE (?)

One idea I had was to explode(' ', $q) and use LIKE '%...%' for everything and somehow count the number of conditions that are true and then ORDER BY that but I don't know if that's possible an if how to do it in SQL.

Edit1:

I came up with this solution thanks to 'PM 77-1's comment:

SELECT unique_id
FROM members
WHERE CONCAT( f_name1, l_name1, f_name2, l_name2, prop_name ) LIKE '%Barack%Obama%'

(replacing white-spaces with '%' in php)

However this only works if the input string is in the correct order...

Upvotes: 0

Views: 55

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You could try this:

select t.*
from table t
where $p like concat('%', f_name1, '%') or
      $p like concat('%', l_name1, '%') or
      $p like concat('%', f_name2, '%') or
      $p like concat('%', l_name2, '%') or
      $p like concat('%', prop_name, '%')
order by (($p like concat('%', f_name1, '%') ) +
          ($p like concat('%', l_name1, '%') ) +
          ($p like concat('%', f_name2, '%') ) +
          ($p like concat('%', l_name2, '%') ) +
          ($p like concat('%', prop_name, '%') ) desc;

This solves the problem as described in the question. But, you are possibly better off with a full text search. The place to start with learning about this is the documentation.

Upvotes: 1

Related Questions