Mysql Query Help

[fluiduk]

Hi Guys

I am working on a small script that works out whether or not to show a webcam image based on rules that are stored in a database table.

There are two types of rules. The first is a permanent rule, so the image is not shown between 1am and 6am every monday etc. The other is a one off rule, for the next two hours we dont want it to show.

The table has the following fields.

Override_id
start_time - Start time of rule for permanent rule
end_time - End time of rule for permanent rule
mon
tue
wed
thu
fri
sat
sun - if these are set to 1 then it means that the tmes above apply to this rule
startdate - time and date field for one of rules
enddate - end time and date field for one off rules

Basically I need the query to search the table for any rules that fit, and so if the query returns a result, the image will not be shown and a placeholder will be shown instead.

The query I have so far kind of works, but I need to make it so that it only returns a result if the selected day is set to 1, and only between the times listed. At the moment it is blocked out for the whole day.

$query = 'select * from tbl_overrides where ("'.$time.'" NOT BETWEEN start_time AND end_time  AND '.$day.' != "1") AND ("'.$date_time.'" NOT BETWEEN startdate AND enddate)';

Hope this makes sense

Any help would be fantastic. Ideally I would like to avoid going down the route of having a start and end time for each day.

Thanks
Aaron

 

[Pho]

Having a separate entry for each day is the better option. Infact, this table should really be split into two - 'static rules' and 'temporary rules' as it were. Otherwise you have problems like this selecting correct data.