Dynamic Query based on mulitple GET variables (Refine Search Form)

Question

So im trying to find a script that can start me off with creating an 'refine search' panel which will be a form, hopefully something close to what Ebay have got at the moment.

Whilst I know this is going to be a form and using the GET method, I cant find a script that can parse a url for multiple params and update the query all dynamically.

Im not sure quite how to do this without a million and one if statements. (about 10 or so possible GET variables but could be a lot more soon.)

So i need a simple:

• foreach GET add WHERE field=GET[variable]

any ready made scripts you know of?

Answer 1

I know next to nothing about PHP, but there's some discussion about retrieving and parsing the query string here (scroll down a bit):

http://us2.php.net/manual/en/reserved.variables.get.php

You can probably lift some useful code out of there to extract all of your GET parameters and use them to construct your WHERE clause. I'm not sure how your data model is set up, so I can't offer any detailed advice on that -- besides the usual advice of sanitizing all your inputs so that you don't end up with a Bobby Tables on your hands.

Answer 2

I don't know of some canned script to do this.

But it's quite easy to write it yourself.

<?PHP
    $valid_fields = array('field1','field2',...'fieldN');

    $where = "WHERE 1=1 ";

    foreach($valid_fields as $fname){
       if (! empty($_GET[$fname])){
          $where .= " AND $fname='" . mysql_real_escape_string($_GET[$fname]) ."'";
       }
    }

This code simply loops over your expected input variables, and if they're passed as a parameter in $_GET, ads an AND to the WHERE clause.

Then you just tack the $where onto the end of your query (whatever it happens to be):

$sql = 'SELECT * FROM some_table ' . $where;

Edit for example code to hide column names:

<?PHP
//keys are parameter names in _GET, values are database column names.
$fieldmap = array(
'fname'=>'first_name',
'lname'=>'last_name'
);

$where = '1=1 ';

foreach($fieldmap as $get_name => $col_name){
  if (! empty($_GET[$get_name])){
    $where .= " AND $col_name = '" . mysql_real_escape_string($_GET[$get_name]) . "'";
  }
}
?>

Answer 3

You mean something like this?

<?php
$query = "SELECT [WHATEVER YOU'RE SELECTING] FROM your_table WHERE ";
$length = count($_GET);
$count = 0;
foreach($_GET as $key => $value){
    if($count <= $length){
        $query .= "$key='{$value}' OR ";
    }else{
        $query .= "$key='{$value}'";
    }

    $count++;
}
?>

That will loop through your $_GET array, adding key='value' type strings to the query string. Then once you're done recursing through the array, you can do whatever you want with the complete query string.

As per usual, use standard sanitizing functions as you collect your inputs (strip_slashes, mysql_real_escape_string, etc.)