Querying mySQL database with dropdown values

Question

I've got below snippet where $filter_xx values are extracted from a dropdown basis user choice.

I'm trying to query the mySQL database with what the user chose to query the database with via dropdown selection.

You will see that there are 4 $filter_xx variables and how many of them are set in a given instance is completely random.

The issue is when I use && in the query it checks if all four parameters are true and then throws and output. (Well I know && is suppose to work that way!). I tried replacing all && operators with || and had no luck.

How do I search the database with only options selected by the user?

    if(isset($filter_brand) || isset($filter_year) || isset($filter_month) || isset($filter_status)) 
         {
          $query = "SELECT * FROM targets WHERE brand='$filter_brand' && startyear='$filter_year' && startmonth='$filter_month' && status='$filter_status' ORDER BY createdon DESC";
          } else {
          $query = "SELECT * FROM targets ORDER BY createdon DESC";
         }

Do you quote the filter variables before putting them into SQL? — user4035
– user4035, Commented Feb 5, 2018 at 9:56
Check each one individually and only append it to the query if it's got a value — ADyson
– ADyson, Commented Feb 5, 2018 at 9:57

user4035 · Accepted Answer · 2018-02-05 10:23:15Z

1

When you have several values that must work in a similar manner, use an array together with loop. I am supposing, you are using mysqli, change quoting for PDO if needed.

$mysqli = new mysqli("localhost", "user", "pass", "test");
//...

//SQL attr name => name of POST parameter
$filter = array('brand' => 'brand', 'startyear' => 'year', 
                'startmonth' => 'month', 'status' => 'status');

//here we'll store SQL conditions
$sql_filter = array();

foreach($filter as $key => $value)
{
    if (isset($_POST[$value]))
    {
        //use your library function to quote the variable before using it in SQL
        $sql_filter[] = $key . '="'. $mysqli->escape_string($_POST[$value]) . '"';
    }
}

$query = "SELECT * FROM targets ";

if(isset($sql_filter[0]))
{
    $query .= 'WHERE ' . implode(' AND ', $sql_filter) . ' ';
}

$query .= 'ORDER BY createdon DESC';

edited Feb 5, 2018 at 10:23

answered Feb 5, 2018 at 10:10

user4035

24k11 gold badges69 silver badges101 bronze badges

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

4 Comments

Mohan Wijesena Over a year ago

Looks a solid piece however does NO querying the database at all. (I'm saying this as absolutely no output and no errors shown. Funny of course!) Do you see anything requires changing?

ADyson Over a year ago

@MohanWijesena check the final string it produces is valid SQL before sending to the DB, and check it makes sense in terms of what you expected it to do, and check there are actually rows in the DB which match the query filters. Also we don't know from your code, but are you checking for mysql errors when you run your queries from PHP?

Mohan Wijesena Over a year ago

Thanks @ADyson Yes I do sanitize and handles errors as appropriate in my local work environment.

user4035 Over a year ago

@MohanWijesena Show us the value of $query you get. What query do you expect to get according to parameters?

TarangP · Accepted Answer · 2018-02-05 11:27:37Z

0

Try By This

$join = "";
//TAKE ONE BLANK VARIBLE THAT JOIN IF VALUE IS SET

if(isset($filter_brand)){
    //IF VALUE ISSET THAN IT ADDED TO QUERY 
    $join .= " AND brand='$filter_brand'";
}

if(isset($filter_year){
    $join .= " AND startyear='$filter_year'";
}

$query = "SELECT * FROM targets WHERE id != '' $join ORDER BY createdon DESC";

edited Feb 5, 2018 at 11:27

answered Feb 5, 2018 at 9:58

TarangP

2,7385 gold badges24 silver badges42 bronze badges

9 Comments

Mohan Wijesena Over a year ago

Thanks @TarangP, Do you see the way you append $join = .= is right? I'm seeing an error which says .= is unexpected.

TarangP Over a year ago

its not because .= its because i missed semicolon. php.net/manual/en/language.operators.string.php

Mohan Wijesena Over a year ago

Where do you suggest the missed semicolon should go on your snippet?

ADyson Over a year ago

@TarangP sure it's not the $join = .= as well?

ADyson Over a year ago

@MohanWijesena like I said above already, $join = .= was also a problem. And since that was earlier in the code, you'd see that error first.

|

Amit Merchant · Accepted Answer · 2018-02-05 10:06:07Z

0

You can do something like this:

$query = 'SELECT * FROM targets';

$flag = 0;
if(isset($filter_brand) ) 
{
    $query = "SELECT * FROM targets WHERE brand='$filter_brand'";
    $flag = 1;
}

if(isset($filter_year)) {
    if($flag==1)
        $query .= " &&";
    $query .= " startyear='$filter_year'";
    $flag = 1;
}

if(isset($filter_month)) {
    if($flag==1)
        $query .= " &&";
    $query = " startmonth='$filter_month'";
    $flag = 1;
}

if(isset($filter_status)){
    if($flag==1)
        $query .= " &&";
    $query = " status='$filter_status'";
    $flag = 1;
}

if($flag == 1){
    $query .= " ORDER BY createdon DESC";
} else {
    $query = "SELECT * FROM targets ORDER BY createdon DESC";
}

answered Feb 5, 2018 at 10:06

Amit Merchant

1,0436 silver badges21 bronze badges

Comments

k32y · Accepted Answer · 2018-02-05 10:24:02Z

0

Try this:

$query = "SELECT * FROM targets WHERE 1 ";
$query = isset($filter_brand) ? $query . " AND brand = '".$filter_brand."'" : $query;
$query = isset($filter_year) ? $query . " AND startyear = '".$filter_year."'" : $query;
$query = isset($filter_month) ? $query . " AND startmonth = '".$filter_month."'" : $query;
$query = isset($filter_status) ? $query . " AND status = '".$filter_status."'" : $query;
$query .= " ORDER BY createdon DESC";

edited Feb 5, 2018 at 10:24

answered Feb 5, 2018 at 10:04

k32y

4277 silver badges13 bronze badges

Collectives™ on Stack Overflow

Querying mySQL database with dropdown values

4 Answers 4

4 Comments

9 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

9 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related