Paul Hollands: November 2007 Archives

« October 2007 | Main | December 2007 »

November 26, 2007

PHP JSON support

November 21, 2007

SQL for checking event overlap

$sql = sprintf("select d.diary_entry_id, 
from_unixtime(d.start_date) as start_time, 
from_unixtime(d.end_date) as end_time, 
concat(u.forename,' ',u.surname) as user_name 
from diary_entry d, diary_user_entry due, user u, diary_entry_status des  where (
(d.start_date between %s and %s) or 
(d.end_date between %s and %s) or
((%s between d.start_date and d.end_date) and (%s between d.start_date and d.end_date)) or
((d.start_date between %s and %s) and (d.end_date between %s and %s))
)
and d.diary_entry_id = due.diary_entry_id
and due.user_id in(%s)
and due.end_date is null and due.user_id = u.user_id and due.accepted_flag = 1
and d.diary_entry_id = des.diary_entry_id and des.end_date is null
and des.diary_entry_status_type_id != 3 order by u.surname",
$module['start_date'], $module['end_date'],
$module['start_date'], $module['end_date'],
$module['start_date'], $module['end_date'],
$module['start_date'], $module['end_date'],
$module['start_date'], $module['end_date'], 
$r['user_id']);

Errmmm.. Except that..

 if(($r['date_absent_from']) && ($r['to_date'])){
        
        $where = array();
        
        $where[] = sprintf("(
       (%d <= (a.date_absent_from + 86400)) and (%d >= a.date_absent_from)
        )", $r['date_absent_from'], $r['to_date']);
        
        }

Posted by pj at 10:49 AM | Comments (0)