MySQL query with where clause and if statements

Question

I'm stuck again on a MySQL query and I would like some help.

I am trying to find the best possible way to do this. What I want to do is check if $var = 'x' and based on that run a different query.

Part of the code:

if ($var1 == '0' AND $var2 != '0' AND $var3 != '0' AND $var4 != '0') {
    echo $query = "SELECT * FROM table WHERE var1 = ''";
}elseif ($var1 == '1' AND $var2 != '0' AND $var3 != '0' AND $var4 != '0') {
    echo $query = "SELECT * FROM table WHERE var1 = '1'";
}elseif ($var1 != '0' AND $var2 == '0' AND $var3 != '0' AND $var4 != '0') {
    echo $query = "SELECT * FROM table WHERE var2 = ''";
}elseif ($var1 == '0' AND $var2 == '0' AND $var3 != '0' AND $var4 != '0') {
    echo $query = "SELECT * FROM table WHERE var1 = '' AND var2 = ''";
} etc..

The connections are a LOT. Is there a better way to do this? Pretty much each variable (in total 7) got 3 different choices. I can't put var = $var under the WHERE clause because sometimes when $var = '0' I mean all the results so I don't even put it there..

This is a filtering script/code by the way..

Does this even make sense to you? Thanks!

what happens if, for example, $var1 == 0 and $var2 == 0.. Is there an if statement for EVERY single combination? If not you can probably limit it down to a few statements using ||(OR) — superphonic
– superphonic, Commented Mar 27, 2014 at 22:18
Yeah -.-" There is an if statement for EVERY single combination indeed which are a lot.. — user3465843
– user3465843, Commented Mar 27, 2014 at 22:20

mesutozer · Accepted Answer · 2014-03-27 22:18:43Z

2

I think this can be done with a single query and varying WHERE conditions. You need to first define when to put var1 = '' in your WHERE clause. You should find out the cases when you need varX = '' in your query one by one.

$conds = array();
if ($var1 == '0' || $var2 == '0') {
  $conds[] = "var1 = ''"; // this should be the only point where var1 is included in your query, so you need to have every possible condition in this if part
}
if ($var3 == '0' && $var4 != '0') {
  $conds[] = "var3 = ''";
}
// ..etc..
$query = "SELECT * FROM TABLE WHERE " . implode(' AND ', $conds);

answered Mar 27, 2014 at 22:18

mesutozer

2,8591 gold badge15 silver badges13 bronze badges

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

1 Comment

user3465843 Over a year ago

Worked like a charm! Tyvm!

superphonic · Accepted Answer · 2014-03-27 22:35:12Z

0

How about:

$where_parts = array();
if (isset($var1)) {
  if ($var1 == 0) { $var1 = ''; }
  $where_parts [] = "var1 = '" . $var1 . "'";
}
if (isset($var2)) {
if ($var2 == 0) { $var2 = ''; }
  $where_parts [] = "var2 = '" . $var2 . "'";
}
if (isset($var3)) {
if ($var3 == 0) { $var3 = ''; }
  $where_parts [] = "var3 = '" . $var3 . "'";
}
if (isset($var4)) {
if ($var4 == 0) { $var4 = ''; }
  $where_parts [] = "var4 = '" . $var4 . "'";
}

$query = "SELECT * FROM table WHERE " . implode(' AND ', $where_parts );

edited Mar 27, 2014 at 22:35

answered Mar 27, 2014 at 22:29

superphonic

8,0936 gold badges35 silver badges64 bronze badges

1 Comment

user3465843 Over a year ago

Almost the same with the answer I chose! Tyvm for the help! (it seems to be exactly the same thing but your way got a few extra lines lol)

Beauvais · Accepted Answer · 2014-03-27 22:47:55Z

0

Is it possible for you to only concentrate on the variables being relevant for the query? For example something like:

$str = "";

# When will "var1" be in the query
switch($var1) {
  case '0' : $str .= " AND $var1=''"; break;
  case '1' : $str .= " AND $var1='1'"; break;
}

# When will "var2" be in the query
switch($var2) {
  case '0' : $str .= " AND $var2=''"; break;
  case '1' : $str .= " AND $var2='1'"; break;
}

...

# If not empty then remove the first " AND "
if(!empty($str)) {
    $str = substr($str,4);
}

# Final query is a lot of concatenated strings
$query = "SELECT * FROM table WHERE $str";

It may not be the most code-optimized but it is easy to understand.

edited Mar 27, 2014 at 22:47

answered Mar 27, 2014 at 22:41

Beauvais

2,3495 gold badges34 silver badges73 bronze badges

1 Comment

user3465843 Over a year ago

Thank you for the help mate, but it seems a bit more complicated to me than the other answers

Collectives™ on Stack Overflow

MySQL query with where clause and if statements

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related