CODEWITHSUNDEEP
phpmysql
Xavi
Xavi

Reputation: 2594

MYSQL Query not showing data properly

I am performing a filter by selected value this how my database looks like:

contactgroup  media  gender  age  isdelete  married  children driverslicens restatus retype
        -----------------------------------------------------------
        contactgroup1     SMS        Male     28     0   yes   yes     yes   owner    apart           
        contactgroup1     SMS        Female   26     0   no    null    no     rent  house
        contactgroup2     SMS        Male     32     0   null   no      null    owner  null
        contactgroup2     SMS        Male     38     0   yes    yes     no      null   null

This is my query:

SELECT * FROM contact
  where isdeleted = 0
    AND contactgroup in ('Contactgroup1', '')
    and media = 'sms'
    AND (`gender` = 'female' OR `gender` = 'male' OR `gender` is null)
    AND (`married` = 'yes' OR `married` = 'no' OR `married` is null)
    AND (`children` = 'yes' OR `children` = 'no' OR `children` is null)
    AND (`driverslicense` = 'yes' OR `driverslicense` = 'no' OR `driverslicense` is null)
    AND (`retype` = 'owner' OR `retype` = 'renting' OR `retype` is null)
    AND (`restatus` = 'apart' OR `restatus` = 'house' OR `restatus` is null)
    and age BETWEEN 18 AND 60

This query should show the data regarding the contactgroup1, but its showing all the four data can anyone tell me why it’s showing all the data where I went wrong?

Upvotes: 1

Views: 61

Answers (3)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You need to organize your query and group OR conditions

SELECT 
  * 
FROM
  contact 
WHERE isdeleted = 0 
  AND contactgroup IN ('Contactgroup1', '') 
  AND media = 'sms' 
  AND (
    `gender` = 'female' 
    OR `gender` = 'male' 
    OR `gender` = 'null'
  ) 
  AND age BETWEEN 18 
  AND 60

Demo

Edit as question is updated with new column

You need to identify null by using IS NULL

SELECT 
  * 
FROM
  contact 
WHERE isdelete = 0 
  AND contactgroup IN ('Contactgroup1', '') 
  AND media = 'sms' 
  AND (
    `gender` = 'female' 
    OR `gender` = 'male' 
    OR `gender` = 'null'
  ) 
  AND (
    `married` = 'yes' 
    OR `married` = 'no' 
    OR `married` IS NULL
  ) 
  AND age BETWEEN 18 
  AND 60

Demo 2

Upvotes: 3

oezi
oezi

Reputation: 51797

use brackets around your OR-conditions like this:

SELECT 
  *
FROM 
  contact 
WHERE isdeleted = 0
AND contactgroup in ('Contactgroup1', '')
AND media = 'sms' 
AND (gender = 'female' OR gender = 'male' OR gender = 'null')
AND age BETWEEN 18 AND 60

this is called Operator Precedence - AND is more important than OR, so if you don't use brackets, it's like you're doing this:

SELECT 
  *
FROM 
  contact
WHERE (isdeleted = 0 AND contactgroup in ('Contactgroup1', '') and media = 'sms' AND gender = 'female')
OR (gender = 'male')
OR (gender = 'null' AND age BETWEEN 18 AND 60)

Upvotes: 0

Fluffeh
Fluffeh

Reputation: 33512

Conditions in queries need to be popped in backets where you want some more than rudimentary logic applied to the search:

SELECT 
    * 
FROM 
    contact 
where 
    isdeleted = 0 
    AND contactgroup in ('Contactgroup1', '') 
    and media = 'sms' 
    AND
    ( 
        `gender` = 'female' 
        OR `gender` = 'male' 
        OR `gender` = 'null' 
    )
    and age BETWEEN 18 AND 60

Any where clauses that introduce an OR statement will basically cause the database to ignore any of the other AND statements and return any matching data.

In your query, you had

OR `gender` = 'male' 
OR `gender` = 'null'

Which basically meant bring back any rows that matched this data. The change in the query now makes it that all the other AND statements have to be met - as long as at least ONE of the OR statements is met inside the brackets.

Upvotes: 1

Related Questions