[RESOLVED] MySQL query not returning values based on date correctly

[dmacman1962]

Hi All,

I am trying to use a MySQL query that searches the database and only returns results during a specific time frame. And it is giving me articles that are not correct.

Here is the query,

SELECT * 
FROM daily_events
WHERE Questions LIKE '%$%'
OR Article LIKE '%$%'
OR Keywords LIKE '%$%'
AND PostDate
BETWEEN CURDATE( ) - INTERVAL 6
DAY AND CURDATE( ) 
ORDER BY PostDate DESC 
LIMIT 0 , 30

Now this should give me all records with the characters '$' in it and then display them only 6 days past.

But I get all the records and 2 of them have a PostDate of 2007-08-17

Thanks in advance,
Don

[Piranha]

It is correct according to your query. The query returns all rows where one of the following is true:

• column Questions contains the sign "$"

• column Article contains the sign "$"

• column Keywords includes the sign "$" and PostDate is in the last days.

What you want is probably that all of the below should be true:

• One or more of the columns Questions, Article and Keywords should contain the sign "$"

• PostDate is in the last days

Why it does like that can be understood by looking at the operator precedence.

Use parantheses to get the result you want:

SELECT *
FROM daily_events
WHERE (Questions LIKE '%$%'
OR Article LIKE '%$%'
OR Keywords LIKE '%$%')
AND PostDate
BETWEEN CURDATE( ) - INTERVAL 6
DAY AND CURDATE( )
ORDER BY PostDate DESC
LIMIT 0 , 30 

[dmacman1962]

Hi Piranha,

That was it exactly, and thanks for the op link. You and it were correct.

I have not had to do a query like this yet, and it did not occur to me that operator precedence would apply.

Sorry, I am tired and need to wake up. 🙂

Thanks,

Don