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%')

Answer 1

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)