This SELECT Query Is Driving Me Crazy!

[mrmbarnes]

Hi All

This SELECT statement is driving me crazy… unless I am missing something it should work.

I am using PHP 7.2.

My SELECT statement is:

SELECT firstName, emailAddress FROM mailing_list WHERE unsubscribed=“” OR unsubscribed IS NULL AND verified=“y” AND active=“” OR active IS NULL

A screenshot of my DB is below… this query should return 2 results uniqueID 15 and 20 however it is also returning uniqueID 10… WHY!!!

Any help would be great.

mrmbarnes

[John_Betong]

Try using round brackets inside the query.

[sibertius]

@John_Betong

Try using round brackets inside the query.

Is this what you ment?

SELECT firstName, emailAddress FROM mailing_list WHERE unsubscribed=“” OR (unsubscribed IS NULL AND verified=“y” AND active=“”) OR active IS NULL

[John_Betong]

Sort of.

The query is excluding uniqueID: 17 because it is not null.

[droopsnoot]

Just like when you use brackets in mathematical formulae, you need to be careful that you group the correct conditions together. ID 10 is included because you’ve got a general “or active is null” condition, which is fulfilled by that row.

I’d imagine you should probably have better control of the data going into the table too - I can foresee trouble if you allow a column to be NULL or a blank string. If there’s nothing to store in it, surely it would be better to always be one or the other? Wouldn’t “unsubscribed” be easier to deal with if it was always either “Y” or “N”, or that’s just another way of using a Boolean “true” or “false”? Same for “active” - it’s always going to be active or not active, so true or false would suffice.