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