CODEWITHSUNDEEP
phpmysqlsql
Citizen SP
Citizen SP

Reputation: 1411

Search across multiple columns

I'm using this Mysql query:

WHERE fname LIKE '%{$query}%' OR lname LIKE '%{$query}%' OR zipcode LIKE '%{$query}%' OR city LIKE '%{$query}%'

Let's say I have the following info in my database:

When I search for 'New York', I get the expected result (bill gates), But when I search for "bill new york' I get no results.

What is the correct query to search across multiple columns?

Upvotes: 2

Views: 173

Answers (3)

Captain Payalytic
Captain Payalytic

Reputation: 1071

You either need to parse the query into separate words and dynamically construct a looooong WHERE clause that searches each field for each word separately, ORing them together.

OR

You use FULLTEXT indexing.

I would suggest the latter.

Upvotes: 0

neildt
neildt

Reputation: 5352

When using LIKE in your statement against a column you are saying compare all of "bill new york" against City "New York" which will fail because the phrase "bill" was in the query and not in the column.

In this instance you are better of using MySQL's FULLTEXT searching like below;

SELECT FieldName, 
MATCH (FieldName) AGAINST (+"Bill New York" IN BOOLEAN MODE) AS Relevance
FROM MyTable
WHERE MATCH (FieldName) AGAINST (+"Bill New York" IN BOOLEAN MODE) 
HAVING Relevance > 0
ORDER BY Relevance DESC

If you can provide your table structures I can expand on the answer.

Upvotes: 3

Matt Indeedhat Holmes
Matt Indeedhat Holmes

Reputation: 725

the problem you are having is that LIKE is basically just case insesative version of =

there are two ways you can go about what you are trying to do, the firsat would be to explode you r $query on saces and use a seires of LIKE's for each work found, this is messy and i wouldnt advise it.

the seccond more preferable option is to use a full text search MATCH() AGAINST() it is far simpler and makes for much cleaner code http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html <- this is a good place to start

Upvotes: 0

Related Questions