CODEWITHSUNDEEP
phpmysqlfull-text-search
Mohit Singh
Mohit Singh

Reputation: 142

How to search in mutiple fields with one text field in mysql?

I have multiple tables like make, model and year etc and using joins. I would like to search from one text field. Like user will type "Audi A1 2015 Red Variant", and he will get results on behalf of this "Audi A1 2015 Red Variant" so what is the correct sql should i implement, your suggestions will be appreciated? 

SELECT
  make.make_name,
  model.model_name
FROM cars
  INNER JOIN make
ON make.make_id = cars.fk_make_id
  INNER JOIN model
ON model.model_id = cars.fk_model_id
WHERE cars.fk_make_id = 6
AND (make.make_name LIKE '%Audi A1%'
      OR model.model_name LIKE '%Audi A1%')

Above sql returning 0 results.

Upvotes: 0

Views: 48

Answers (1)

kennasoft
kennasoft

Reputation: 1593

Achieving such is not a trivial task, as you need to be able to determine what token in the user's input should be compared to what field in what table. There are probably full-text search engine solutions out there like elasticsearch, that already do what you want to achieve.

Elasticsearch is a search engine based on Lucene. It provides a distributed, multitenant-capable full-text search engine with an HTTP web interface and schema-free JSON documents. Elasticsearch is developed in Java and is released as open source under the terms of the Apache License. Elasticsearch is the most popular enterprise search engine followed by Apache Solr, also based on Lucene

-- Wikipedia

If you think elasticsearch is a steep learning curve for you, I suggest you simplify by breaking your search form up into individual fields and save yourself some headache.

Upvotes: 1

Related Questions