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

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:

Jacket (3)
Trousers (1)
Acme Apparel (2)
Fictive Fashion (2)
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.

Multi-page post: Next page »
Page: 1 2

Leave a Reply


* Copy This Password *

* Type Or Paste Password Here *


  1. 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.

  2. 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.