How to allow multiple filters to be selected - pagination?

Question

Hey, I am still trying to allow multiple filters to be selected for my pagination script but not sure how to do it being very new to php and programing in general.

So in my pagination, when a user clicks the 'marketing' button(link) it queries the database just for the category that = marketing. The same goes for the other 2 filter buttons as seen in the script below. (automotive, sports).

The problem is, I want to be able to select multiple filters like only marketing and auomotive or automotive and sports, for example if I click the marketing filter and then the automotive, it would display the categories that equal marketing, and automotive.

I have no idea how to accomplish this, so I have come to the experts to help me out.

This is the script I am working on:

<h3>Filter results by:</h3>
<a href='pagi_test.php?category=marketing'>marketing</a>
<a href='pagi_test.php?category=automotive'>automotive</a>
<a href='pagi_test.php?category=sports'>sports</a>
<br />

<h3>Results:</h3>
<?php

//connecting to the database
$error = "Could not connect to the database";
mysql_connect('localhost','root','root') or die($error);
mysql_select_db('ajax_demo') or die($error);

//max displayed per page
$per_page = 3;

//get start variable
$start = $_GET['start'];

$category = mysql_real_escape_string($_GET['category']);
//count records
$record_count = mysql_num_rows(mysql_query("SELECT * FROM explore WHERE category='$category'"));

//count max pages
$max_pages = $record_count / $per_page; //may come out as decimal

if (!$start)
   $start = 0;

//display data
$get = mysql_query("SELECT * FROM explore WHERE category='$category' LIMIT $start, $per_page");
?>
<table width="800px">
<?php
while ($row = mysql_fetch_assoc($get))
{
 // get data
 $id = $row['id'];
 $site_name = $row['site_name'];
 $site_description = $row['site_description'];
?>

<tr>
<td><?php echo $id; ?></td>
<td><?php echo $site_name; ?></td>
<td><?php echo $site_description; ?></td>
</tr>
<?php
}

//setup prev and next variables
$prev = $start - $per_page;
$next = $start + $per_page;

//show prev button
if (!($start<=0))
       echo "<a href='pagi_test.php?category=$category&start=$prev'>Prev</a> ";

//show page numbers

//set variable for first page
$i=1;

for ($x=0;$x<$record_count;$x=$x+$per_page)
{
 if ($start!=$x)
    echo " <a href='pagi_test.php?category=$category&start=$x'>$i</a> ";
 else
    echo " <a href='pagi_test.php?category=$category&start=$x'><b>$i</b></a> ";
 $i++;
}

//show next button
if (!($start>=$record_count-$per_page))
       echo " <a href='pagi_test.php?category=$category&start=$next'>Next</a>";

?>

Any help on this would be great. Thank you.

-- EDIT --

If anyone has a better method of doing a pagination system with multiple filters than the one above, please let me know.

Answer 1

While selecting second filter u can add the category ex:

At first ur variable $category has

$category="Marketing";

When user filter with another category suppose automotive then add it to $category with a delimeter,Now

$category="Marketing:Automotive";

when u access thru GET use explode:

$cat=explode(":",$_GET['category']);

and write your condition

    $condition="category=category[0]";
    for($i=1; $i<sizeof($cat); $i++)
    {
       $condition="AND category=$cat[$i]";
    }

$where="WHERE $condition";

use $where in ur query, like

$record_count = mysql_num_rows(mysql_query("SELECT * FROM explore $where"));

Answer 2

I see two separate issues

1. How to allow the user to select more than one category to filter by
2. How to propagate those choices to the pagination links

Each for which I have a solution!

How to allow the user to select more than one category to filter by

A form is going to be the most direct approach.

<h3>Filter results by:</h3>
<form action="pagi_test.php" method="GET">
  <input type="checkbox" name="category[]" value="marketing" id="cat_marketing"/>
  <label for="cat_marketing">Marketing</label>
  <br/>

  <input type="checkbox" name="category[]" value="automotive" id="cat_automotive"/>
  <label for="cat_automotive">Automotive</label>
  <br/>

  <input type="checkbox" name="category[]" value="sports" id="cat_sports"/>
  <label for="cat_sports">Marketing</label>
  <br/>

  <input type="submit" value="Filter!" />
</form>

Now, $_GET['category'] will be an array of every category that was selected.

$categories = $_GET['category'];
$inClause   = "'" . implode( "','", array_map( 'mysql_real_escape_string', $categories ) ) . "'";

//count records
$record_count = mysql_num_rows(
  mysql_query( "SELECT * FROM explore WHERE category IN($inClause)" )
);

Of course, you'd probably want to add a check here to make sure $categories isn't empty before you execute the query.

You'll need to modify the actual selection query as well

//display data
$get = mysql_query("SELECT * FROM explore WHERE category IN($inClause) LIMIT $start, $per_page");

Bingo! Now that part is done!

How to propagate those choices to the pagination links

Since we already have an array of the categories selected stored in $categories, this will be trivial using http_build_query().

//setup prev and next variables
$prev = $start - $per_page;
$next = $start + $per_page;

// Get the categories in an HTML-safe array
$requestVars = array_map( 'htmlspecialchars', $categories );

//show prev button
if (!($start<=0))
{
  $requestVars['start'] = $prev;
  echo '<a href="pagi_test.php?' . http_build_query( $requestVars ) . '">Prev</a> ';
}

//show page numbers

//set variable for first page
$i=1;

for ( $x = 0; $x < $record_count; $x = $x + $per_page )
{
  $requestVars['start'] = $x;
  if ( $start != $x )
  {
    echo '<a href="pagi_test.php?' . http_build_query( $requestVars ) . '">'. $i .'</a> ';
  } else {
    echo '<a href="pagi_test.php?' . http_build_query( $requestVars ) . '"><b>'. $i .'</b></a> ';
  }
  $i++;
}

//show next button
if (!($start>=$record_count-$per_page))
{
  $requestVars['start'] = $next;
  echo '<a href="pagi_test.php?' . http_build_query( $requestVars ) . '">Next</a> ';
}

Now, there are still holes in this implementation.

• Since the <form> is printed to the page before the rest of the logic, there's no way to pre-select the checkboxes that represent the current filter choices. Which you could definitely change.
• Also, you have categories as literal strings in the PHP script - they would really be better in their own table in the database.
• The way you retrieve a count for the entire data set is inefficient - it sends the entire data set over the wire to PHP, which then is responsible for determining the record count. It's much better to run a separate query that uses SELECT count(*) ... instead.