user3465835 user3465835 - 7 months ago 16
SQL Question

MYSQL 'IN' and 'COUNT' issues

I Have 2 SQL queries that I might not use correct:

SELECT
`contract_id`,
`contract_title`,
`contract_description`,
`contract_postalcode`,
FROM
`gmw_contracts`
WHERE contract_title COLLATE UTF8_GENERAL_CI REGEXP "test"
OR contract_description COLLATE UTF8_GENERAL_CI REGEXP "test"
AND contract_postalcode IN (
53111,53113,53129,53175,53225,53227,53229,53757
) ;

SELECT
COUNT(`contract_postalcode`)
FROM
`gmw_contracts`
WHERE `contract_title` COLLATE UTF8_GENERAL_CI REGEXP "test"
OR `contract_description` COLLATE UTF8_GENERAL_CI REGEXP "test"
AND `contract_postalcode` IN (
53111,53113,53129,53175,53225,53227,53229,53757
) ;


The first query selects rows with given parameters like keyword and postalcode etc, but i don't get the desired result. Seems like the postalcode thing is completely ignored.

The second query should count it so i can paginate the result in PHP (BUT I don't want to count PHP)

THE SOLUTION for the first query: I can use HAVING instead of AND. But I need to do it with the counting query too. COUNT and HAVING dont work together in this exapmle.

U got any hints or solutions for me?

Answer

Your first problem is caused by the combination of OR and AND:

... WHERE ... OR ... AND ...

Here the AND clause will probably not do what you want / expect as the conditions return true if any of the OR conditions is met.

You probably want:

... WHERE (... OR ...) AND ...