php mysql conditional query AND OR

Question

I have a code like this and variables which are selected by user in check-able form

SELECT
FROM
WHERE

 product = 'Super Model'
 AND Model = 'Model1'
 OR Model = 'Model2'
 OR Model = 'Model3'
 OR Model = 'Model4'

GROUP BY

And check list look like:

Choose Models:

Model1
Model2
Model3
Model4

I want to make this query dynamic. I would make a switch case statement for this.

switch ($model) {

    case "Model1":
        $models = " AND Model = 'Model1' ";
    break;

    case "Model2":
        $models = " OR Model = 'Model2' ";
    break;

    case "Model3":
        $models = " OR Model = 'Model3' ";
    break;

    case "Model4":
        $models = " OR Model = 'Model4' ";
    break;
}

But there are problems: 1. It works only for one variable 2. If a user will choose i.e. model2 and model3 string will begin with OR statement, which is not logical as it suppose to start with AND.

I tried with something like this:

$models = " AND '.$model[0].' OR '.$model[1].' OR '.$model[2].'  OR '.$model[3].'  OR '.$model[4].' ";

then I put $models in WHERE statement but it doesn't work. And I have noticed problems: 1. If user will select only 2 models, probably statement will look like: " AND Model2 OR Model3 OR OR OR " 2. It is not dynamic, I would like to have something like: "for every item in the array do sth".

So maybe foreach? But how to deal with this AND at the beginning and OR between other models?

Any help appreciated.

If you get the values in an array, use the IN statement in your query;. It would be something like AND Model in ('model1', 'model2', 'model3') and so on. — Jorge Campos
– Jorge Campos, Commented Nov 4, 2013 at 10:47

ModulusJoe · Accepted Answer · 2013-11-04 11:04:56Z

Firstly you need to be careful with your logical operator combination (See this page about mysql operator precedence) http://dev.mysql.com/doc/refman/5.0/en/operator-precedence.html ), lets look at some examples:

mysql> select TRUE AND TRUE OR FALSE;
+------------------------+
| TRUE AND TRUE OR FALSE |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

Here we see an AND followed by an or that gives the expect result (product matches and the first model matches)

mysql> select FALSE AND FALSE OR TRUE;
+-------------------------+
| FALSE AND FALSE OR TRUE |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

But in this example the product doesn't match but if the model matches you will still get results. From your question this looks like this is not something you want.

mysql> select FALSE AND (FALSE OR TRUE);
+---------------------------+
| FALSE AND (FALSE OR TRUE) |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (0.00 sec)

Adding ( ) around your OR section will result in the product having to match as well as one of the selected models.

With that said a simple solution would be to use and IN clause (as this has simialr behaviour to an (...OR...OR...OR...) etc

WHERE  product = 'Super Model' AND Model IN ('Model1','Model2','Model3','Model4')

The in clause can be gained from PHP with:

$models_string = "AND model IN ('".implode("','",$model)."')";

But you would need to check that the $model array had elements.

If you wanted to use OR statements you could do something like the following:

$models = '';
if(count($model) > 0 ){
   $models .= ' AND ( FALSE';
   foreach($model => $m){
      $models .= " OR model = '$m'";
   }
   $models .= ')';
}

(I have not tested that code so there might be a typo but I think the logic is sound).

works like a charm :) $models_string saves a lot of my time! thnks

Yoga Fishguts · Accepted Answer · 2013-11-04 10:55:50Z

0

$models = array($model1, $model2, $model3, $model4, $model5);
$models = implode(',', $models);

SELECT * FROM table WHERE product = 'Super Model' AND FIND_IN_SET(Model, $models);

answered Nov 4, 2013 at 10:55

Yoga Fishguts

1161 silver badge7 bronze badges

Collectives™ on Stack Overflow

php mysql conditional query AND OR

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related