Faceted navigation in PHP and MySQL Tutorial – part 1
In this tutorial we are going to build a simple PHP/MySQL faceted navigation from scratch. In case you don’t know what faceted navigation is (or faceted search, layered navigation), it’s the nice filtering mechanism you see on most e-commerce sites in some form or another next to the product listing (see image).
Magento faceted navigation example (zumiez.com)
With each newly selected filter the resultset is narrowed down accordingly which allows user to quickly find the items (products, classifieds, articles, etc.) that meet their criteria. This search mechanism is most useful when a dataset needs to be filtered based on multiple properties.
Unfortunately MySQL does not support faceted search out-of-the-box. There are dedicated external search applications such as SOLR or ElasticSearch which index the facets automatically for filtering. However not everyone has the knowledge or resources required to run such an application. Luckily we can achieve similar results while maintaining good performance using MySQL and PHP by setting up an index table and making some clever table joins.
The example data
You can download an SQL export of all the example data here.
Let me first introduce the dataset we’re going to work our magic on for this example:
table: products +----+-----------------------+----------+-----------------+--------+ | id | name | type | brand | colour | +----+-----------------------+----------+-----------------+--------+ | 1 | Black Jacket by Acme | jacket | Acme Apparel | black | | 2 | Blue Jacket by Acme | jacket | Acme Apparel | blue | | 3 | Blue Jacket by FF | jacket | Fictive Fashion | blue | | 4 | Orange Trousers by FF | trousers | Fictive Fashion | orange | +----+-----------------------+----------+-----------------+--------+
The faceted navigation in this example without any of the filters selected will look something like this:
Type:
Jacket (3)
Trousers (1)
Brand:
Acme Apparel (2)
Fictive Fashion (2)
Colour:
Black (1)
Blue (2)
Orange (1)
The user can filter the product overview simply clicking on the filter links which makes it a form of navigation to the user (hence the term faceted navigation).
Note the item counts next to each facet value, these will be updated to reflect the current resultset whenever a filter is added or removed.
Massimo
Thank you, very interesting post. When you say “SELECT * FROM products” you mean “product_facts” I guess. What is the load on the DB if the user starts to click here and there?
Now I’m curious to read the second part!
Massimo
Hi, sorry I just realised that the products table was on page 1.
MightyWebdev
🙂 Thanks, I hope to get around to writing part 2 soon.
Ivan Jaros
How would you get the counts only for items/products that are ‘Fictive Fashion’ brand?
Ivan Jaros
Oh I see. You are using the facets only for the stats and not for the actual retrieval of products from DB.
burraak
Why overload database with “product_facts” why we even need it ? can’t we directly access product table using some sort of query
burraak
Where is part 2 ?
MightyWebdev
I haven’t gotten around to it yet unfortunately. I’ll let you know when it’s done (but don’t wait for it please..).
Anu Web
Hello Mighty,
When you are posting 2nd article?
capri
Very nice article unfortunatly its incomplete I can understand that writing a tutorial is not an easy task but if possible please atleast upload the code for second part and later whenever you get time then explain it , I hope you will consider it
regards
capri
why do we need “product_facts” can’t we directly use product table ?
Robert
Nice post, I would suggest using a view to generate the product_facts structure instead of duplicating data, that way you don’t need to worry about populate it after every crud operation.
I like of the approach of having a physical table updated in every CRUD operation from the performance point of view but if performance is the issue I would consider a solution like Solr which is clearly outside the scope of this tutorial.
Troy Francis
Great tutorial. I’ve been looking for this for sometime now. Looking forward to part 2
bobby
Where is part 2??????????