CODEWITHSUNDEEP
phpsql
User007
User007

Reputation: 41

Multiple Filters That Work Together Or Independently

I have a page where you can filter users by their profile information.

I had 4 different filters that were "City", "Age Above", "Age Below", and "Gender".

I made it work this way, putting every possible combination as an if statement so that all the filters could work separately or in any combination:

if (isset($ageabove)
&& empty($agebelow)  
&& empty($gender)
&& empty($city)
)
{

$sql = mysqli_query($con, "select * from Users1 

WHERE age >= 1

");

}

This worked, but it was a lot of combinations and I did fear that this may be an inefficient way to do it.

Now I decided that I need to add 1 more filter, making it a total of 5 filters. This would increase the amount 'if' statements exponentially and I am wondering if there is a better way to accomplish this?

If I haven't been clear enough, please let me know.

Any help much appreciated!

Upvotes: 0

Views: 604

Answers (3)

Patrick Maciel
Patrick Maciel

Reputation: 4944

To make your filters more dynamic, register them in the database, like that:

You can create a model_filters table (in mysql for example):

drop table if exists model_filters;
create table model_filters (
  id int not null auto_increment primary key,
  name varchar(80) not null,
  model varchar(80) not null,
  condition varchar(40) not null,
  created datetime,
  modified datetime,
  active boolean not null default 1,
  index model_filters (name)
);

So, create some filters, for specific model:

INSERT INTO model_filters VALUES 
  ('Age Above'   , 'user' ,'age <= %filter'),
  ('Age Below'   , 'user' ,'age >= %filter'),
  ('City'        , 'user' ,'city ="%filter"'),
  ('Gender'      , 'user' ,'gender = "%filter"');

Then, get the filters based in your model:

SELECT id, name FROM model_filters WHERE model = 'user' AND active = 1

Iterate this values and generate a filters <select>:

<select name="filters" id="filters">
  <option value="1">Age Above</option>
  <option value="2">Age Below</option>
  <option value="3">City</option>
  <option value="4">Gender</option>
</select>

<input type="text" name="value" id="value">

And, you get this information, search for selected filter, and then execute your query

<?php 
// I'm using PDO
$statment = $pdo->prepare("SELECT * FROM model_filters WHERE id = :id");
$statment->bindValue(':id', $_POST['filters']);
$status = $statment->execute();
$result = null;
if ($status == true) {
  $result = $statment->fetchAll(PDO::FETCH_ASSOC);
}

if ($result == null) {
  // ERROR!
}

Now, organize the values

/*
  $result['condition'] is equal (for example): "age <= %filter"
  After replace is = "age <= :filter"

  Obs.: replace for whatever you want, for example: ":filtername, :value, :whatever"
*/

$condition = str_replace('%filter', ':filter', $result['condition']);

$statment = $pdo->prepare("SELECT * FROM Users1 WHERE $condition");
$statment->bindValue(':filter', $_POST['value']);
$status = $statment->execute();
$result = null;
if ($status == true) {
  $result = $statment->fetchAll(PDO::FETCH_ASSOC);
}

Get $result, sent for your view, and iterate the filtered users.

Done!

Upvotes: 0

Sammitch
Sammitch

Reputation: 32242

  1. Don't store age in a database. Unless the user specifically goes in and edits it they will be whatever age they signed up as forever. Store their birthdate and calculate age on the fly with:

    SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age

  2. I prefer to do this for dynamic conditions in SQL queries:

    $query = 'SELECT * FROM table';

$conditions = array();
$parameters = array();

if( isset($_GET['cond1']) ) {
   $parameters[] = $_GET['cond1'];
   $conditions = 'column1 = ?';
}
if( isset($_GET['cond2']) ) {
   $parameters[] = $_GET['cond2'];
   $conditions = 'column2 = ?';
}
// etcetera...

if( ! empty(conditions) ) {
  $query .= ' WHERE ' . implode(' AND ', $conditions);
  $sth = $db->prepare($query);
  $rs = $sth->(execute($parameters));
} else {
  $sth = $db->prepare($query);
  $rs = $sth->(execute());
}

if( ! $rs ) { /* error message */ }
// yadda yadda yadda

    Which will build you a query like:

    SELECT * FROM TABLE WHERE column1 = ? AND column2 = ? AND ... columnN = ?

    as well as placing all of your arguments into the array in the proper order.

I might be a little squiffy on the parameterization for MySQLi, though. I'm a PDO guy.

Upvotes: 1

Curtis Mattoon
Curtis Mattoon

Reputation: 4742

$query = "SELECT stuff FROM table WHERE foo=1 ";
if ($filter1) {
    $query .= "AND filter1 = $val";
}
if ($filter2) {
    $query .= "AND filter2 = $val";
}
// run query

Would something like that work? (if (isset($filter1) && !empty($filter1))..)

Upvotes: 1

Related Questions