kendepelchin kendepelchin - 2 months ago 20
MySQL Question

PHP navigation with filters

I am working out a faceted navigation (I think that's the right expression...)

So I have a lot of categories and manufacturers on which a user can filter.

I came to the point where I have to get the results from the filters from my database. What would the fastest way to create these queries? I have 3 get values that I can filter on (manufacturer/company/category) so that would mean i would write a query for when manufacturer & company is an active filter and for category and company etc... I see how much work this is and I wonder if there is a short way to do this?

Answer

The simplest solution would probably be one where you build the query dynamically:

// GET SANITIZED $manufacturer $company $category

// Initialize the array
$facets = array();

if (isset($manufacturer))
{
  $facets[] = "manufacturer = '$manufacturer'";
}

if (isset($company))
{
  $facets[] = "company = '$company'";
}

if (isset($category))
{
  $facets[] = "category = '$category'";
}

$query = "SELECT * FROM table";

if (count($facets) > 0)
{
  $query .= " WHERE" . implode(" AND ", $facets);
}

Your query would only filter on those facets that are set.

To make it slightly more general:

// GET SANITIZED $manufacturer $company $category

// Initialize the array
$facets["manufacturer"] = $manufacturer;
$facets["company"] = $company;
$facets["category"] = $category;
// ADD MORE AS NECESSARY

foreach($facets as $key=>$value)
{
  if ($value != '')
  {
    $where[] = "$key = '$value'";
  }
}

$query = "SELECT * FROM table";

if (count($where) > 0)
{
  $query .= " WHERE" . implode(" AND ", $where);
}