Select statement with Where something = this OR that

Could I do this in a select statement where a WHERE condition has an OR

SELECT * from Table WHERE something = ? OR ?

You can if you follow the correct syntax. (Think of it like you’re writing any other language…)

SELECT * from Table WHERE column_name1 = ? OR column_name1 =?

would that work?

Try it and see.

1 Like

This is my statement and in all cases it shows FALSE…even if true…so there is something wrong with my condition

   $sql = "SELECT * FROM student_progress WHERE student_id = ? AND level = ? OR level = ?";

Well, now you’re being ambiguous.

A And B Or C.

AB
AC
C

Which of these values would you expect this query to return? Why can I say that the correct answer is ‘I dont know’?

(A) and (B or C)

But I could say that “A and B or C” means (A and B) or C.

You notice how to convey what you mean to me, you have to put parenthesis? That’s a hint. The database engine needs you to put the parenthesis too.

Yes I understand I need to group then. This is what I tried. I still get no-records each time

$level1 = 1;
$level2 = 2;

 $sql = "SELECT * FROM student_progress WHERE (student_id = ?) AND (level = ? OR level = ?)";
 $stmt = $conn->prepare($sql);
 $stmt->execute([$sid,$level1,$level2]);
 $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

and what does $conn->errorInfo() tell you? (it returns an array)

print_r ($conn->errorInfo());

is that how I print the error?

You’d save yourself a lot of time not asking me if thats how you do X, and instead just… run the code, and if what you get out the other end doesnt look like what you thought it should, then ask me what you did wrong :stuck_out_tongue:

I understand your frustration. I asked you AFTER I already ran the code LOL.

But I get no result…it wont load the rest of my content…just blank.

if you’ve left that space in between the r and the (, then it’s thrown a PHP error.

Yes there was indeed a space but that did not solve it. Here is how the code looks like so far.

 $sql = "SELECT * FROM student_progress WHERE (student_id = ?) AND (level = ? OR level = ?)";
 $stmt = $conn->prepare($sql);
 $stmt->execute([$sid,$level1,$level2]);
 print_r($conn->errorInfo());

(I see you typing there @Mittineague :P)

Right. Time to get superbasic then.

  ini_set('display_errors', '1');
  error_reporting(-1);
 $sql = "SELECT * FROM student_progress WHERE (student_id = ?) AND (level = ? OR level = ?)";
 $stmt = $conn->prepare($sql);
 $stmt->execute([$sid,$level1,$level2]);
 print_r($conn->errorInfo());
print_r($stmt->errorInfo());
die();

EDIT: That shoulda been a 1.

1 Like

AFAIK, MySQL has precedence rules

https://dev.mysql.com/doc/refman/8.0/en/operator-precedence.html

… The precedence of operators determines the order of evaluation of terms in an expression. To override this order and group terms explicitly, use parentheses. …

i.e. AND will evaluate before OR
a AND b OR c evaluates as (a AND b) OR c

Personally, I resort to using parentheses to group not to override precedence but mainly because I don’t want to learn or look up precedence rules for the different use cases I work with. I find it a lot easier to use parentheses whether they’re needed or not.

2 Likes

Thank you for both your help! I changed the SELECT statement to this:

 $sql = "SELECT * FROM student_progress WHERE (student_id = ?) AND ((level = ?) OR (level = 
    ?))";

and it seems to work now

Yeah, pretty much the same thing - I can’t be bothered to remember precedence rules (And before Or in MySQL, but in PHP they have equal precedence so it becomes whatever occurs first in the statement… etc…etc…)

Parenthesis just resolve all ambiguity :stuck_out_tongue:

EDIT: Nope. I’m wrong, PHP has now separated and/or into separate precedence, but “&&” has precedence over “||”, but “||” has precedence over “and”. So. Yeah. Screw trying to remember it all :stuck_out_tongue:

2 Likes

You could replace repeated OR with IN

SELECT * FROM student_progress WHERE student_id = ? AND level IN (?,?)

FWIW

4 Likes