PHP/PDO Select from columns multiple conditions

Question

I am trying to obtain results for a given member where status is pending or accepted doing the below:

$status1 = "Pending";
$status2 = "Attended";
$query = $conn->prepare('SELECT * FROM members WHERE member_id=:mID AND status=:status1 OR status=:status2');
$query->execute(array(':mID' => $mID,':status1' => $status1, ':status2' => $status2));
if ($query->rowCount() > 0) {
   //start to create my table    
   while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
   //create variable, loop through and fill the table etc
   }
}else{
   echo "something";
}

This displays data - however, it even obtains results not specific to the member id (mID). Meaning other members data too!

I'm clearly missing something and or my query is wrong but struggling to find anything..

Any help appreciated.

Group the or statements. or use an in like WHERE member_id=:mID AND status in (:status1, :status2) — user3783243
– user3783243, Commented Aug 1, 2018 at 19:55
If this is your use case, note there isn't really a reason to use prepared statements at all. It's just as safe to do SELECT * FROM members WHERE member_id=:mID AND status='Pending' OR status='Attended', which would get rid of the execute line of code, and shorten prepare to query. Keeping the prepare() does have a negative consequence however, and that is that the query will hit your database 2 times instead of just once (This is a probably a micro-optimization unless you do this frequently). The only time prepare is needed, is if there is user input going to the database. — GrumpyCrouton
– GrumpyCrouton, Commented Aug 1, 2018 at 20:20

Alex Howansky · Accepted Answer · 2018-08-01 19:59:27Z

6

You need to look at operator precedence for your database. You're doing this:

SELECT * FROM members WHERE member_id = :mID AND status = :status1 OR status = :status2;

Which most likely results in this:

SELECT * FROM members WHERE (member_id = :mID AND status = :status1) OR status = :status2;

But that's not what you want, so you will have to explicitly use parens like this:

SELECT * FROM members WHERE member_id = :mID AND (status = :status1 OR status = :status2);

Alternatively, you can use IN so that there's no OR:

SELECT * FROM members WHERE member_id = :mID AND status IN (:status1, :status2);

edited Aug 1, 2018 at 19:59

answered Aug 1, 2018 at 19:54

Alex Howansky

54k8 gold badges85 silver badges102 bronze badges

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

1 Comment

Abdush Samad Miah Over a year ago

answered in record time and very well explained - thank you!

Collectives™ on Stack Overflow

PHP/PDO Select from columns multiple conditions

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related