CODEWITHSUNDEEP
phpmysql
Rana.Asif
Rana.Asif

Reputation: 453

Mysql select query issue where id in ()

I have filters on my site. where i have multiple checkbox to show products from different site. if user select 2 checkboxes my query become

Select * from new_deals where provider_id IN (2,8);

its running fine.

But when user not select any checkboxes it becomes 

Select * from new_deals where provider_id IN ();

and give error.I was thinking it should return empty results.

can any body help me i did not wanna remove this code where provider_id IN ();

so there must be a solution if any checkbox is not checked we handle this IN();

so need to know what i need to put in brackets if any checkbox is not checked.i.e IN ();

You people are genius.

thanks in advance.

Upvotes: 0

Views: 186

Answers (6)

Eborbob
Eborbob

Reputation: 1975

It might be best to not even run the query if there aren't any IDs selected, but sometimes this isn't possible or results in more confusing code. So instead just add the value NULL to the list - NULL doesn't match anything (even other NULLs) so it's more suitable than using 0 or any other arbitrary value that could exist.

// IDs got from checkboxes
$ids = array("10", "12");

// Add in NULL
$ids[] = "NULL";

$values = implode(",", $ids);
$sql = "Select * from new_deals where provider_id IN ($values)";

Upvotes: 0

Sahal
Sahal

Reputation: 4146

Do like this

$delim  =   "";
$whereCondition =   (count($_POST['multiple_check']) > 0) ? " where provider_id IN (" : "";

foreach ($_POST['multiple_check'] as $multipleCheckbox) {
     $whereCondition    .=  $delim.$multipleCheckbox;
     $delim =   ",";
}
$whereCondition .=  (count($_POST['multiple_check']) > 0) ? ")" : "where provider_id IN (NULL)";

if ($whereCondition != "") {
    $query  =   "Select * from new_deals {$whereCondition}";
}

Upvotes: -1

Dhruv Patel
Dhruv Patel

Reputation: 404

Please use this methodology when you work with search.

Create a variable to contain addition conditions of SQL Query. In your case lets say your request variable is provider_id.

$QueryAddons = "";

if(isset($_REQUEST['provider_id']) && !empty($_REQUEST['provider_id'])){
     $QueryAddons .= "AND provider_id IN ".implode(",",$_REQUEST['provider_id'])."";
}

Now your Query Statement Should Be :

  $selectStmt = "Select * from new_deals where 1=1 ".$QueryAddons;

Hope this helps.

Upvotes: 0

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

You are using php so here is a solution:

if (isset($_POST['checkboxfieldname']) and count($_POST['checkboxfieldname']) > 0) {
     // run query
} else {
  // dont run query
}

Upvotes: 11

Pupil
Pupil

Reputation: 23968

As per my understanding, your problem is SQL is generating error if no checkboxes are selected.

Why don't you use 

NULL

So that if there are no checkboxes selected, the code should be

Select * from new_deals where provider_id IN (NULL);

I have tried this and it does not give any error.

Thanks.

Upvotes: 1

Jakob
Jakob

Reputation: 749

You could use WHERE provider_id IN(0) if 0 is an id you dont have in your DB. This is ugly as hell and you should instead just not run the query at all.

Upvotes: 0

Related Questions