mysql select where if variable is set

Question

I have two tables:

sales
sales_sizes

I want to do a query for sales_sizes constrained by the variables $gender, $category and $brand.

How do I do a where statement if the individual variables are set, and don't include them in the query if they are not set?

[pseudo code in brackets]

SELECT size, country
FROM `sales_sizes` WHERE sale_id IN (
    SELECT sale_id
    FROM `sales`
    WHERE [gender = $gender if $gender isset]
    AND [category = $category if $category isset]
    AND [brand = $brand if $brand isset]

)

are you using PDO or create a query as text?

user4035
– user4035

2013-10-03 08:10:05 +00:00
Commented Oct 3, 2013 at 8:10 — user4035
– user4035, Commented Oct 3, 2013 at 8:10
build the querry in php?

Naruto
– Naruto

2013-10-03 08:10:51 +00:00
Commented Oct 3, 2013 at 8:10 — Naruto
– Naruto, Commented Oct 3, 2013 at 8:10

Alma Do · Accepted Answer · 2013-10-03 08:11:28Z

1

You can gather your variables into array and then handle it like this:

$gender   = 'f';
$category = null;
$brand    = 'foo';

$rgCondition = ['gender'=>$gender, 'category'=>$category, 'brand'=>$brand];
$rgCondition = array_filter($rgCondition, function($x)
{
   //strip null-ed values:
   return isset($x);
});
$rgWhere     = [];

foreach($rgCondition as $sField=>$mValue)
{
   $rgWhere[] = '`'.$sField.'` = "'.$mValue.'"';   
}
$sWhere = count($rgWhere)?' WHERE '.join(' && ', $rgWhere):'';
//var_dump($sWhere);

-I've skipped strings escaping, but do not forget it.

answered Oct 3, 2013 at 8:11

Alma Do

37.4k10 gold badges81 silver badges106 bronze badges

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

Comments

S. S. Rawat · Accepted Answer · 2013-10-03 08:03:19Z

0

Use a CASE statement UPDATE: The previous syntax (as pointed out by a few people) doesn't work. You can use CASE as follows:

SELECT size, country
FROM `sales_sizes` WHERE sale_id IN (
    SELECT sale_id
    FROM `sales`
    WHERE [gender = $gender if $gender isset]
    AND [category = $category if $category isset]
    AND [brand = $brand if $brand isset]

)

WHERE gender
  CASE WHEN gender ($gender) = isset THEN 
    $gender 
  ELSE
    $gender='NULL'
  END

answered Oct 3, 2013 at 8:03

S. S. Rawat

6,1414 gold badges45 silver badges59 bronze badges

1 Comment

user4035 Over a year ago

this won't work, look at case statement syntax dev.mysql.com/doc/refman/5.0/en/case.html

Collectives™ on Stack Overflow

mysql select where if variable is set

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related