Create multiple MySQL filters with PHP and HTML Select

Question

I'm trying to create a PHP script to filter data from a MySQL DB. Filters are selected with HTML Select tags.

The problem is, there are many filters, and a user may only select some of them. I'm quite confused about how to use PHP to dynamically adjust the MySQL select statement so that it only uses those filters selected by the user. That is, if the user select SchoolType and District. The query is something like this:

"SELECT * FROM mydatabase 
 WHERE SchoolType='SelectedSchoolType' 
   AND District='SelectedDistrict'"

And then if the user also select County in the filter, the SQL statement adjusts to:

"SELECT * FROM mydatabase 
 WHERE SchoolType='SelectedSchoolType' 
   AND District='SelectedDistrict' 
   AND County='SelectedCounty'"

How can I make the SQL statement adjust according to the filter selected?

How can I make the SQL statement adjust according to the filter selected? What selected filters Maybe you should also show us the form where the user selects these filters — RiggsFolly
– RiggsFolly, Commented Jun 28, 2016 at 21:04
Click "edit" below your question, paste your HTML and PHP codes, save your question. — Jose Manuel Abarca Rodríguez
– Jose Manuel Abarca Rodríguez, Commented Jun 28, 2016 at 21:12

TylerH · Accepted Answer · 2019-07-16 16:12:19Z

3

On your PHP page, you need to:

Check the select values (switch)
Construct your query from those selects (I used implode)
Run your query (as you normally would)

Example:

<?php
$sql = "Select * FROM mydatabase";
$conditions = array();
switch($_POST['select_1']){//you can have mutiple switches
    case "school":
        $conditions[] = "SchoolType = '".$_POST['select_1_text']."'";
        break;
    case "district":
        $conditions[] = "District = '".$_POST['select_1_text']."'";
        break;
    //etecetera
}
if(count($conditions)){
    $sql.= " WHERE ";
    $sql.=implode(" AND",$conditions);
}
//Run your query

Check this related question about dynamic queries

edited Jul 16, 2019 at 16:12

TylerH

21.3k84 gold badges84 silver badges121 bronze badges

answered Jun 28, 2016 at 21:18

M.M

2,3142 gold badges22 silver badges34 bronze badges

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

Comments

Dmitriy Urazov · Accepted Answer · 2016-06-28 21:19:57Z

0

So firstly you need to send filter type id and selected values to server side. For example you can use jquery (for 1 selected value):

$.ajax({
  method: "POST",
  url: "path_to_your_script",
  data: {
    type: $("your_element:selected").val(), 
    filter : $("your_element:selected").text()}
  });

Then on the server side you can dynamically create query for your database:

<?php
$input_filter = $_POST['filter'];
$my_mapped_type = get_mapped_type($_POST['type']); //for.ex. you store it in db
$base_query = "SELECT * FROM mydatabase";
if(isset($input_filter) && isset($my_mapped_type)){
  $base_query .= " where $my_mapped_type = $input_filter";
}

As result you'll have whole query in $base_query

answered Jun 28, 2016 at 21:19

Dmitriy Urazov

316 bronze badges

3 Comments

M.M Over a year ago

I don't think he was loking for an ajax or javascript answer, considering the question itself and it's tags

Dmitriy Urazov Over a year ago

if you won't use ajax or javascript so you need to decide how to send selected values by user to server side. you can use form submit )

M.M Over a year ago

I think that is implicit and does not seem like @Mishidoo 's problem, since he does not have difficulties in passing the values but rather in constructing a query dynamically

Collectives™ on Stack Overflow

Create multiple MySQL filters with PHP and HTML Select

2 Answers 2

Comments

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Linked

Related