Properly Filter with PHP/MySQL and Select Box

Question

Hello all,

I currently have a large table in MySQL with several fields. I am currently attempting to allow the user to filter by each field, via Select Boxes.

I have 4 fields: School, Division and State.

I have no problem, using PHP, on how to filter based on which option the user selects in the select box.

For example, if these 3 are submitted:

<select id="school">
 <option value="13">John Brown School</option>
</select>

<select id="division">
 <option value="I">I</option>
</select>

<select id="state">
 <option value="NY">New York</option>
</select>

I would be able to query based on each option, like:

$school= $_POST['school'];
$division= $_POST['division'];
$state = $_POST['state'];

Query: SELECT * from table WHERE school=$school AND division=$division and state=$state

However, if I want to have an "All" option (in the select box), how would I implement this into the query or the WHERE clause, so that it does not filter that particular field?

Answer 1

You should simply make an option that you specifically look for. In fact, in this case it sounds like the best choice would be to make your select boxes have an "All" option, or just leave them blank. The blank choice can be your default. I would have it set up as:

<option value="">-- All schools --</option>
 etc.

Now, when setting up your query variables, you do this:

$whereClauses = array();
if (! empty($_POST['school'])) $whereClauses[] = 'school='.mysql_real_escape_string($_POST['school']);
if (! empty($_POST['division'])) $whereClauses[] ='division='.mysql_real_escape_string($_POST['division']);
if (! empty($_POST['state'])) $whereClauses[] = 'state='.mysql_real_escape_string($_POST['state']);

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

$query = "SELECT * FROM table ".$where;

Now if any select options get selected with the blank values (ie. use all), they won't be part of the WHERE clause.

You are open to SQL injection attacks if you don't escape your incoming $_POST data as well, so one thing you need to do is run mysql_real_escape_string() on your $_POST values, which I added in to the example. It sounds like you're already aware of that though.

Answer 2

Not accounting for sql injection, etc, here is the basic logic of one way to do it. Psudo-code, or course.

<select id="select1">
    <option value="">All</option>
    <option value="value1">Value1</option>
    ...
</select>



$sql = "select ... from ... where 1 = 1 ";

if ( !empty($_POST["select_1"]) ) {
    $sql .=" and field1 = select1value";
}

if ( !empty($_POST["select_2"]) ) {
    $sql .=" and field2 = select2value";
}

if ( !empty($_POST["select_one"]) ) {
    $sql .=" and field3 = select3value";
}

// Etc.

This way, with the 1=1, you don't have to worry about whether to append/prepend 'and' based on whether some or all of them are present. You select all (blank select value) by default, and filter based on the presence of each select.

Of course, you'll need to clean it up a bit, secure it, put values into variables, etc., but this is the basic logic.