How to filter mysql query results based on a set of constraining parameters? [closed]

Your answer gives the impression that you might need a query builder class in your project.

It'll help you achieve exactly what you intend and keep your code clean too.

If you're using framework such as Yii, it already provides this functionality, for standalone query builder classes, please try these:

1. http://www.phpclasses.org/package/2813-PHP-Dynamically-build-SQL-queries.html
2. https://code.google.com/p/php-query-builder/

Disclaimer, I have not tried or tested any of these.

Here's a code example from the second option in the list:

<?php
require_once './QueryBuilder.php';

$qb = QueryBuilder::create()
    ->select(array('column1', 'alias' => 'column2'))
    ->from('mytable as mt')
    ->join('another_table at', 'on', 'at.id = mt.fk_at_id')
    ->where('mt.status = 1')
    ->group('mt.group_field')
    ->order('mt.date_field desc')
    ->limit(15); 
// when printed, gives the following query:
// SELECT column1,column2 as alias
//  FROM mytable as mt
//  JOIN another_table as at on at.id = mt.fk_at_id
//  WHERE mt.status = 1
//  GROUP BY mt.group_field
//  ORDER BY mt.date_field as desc
//  LIMIT 15

Transforming Selections to SQL

Basically you need a way to transform the checkbox selections into a where statement. A very basic example of this concept is described here: https://stackoverflow.com/a/14761616/1688441

Quoting (simplest example):

<?php
$location= $_GET['location'];
$language= $_GET['language'];
$name= $_GET['name'];

if($location!=""){
    $where[] = " `location` = '".mysql_real_escape_string($location)."'";
}
if($language!=""){
    $where[] = " `language` = '".mysql_real_escape_string($language)."'";
}
if($name!=""){
    $where[] = " `name` = '".mysql_real_escape_string($name)."'";
}
$where_clause = implode(' OR ', $where);
//same code for the other 2 options

$sql_json = "SELECT * FROM myTable WHERE $where_clause ORDER BY id DESC";
?>

If you want to make a more dynamic solution

You need to do two things:

• Create an associative array where key is the field and the value is what to assign to the where statement. You could also use another data structure depending on your needs.
• Write a function that takes the associative array and created the where statement to append to your SQL statement.