Mysql not returning expected (using LIKE and EQUALITY)

Asked

Viewed 41 times

0

Here is the example code:

SELECT * FROM maq_dispo
WHERE ativo = '1'
AND titulo LIKE '%coladeira%' OR descricao LIKE '%coladeira%' OR valor LIKE '%coladeira%'

The return shows all records indifferently if the field active presents the value 1.

What would be the solution?

mysql

  • place OR conditions within parentheses.

2 answers

2

If I understand, you want all the assets that have the title/description/value similar to the filter. So only the parentheses were missing:

SELECT 
    *
FROM 
    maq_dispo 
WHERE 
    ativo = '1' AND
    (
        titulo LIKE '%coladeira%' OR
        descricao LIKE '%coladeira%' OR 
        valor LIKE '%coladeira%'
    )

2


The only condition that should marry ativo = '1' is titulo LIKE '%coladeira%', which comes on right after with a AND. So, if one of the other two is true, WHERE will be met because they are connected with OR.

It is therefore necessary to group the conditions OR within parentheses, so only two conditions should be true: the ativo be equal to 1 And the result of the parentheses also:

ativo = '1'
AND
(titulo LIKE '%coladeira%'
OR descricao LIKE '%coladeira%'
OR valor LIKE '%coladeira%')

  • Yes I understand. However the problem I am facing is that I am using the CODEIGNITER 2.0 framework and it does not group conditions. I’m trying some way to do it manually. Thanks! It helped a lot!

  • In Codeigniter 2.0 there is no option to group queries. I thought it would be better to update to version 3.0 of Codeigniter where it has the group_start() and group_end() commands to group the queries. Now it all worked out correctly.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.