Faceted navigation in PHP and MySQL Tutorial – part 1

Faceted navigation index tables

To perform the faceted search in MySQL we need to create two extra tables. One table contains a list of the product facets and the other contains facts about the products.

table: product_facets
+----+--------+
| id | name   |
+----+--------+
|  1 | type   |
|  2 | brand  |
|  3 | colour |
+----+--------+

table: product_facts
+------------+----------+------------+-----------------+
| product_id | facet_id | facet_name | value           |
+------------+----------+------------+-----------------+
|          1 |        1 | type       | jacket          |
|          1 |        2 | brand      | Acme Apparel    |
|          1 |        3 | colour     | black           |
|          2 |        1 | type       | jacket          |
|          2 |        2 | brand      | Acme Apparel    |
|          2 |        3 | colour     | blue            |
|          3 |        1 | type       | jacket          |
|          3 |        2 | brand      | Fictive Fashion |
|          3 |        3 | colour     | blue            |
|          4 |        1 | type       | trousers        |
|          4 |        2 | brand      | Fictive Fashion |
|          4 |        3 | colour     | orange          |
+------------+----------+------------+-----------------+

We used the following PHP function to automatically populate the facts table with data. You’ll need to make sure the product_facts table gets updated for each CRUD operation on the original dataset.

/**
 * Populates the product_facts table with data from the products table
 * @param object $mysql_connection the mysql connection object
 */
function populate_product_facts($mysql_connection) {
  //empty product_facts table
  mysqli_query($mysql_connection, "TRUNCATE product_facts");   

  //fetch all product data
  $result = mysqli_query($mysql_connection, "SELECT * FROM products");

  //loop through resultset rows
  while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
    $facet_id = 1;

    //loop through table columns
    foreach ($row as $key => $value) {
      //create facts for all product fields except 'id' and 'name'
      if ($key != "id" && $key != "name") {
        $sql = "INSERT INTO product_facts VALUES (" . $row['id'] . ",$facet_id,'$key','$value');";
        mysqli_query($mysql_connection, $sql);
        $facet_id++;
        echo "Added fact: (".$row['id'].", $facet_id, $key, $value) <br/>";
      }
    }
  }
}

Now that we have set up all the required tables we will look at how to query the tables in the next section.

Querying the data

Let’s say a user just opened the product browse page and hasn’t selected any filters yet. To retrieve the product data we can simply execute the query:

SELECT * FROM products;

This will return a list of all products.

To construct the faceted navigation HTML we need to query the product_facts table.

SELECT pf.facet_name, pf.value, count(*) AS c
FROM product_facts pf
GROUP BY pf.facet_id, pf.value;

Which produces the following result:

+------------+-----------------+---+
| facet_name | value           | c |
+------------+-----------------+---+
| type       | jacket          | 3 |
| type       | trousers        | 1 |
| brand      | Acme Apparel    | 2 |
| brand      | Fictive Fashion | 2 |
| colour     | black           | 1 |
| colour     | blue            | 2 |
| colour     | orange          | 1 |
+------------+-----------------+---+

As you can see this contains all the data required to output the faceted navigation (including counts).

Now let’s say that a user selects the colour blue so that only blue products are shown. This changes the product query to:

SELECT * FROM products
WHERE colour='blue';

Similarly the query to retrieve the facet data becomes:

SELECT pf.facet_name, pf.value, count(*) AS c
FROM product_facts pf 
JOIN products p ON pf.product_id = p.id 
WHERE p.colour='blue'
GROUP BY pf.facet_id, pf.value;

Which results in:

+------------+-----------------+---+
| facet_name | value           | c |
+------------+-----------------+---+
| type       | jacket          | 2 |
| brand      | Acme Apparel    | 1 |
| brand      | Fictive Fashion | 1 |
| colour     | blue            | 2 |
+------------+-----------------+---+

We can add an ORDER BY statement to sort the list of filters alphabetically:

SELECT pf.facet_name, pf.value, count(*) AS c
FROM product_facts pf 
JOIN products p ON pf.product_id = p.id 
GROUP BY pf.facet_id, pf.value
ORDER BY pf.facet_name, pf.value;

When the user selects multiple filters we simply add multiple conditions in the WHERE clause based on the selected filters.

In the next post I’ll explain how to create the interactive faceted navigation HTML interface based on the work we’ve done so far.

If you have any questions please leave a comment below!

Multi-page post:« Previous page
Page: 1 2

Leave a Reply

*

* Copy This Password *

* Type Or Paste Password Here *

14 comments

  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.

  3. burraak

    Why overload database with “product_facts” why we even need it ? can’t we directly access product table using some sort of query

    • 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?

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

  5. capri

    why do we need “product_facts” can’t we directly use product table ?

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

  7. Troy Francis

    Great tutorial. I’ve been looking for this for sometime now. Looking forward to part 2