PHP-SQL - populate a dynamic filter list

Question

I am trying to :

• Display products based on a location selection
• loop through products and return column names that = 1 (true)
• Create a products filter drop-down containing these column names that represent product features

At the moment, the filter dropdown contains by default all columns, in some scenarios ( based on the selected location ) this is okay on the other hand, if a location has only two products out of the 20 it is annoying to display in the filter drop-down features that are irrelevant and that will return no-result...

This is a brief example of what I am trying to say ;

Database Table structure

+------+------------+-------------+----------------+-------------+-------------+ | SKU | HDMI | 3D | Android | Ethernet | location | +------+------------+-------------+----------------+-------------+-------------+ |TV1X | 0 | 0 | 0 | 0 |all | |TV5X | 0 | 0 | 0 | 0 |fr uk usa | |TV3Z | 1 | 0 | 0 | 1 |usa | |TVH3 | 1 | 1 | 1 | 1 |mex | |TVH1 | 1 | 1 | 1 | 1 |fr es uk | +------+------------+-------------+----------------+-------------+-------------+

Features columns are of type bit

Product display

Let's say $location = "%".'usa'."%"

$queryString = "SELECT * FROM `products` WHERE `location` LIKE :location" ;
$statement = $dbh->prepare($queryString)
$statement->bindParam(':location',  $location, PDO::PARAM_STR);
$statement->execute();
$statement->setFetchMode(PDO::FETCH_ASSOC);
$result = $statement->fetchAll();

The query above will return TV5X & TV3Zwhich are related to USA, these products features that are 'TRUE' are HDMI + Ethernet only where the remaining two columns I do not wish to display in the filter dropdown. This example might be more relevant when we many products/features and locations...

Default Filter List

<li >
  <button  data-filter=".HDMI" >HDMI</button>
</li>
<li >
  <button  data-filter=".3D" >3D</button>
</li>
<li >
  <button  data-filter=".Android" >Android OS</button>
</li>
<li >
  <button  data-filter=".Ethernet" >Ethernet</button>
</li>

The default filter list is relevant to some countries but not USA

Dynamic Filter List

Based on the retrieved products (two in this case), query the db and find which columns for these products have TRUE as value.

<?php  foreach( $feature as $feat) : ?>

 <li >
    <button  data-filter=".<?php print  //Col_Name   ;?>" ><?php print  ect..    ;?></button>
 </li>

<?php endforeach;  ?>

I apologize for the long post, I am trying to explain the best way I can.

My question is how do build a query for this purpose ?

Answer 1

You could do this in one additional query by selecting the sum of each column and then checking that the returned value is > 0:

SELECT SUM(`HDMI`) AS `HDMI`,
       SUM(`3D`) AS `3D`,
       SUM(`Android`) AS `Android`,
       SUM(`Ethernet`) AS `Ethernet`
FROM `products`
WHERE `location` LIKE :location

Run this query after your first query, and the code to output the filter list simply becomes this:

foreach ($result as $row) {
    foreach ($row as $column => $numProducts) {
        if ($numProducts > 0) { // i.e., SUM($column) is > 0
            $html .= "<li>
                <button data-filter=\".{$column}\">{$column} ({$numProducts})</button>
            </li>";
        }
    }
}

This also has the added bonus of giving you the number of products which have each feature. :)