searching multiple columns with one or more keywords using sql

Question

I have one search box that I would like to have search 6 columns in my schools database when an input is made. So far the search box searches the name field only and returns a match for exact or partial inputs.

I would like to search for a specific city and have all results show up from the name AND city columns (instead of just the name column) and so on.

Say I wanted to search with a zip code, I would like the listings to be all schools in that zip code. And finally if I input 2 words (e.g. penn Philadelphia) I would like all penn schools to show that are in the name column AND city column only. (not just all the penns in the name or every school in Philadelphia) and so on. These may be an elementary questions on the matter but I've been searching for days with no success. Maybe better use of wildcards and the "AND" "OR" clauses would benefit me.

Can someone help me structure a sql query to accomplish this?

this is what I have so far:

SELECT * FROM schools 
WHERE (name='%name%' OR address='%name%' OR city='%name%'OR zip='%name%')

you may use multiple approaches: for example to have "penn" and "philadelphia" you can do: `SELECT * FROM schools WHERE name='penn' AND city = 'philadelphia';` you can combine a few conditions together. Another approach: `SELECT * FROM school s1, school s2 JOIN ON s1.name = s2.name WHERE s1.name = 'penn' AND s1.city = 'philadelphia';` here you can also use multiple joins don't go wild with it but having a good index helps this latter robust — Hamed, Jul 03 '15 at 17:40
There is also another approach that you can use a few nested SELECTs (not recommended), as you do: `SELECT * FROM schools WHERE ( SELECT * FROM schools WHERE name = 'penn') AS s1 AND city = 'philadelphia';`. Don't go too deep on this as it would get nasty. Choose what fits you best — Hamed, Jul 03 '15 at 17:45
@RyanVincent well it's not really a proper answer more like a few tips — Hamed, Jul 03 '15 at 17:46
`=` and `%` do not combine like I think you intend. See also http://stackoverflow.com/questions/1127088/mysql-like-in — bishop, Jul 03 '15 at 18:05
Does this answer your question? [Checking if a string is found in one of multiple columns in mySQL](https://stackoverflow.com/questions/1804469/checking-if-a-string-is-found-in-one-of-multiple-columns-in-mysql) — mickmackusa, Sep 13 '21 at 22:57

Hitesh Mundra · Answer 1 · 2015-07-03T20:39:12.333

There are few ways to do that- The very basic strategy is to match input with our table columns by the query like as you mentioned -

1. SELECT * FROM table WHERE (name='%name%' or zip='%name%' or city='%name%');

2. SELECT * FROM table WHERE LOCATE(name, GROUP_CONCAT(name,city,zip)) > 0;

3. 
SELECT * FROM table WHERE name like '%name%' 
          UNION 
SELECT * FROM table WHERE name like '%name%' 
          UNION
SELECT * FROM table WHERE name like '%name%';

but suppose the case where input box have the string- "varun bharti" but actual name in database is "varun bal bharti" So when you search you will missed the record. for that case you should break the string by space in to array elements and use these queries for elements or either you can replace the space in name column and match.

set @var=REPLACE ('varun bharti', ' ', '%');    
SELECT * FROM table WHERE name like concat('%',@var,'%') or 
         zip like concat('%',@var,'%') or 
         city like concat('%',@var,'%');

You can also use regualar expressions for that. For example input string the day boss get

Hitesh> select * from test;
+--------------------+
| name               |
+--------------------+
| i am the boss      |
| You will get soon  |
| Happy birthday bro |
| the beautiful girl |
| oyee its sunday    |
+--------------------+
5 rows in set (0.00 sec)

Hitesh> set @var=CONCAT('.*',REPLACE('the day boss get',' ','.*|.*'),'.*');
Query OK, 0 rows affected (0.00 sec)

Hitesh> select @var;
+----------------------------------+
| @var                             |
+----------------------------------+
| .*the.*|.*day.*|.*boss.*|.*get.* |
+----------------------------------+
1 row in set (0.00 sec)

Hitesh> select * from test where name  REGEXP @var;
+--------------------+
| name               |
+--------------------+
| i am the boss      |
| You will get soon  |
| Happy birthday bro |
| the beautiful girl |
| oyee its sunday    |
+--------------------+
5 rows in set (0.00 sec)

searching multiple columns with one or more keywords using sql

1 Answers1