Searching multiple values through multiple columns

Question

I'm looking to create a search as such https://i.sstatic.net/cuYZk.png

Basically it'll look through my database looking for the values that people input.

There will be different values for each combo box (up to 5 or so). I need to somehow determine what values the user uses and with those values I need to create a search accordingly. I tried building a string builder with get requests however that was too insecure.

The issue here is there is one value which is "Any" meaning i will need it to retrieve any kind of value.

I would need to somehow "SELECT FROM DATABASE WHERE ITEM = ANY" if that makes sense.

Thanks!

The Alpha · Accepted Answer · 2013-09-06 08:09:55Z

1

You have 5 combos/selects and each one contains a All so to determine the All you can set a value for All, for example :

<option value="none">All</option>

So, you can check on the server whether it's none submitted or not by something like this

$where = array();
$where['country'] = (isset($_GET['country']) && $_GET['country'] != 'none') ? filter_var($_GET['country'], FILTER_SANITIZE_STRING) : null;
$where['state'] = (isset($_GET['state']) && $_GET['state'] != 'none') ? filter_var($_GET['state'], FILTER_SANITIZE_STRING) : null;
// Rest of them ....

$where_str = ' where ';
foreach($where as $field => $value) {
    if(!is_null($value)) {
        if(strlen($where_str) > 6) $where_str .= ' and ';
        $where_str .= "$field = '$value'";
    }
}

So far, you'll get a string like where country = 'Bangladesh' and holiday_cost = '1000' or only where (depending on user selection), so you can check if anything you have in where clause like

// At first your basic/default query
$sql = "select * from tablename";

// Now, if $where_str contains more than where, then add it
if(strlen($where_str) > 6) {
    $sql .= $where;
}

Now, your sql query is ready and you can run your query from here. Don't forget to filter inputs and you can use filter-var for that.

edited Sep 6, 2013 at 8:09

answered Sep 6, 2013 at 6:02

The Alpha

147k30 gold badges294 silver badges313 bronze badges

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

2 Comments

Oliver Kucharzewski Over a year ago

$where['country'] = isset($_GET['country'] && $_GET['country'] != 'none') ? filter_var($_GET['country'], FILTER_SANITIZE_STRING) : null;   $where['state'] = isset($_GET['state'] && $_GET['state'] != 'none') ? filter_var($_GET['state'], FILTER_SANITIZE_STRING) : null;

No idea why.. Edited with Dreamweaver

The Alpha Over a year ago

My mistake, it should be

$where['country'] = isset($_GET['country']) && $_GET['country'] != 'none' ? filter_var($_GET['country'], FILTER_SANITIZE_STRING) : null; $where['state'] = isset($_GET['state'] && $_GET['state'] != 'none') ? filter_var($_GET['state'], FILTER_SANITIZE_STRING) : null;

.

user2417483 · Accepted Answer · 2013-09-06 05:13:34Z

0

You need to build the sql statement. For example:

$whr = "";
$srch1 = ( isset($_POST['suburb']) ) ? $_POST['suburb'] : "";
//-- repeat for each field
if( $srch1 != "" ) $whr .= " suburb like '%'.%srch1."%'";

$sql ="SELECT ... FROM ... ";
if( $whr != "" ) $sql .= " WHERE ".$whr;

answered Sep 6, 2013 at 5:13

user2417483

Comments

Carsten Massmann · Accepted Answer · 2013-09-06 05:46:01Z

Since SQL differentiates between data types and even if a value was given for a particular field the any value would always need to be composed of two OR-connected parts:

field ="a generous data range that includes any posible existing value ..."
field IS NULL

For numeric types 1. gets a bit complicated and would have to be something like

field IS BETWEEN MIN(<numeric type>) AND MAX(<numeric type>)

For string types it is a little bit simpler:

field LIKE '%'

But in any case these search criteria will never be as simple as you would like them to be and they will cost unnecessary search effort for the SQL server!

So, I guess, your first approach of building a SQL search string is still the method to be applied here. You wil have to put some effort into that but I think it can be made safe nonetheless.

In similar projects I have used a pattern generator in which I put together a search criterion for each field. The pattern generator needs to know the field's name, type and posibly table name and of course the user input. Then, dependent on whether anything was actually input it churns out either

nothing (null) --> no comparison!
a valid search condition like field LIKE '<processed user input> or field =orfield BETWEEN user min value AND user max value

Since the user input will always be processed according to the data type of the given field (including of course a final call of something like mysqli_real_escape_string()) you can make the whole thing safe.

After you processed all the fields with it's user input you need to put all generated (not the null) criteria together again (surround them with (, ), collect them in an array ($arr) and finally join(' AND ',$arr) them) and if necessary, determine which tables (assuming there will be JOINs) are involved. These JOINS can now also be applied conditionally depending on certain crieteria being given or not. This of course really depends on the deeper logic of your table setup.

Collectives™ on Stack Overflow

Searching multiple values through multiple columns

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related