Php mysql custom ecomm - product search, include category name?

Question

I am doing a custom ecommerce web app using php and mysql. I am stuck with an issue related to product search.

User enters a keyword to search product. The search query should look into product name & description, category name and manufacturer name it belongs to.

The table schema is as follows:

Products: id, name, description (and other fields) Manufacturers: id, name, description (and other fields) Category: id, name, description (and other fields) Product_Manu: id, prod_id, manu_id Product_Cat: id, prod_id, cat_id

Can you please help me with the SQL query? Thanks in advance.

Answer 1

Most people tend to achieve this using this approach:

When adding/modifying each products's data, you're concatenating all the relevant fields you would want to search in into one field ("search") then allow MySQL Full Text Search on this field.

When performing search, you execute something like this:

SELECT * FROM Products WHERE search LIKE '%$query%'

Answer 2

Aside from the straight forward approach suggested by @haim770, might I also suggest taking a look at search frameworks? Not only do they scale very well with changing search requirements, knowledgeable people of application design today consider the search function of web applications to be an application making or breaking matter. Looking for only the literal text inside the whole name and description might not be enough nowadays.

Search frameworks like Apache Solr or Elasticsearch can be incorporated into an existing architecture very easily, can be fed in multiple ways (HTTP POSTs, database indexing) and can be used to get additional features such as search suggestions via AJAX, even before posting the search.