Query problem - check if NULL or greater than some value

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
mecha_godzilla
Forum Contributor
Posts: 375
Joined: Wed Apr 14, 2010 4:45 pm
Location: UK

Query problem - check if NULL or greater than some value

Post by mecha_godzilla »

Hi,

I'm trying to write a PHP script that gives a report on how many support tickets were 'live' between a certain period of time. To explain this as simply as possible, the live period starts at (say) 1st June 2010 and ends at 30th June 2010. If a support ticket was closed before the 1st June then this must be excluded from the report, and if a support ticket was opened after 30th June then this must also be excluded.

Here's my query:

Code: Select all

SELECT ticket_id
FROM ticket_table
WHERE client_id = '$client_id'
AND ticket_opened < '2010-06-30'
AND ticket_closed > '2010-06-01'
OR ticket_closed IS NULL
The problem I have is that the test needs to check whether the ticket_closed value is either NULL or greater than 1st June, but the way my query is structured means that it all gets evaluated wrong. In the DB, the ticket_closed value is set to the DATE type and has a default value of NULL when a new ticket is added. I realise I could make the test easier if instead of NULL I manually edited the ticket_closed field to 0000-00-00 but there are an awful lot of records in there already :)

Thanks in advance,

Mecha Godzilla
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Query problem - check if NULL or greater than some value

Post by Eran »

You need to use brackets -

Code: Select all

AND ticket_opened < '2010-06-30'
AND (ticket_closed > '2010-06-01'
OR ticket_closed IS NULL)
Also, one query can update the entire table, it doesn't matter how many records are in there.

Code: Select all

UPDATE ticket_table SET ticket_closed='0000-00-00' WHERE ticket_closed IS NULL
Though it doesn't really make a difference (I'd keep the NULLs)
User avatar
mecha_godzilla
Forum Contributor
Posts: 375
Joined: Wed Apr 14, 2010 4:45 pm
Location: UK

Re: Query problem - check if NULL or greater than some value

Post by mecha_godzilla »

Thank you...I bow to your supreme knowledge :)

I did find a way to do what I wanted but it was a bit hideous - the query went something like:

Code: Select all

SELECT ticket_id
FROM ticket_table
WHERE client_id = '$client_id'
AND ticket_opened < '2010-06-30'
AND ticket_closed > '2010-06-01'
OR client_id = '$client_id'
AND ticket_opened < '2010-06-30'
AND ticket_closed IS NULL
I expected this approach to be pretty slow (given that I'm doing the same test twice) but it didn't seem to make much difference.

Thanks again,

M_G
Post Reply