2

I have table who have a city and description columns and my web page have a single input for search string ..

if i type into my input furniture production i get company name, so its fine, but if i want do something like this furniture production london string with city, i do not get any result.

How can i fix this query to work with city column or other column?

SELECT slug, title FROM catalog WHERE city || description LIKE '%$keyword%
TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
SkySonny
  • 105
  • 2
  • 10
  • 5
    Look up Fulltext Search. – juergen d Aug 02 '16 at 10:48
  • 1
    what all search terms are acceptable? what about `london furniture production`, `furniture london`. Any query satisfying these multiple search filter would have poor performance. – ughai Aug 02 '16 at 10:48
  • I think user can type in both way, so it will be good if query can work in both way. – SkySonny Aug 02 '16 at 10:52
  • Possible duplicate of [Using OR in LIKE Query in MySQL to compare multiple fields](http://stackoverflow.com/questions/11106888/using-or-in-like-query-in-mysql-to-compare-multiple-fields) – Sumit patel Aug 02 '16 at 11:03

1 Answers1

5

As stated in the comments, performing a search with such "fluid" criteria will be really expensive on the DB.

Anyway, if this is the path you want to take, here's my attempt:

SELECT slug, title FROM catalog 
WHERE CONCAT(city, description) LIKE '$keyword'
OR CONCAT(description, city) LIKE '$keyword'

To make this work, you may wanna replace all spaces with % and maybe put leading and trailing % in the code.

I reckon the code which performs the query is PHP, so this could do it:

$keyword = "%" . str_replace(" ", "%", $keyword) . "%";

Like I said, this will be very very heavy on the DB, specially if the number of rows is important. Try to optimize the columns for full-text search and maybe make sure the search criteria can't be too short in the input form.

Hope this helps!

GigiSan
  • 1,170
  • 2
  • 19
  • 30
  • It works! It was exactly what i need. Thanks! I think it will be not so much rows in the db, about ~3k, but i will think about full-text search. – SkySonny Aug 02 '16 at 11:31