CODEWITHSUNDEEP
phpmysqlsql
T_5
T_5

Reputation: 31

php mysql conditional query AND OR

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:

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.

Upvotes: 0

Views: 225

Answers (2)

ModulusJoe
ModulusJoe

Reputation: 1446

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).

Upvotes: 1

Yoga Fishguts
Yoga Fishguts

Reputation: 116

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

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

Upvotes: 0

Related Questions