CODEWITHSUNDEEP
phpmysql
Mohan Wijesena
Mohan Wijesena

Reputation: 235

Querying mySQL database with dropdown values

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";
         }

Upvotes: 0

Views: 52

Answers (4)

TarangP
TarangP

Reputation: 2738

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";

Upvotes: 0

k32y
k32y

Reputation: 427

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";

Upvotes: 0

user4035
user4035

Reputation: 23719

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';

Upvotes: 1

Amit Merchant
Amit Merchant

Reputation: 1043

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";
}

Upvotes: 0

Related Questions