« Jython FTP Client | Main | 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']);
}
Tags: MySQL
Posted by pj at November 21, 2007 10:49 AM