PHP navigation with filters

Question

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?

MrJ · Accepted Answer · 2011-11-28 19:02:55Z

2

probably want something like below (if I understand your question correctly:

SELECT * FROM tablename WHERE manufacturer='A' AND company='B' AND category='C'

If you're using PHP, you could use it to put the current value in for A, B, and C - but remember to sanitize these values

Edit

For example, with PHP...

<?php
  $manufacturer = mysql_real_escape_string($_GET['manufacturer']);
  $company = mysql_real_escape_string($_GET['company']);
  $category = mysql_real_escape_string($_GET['category']);

  $query = "SELECT * FROM tablename WHERE manufacturer='".$manufacturer."' AND company='".$company."' AND category='".$category."'";

  // then simply run the query....
?>

Edit 2

You can change AND to OR when needed be

<?php
  $query = "SELECT * FROM tablename";
  $mixed_query = "";

  if(isset($_GET['manufacturer']) && !empty($_GET['manufacturer'])){
    $mixed_query .= ($mixed_query !== "") ? " AND " : " WHERE ";
    $mixed_query .= "manufacturer='".mysql_real_escape_string($_GET['manufacturer'])."'";
  }
  if(isset($_GET['company']) && !empty($_GET['company'])){
    $mixed_query .= ($mixed_query !== "") ? " AND " : " WHERE ";
    $mixed_query .= "company='".mysql_real_escape_string($_GET['company'])."'";
  }
  if(isset($_GET['category']) && !empty($_GET['category'])){
    $mixed_query .= ($mixed_query !== "") ? " AND " : " WHERE ";
    $mixed_query .= "category='".mysql_real_escape_string($_GET['category'])."'";
  }

  // then add to query
  $query .= $mixed_query;

  // then simply run the query....
?>

edited Nov 28, 2011 at 19:02

answered Nov 28, 2011 at 18:49

MrJ

1,9382 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kendepelchin Over a year ago

Yes, that's what i want, but not all the values are filled in. So that users can filter on either manufacturer OR company OR ... So i will have to check each entry if it exists in the url and then get the products. I thought that this was a bit to much waste of time (what if I have 20 getvalues on which a user can filter?)

Gustav Bertram · Accepted Answer · 2011-11-28 19:12:00Z

1

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);
}

answered Nov 28, 2011 at 19:12

Gustav Bertram

15k3 gold badges46 silver badges68 bronze badges

2 Comments

kendepelchin Over a year ago

this worked great. Thanks alot! It seems to easy but I couldn't figure it out. using the implode function didn't came to my mind. thanks

Gustav Bertram Over a year ago

I'm glad it helped. PHP is good at arrays. You should use arrays whenever you can.

Collectives™ on Stack Overflow

PHP navigation with filters

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related