MySQL: Look for the same string in multiple columns – This article will take you through the common SQL errors that you might encounter while working with php, mysql, sql. The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue. SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem.
SQL Problem :
I am trying to make a search-page-like function. I want to make a query to look for “query” in “ColumnA” and “ColumnB” and “ColumnC” and “ColumnD”. And select the rows which has the word/phrase “query” in any of those columns.
This appears to work:
SELECT * FROM projects
WHERE
category LIKE '%query%' OR
name LIKE '%query%' OR
description LIKE '%query%'OR
keywords LIKE '%query%' OR
'type' LIKE '%query%'
ORDER BY name ASC
But it is lengthy. Is there any easier or more efficient way of doing this?
Solution :
Simple workaround:
SELECT *
FROM projects
WHERE
CONCAT(category,name,description,keywords,type) LIKE '%query%'
ORDER BY name ASC;
You can add separators between columns if needed:
SELECT *
FROM projects
WHERE
CONCAT(category,"|",name,"|",description,"|",keywords,"|",type) LIKE '%query%'
ORDER BY name ASC;
You can also use a fulltext search (you need to create a fulltext index as described here: How do FULLTEXT INDEXES on multiple columns work?)
SELECT *, MATCH (category,name,description,keywords,type) AGAINST ('query') AS score FROM projects WHERE MATCH (category,name,description,keywords,type) AGAINST ('query');
But it is lengthy.
I think this is not problem. query can be generated client side.
Is there any easier or more efficient way of doing this?
use Sphinx or Solr with MySQL. It is not difficult and super fast. Here is Sphinx example. http://www.ibm.com/developerworks/library/os-php-sphinxsearch/
and I think CONCAT is not efficient than yours, there is cost to concatenate columns (some columns can be long text). CONCAT(name, description) LIKE '%query%' reads name and description and concat two values after that LIKE is applied. that means all columns READ twice, while your query can be completed just first column is matched. All condition is “OR”, so category column matches %query% that row does not need to be compared to ‘name’ column.
FYI
just FYI, below query can check, which column has more ‘query’
SELECT name,
(LENGTH(category) - LENGTH(REPLACE(category, 'query', ''))) / LENGTH('query') as category_match_cnt,
(LENGTH(name) - LENGTH(REPLACE(name, 'query', ''))) / LENGTH('query') as name_match_cnt,
(LENGTH(description) - LENGTH(REPLACE(description, 'query', ''))) / LENGTH('query') as desc_match_cnt,
Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code.