<?php
// open the db connection
$db = new wpdb('user', 'pass', 'db', 'server');
// debug
$db->show_errors();
$db->print_error();
// $db->last_query;
// $db->last_results;
// $today = date('Y-m-d');
$today = '2009-06-21';
$tomorrow = date( 'Y-m-d', mktime(0, 0, 0, date('m'), date('d')+1, date('Y')) );
$seven_days_ahead = date( 'Y-m-d', mktime(0, 0, 0, date('m'), date('d')+6, date('Y')) );
$thirty_days_ahead = date( 'Y-m-d', mktime(0, 0, 0, date('m'), date('d')+29, date('Y')) );
echo '<div class="column first">';
if ( ! empty($_REQUEST['date_range']) )
{
// clean up the data
$date_range = mysql_real_escape_string($_REQUEST['date_range']);
switch( $date_range )
{
case 'all':
$day_number = convert_date_to_day_number( $today );
$where = sprintf( 'WHERE e.day_id >= %s', $day_number );
break;
case 'next_7_days':
// code here
break;
case 'next_30_days':
// code here
break;
default:
$day_number = convert_date_to_day_number( $today );
$where = sprintf( 'WHERE e.day_id = %s', $day_number );
// get_events($where);
}
}
else
{
// no date selected, show todays events
$days = convert_date_to_day_number( $today );
foreach ( $days as $day_number )
{
$where = sprintf( 'WHERE e.day_id = %s', $day_number );
$events = get_events( $where );
if ($events)
{
echo '<table class="results">';
get_day_header( $day_number );
foreach ($events as $event)
{
render_event($event);
}
echo '</table>';
}
else
{
echo 'No events';
}
}
}
echo '</div> <!--/column-->';
function convert_date_to_day_number($date)
{
global $db;
$sql = "SELECT day_number FROM days WHERE day_date = '$date'";
$day_numbers = $db->get_results($sql);
foreach ($day_numbers as $key => $value)
{
$day_number[] = $value->day_number;
}
return $day_number;
}
function get_events($where)
{
global $db;
$sql = "SELECT
e.id,
TIME_FORMAT(e.start_time, '%H:%i' ) AS start_time,
e.title_en AS title,
p.name_en AS place,
et.name_en AS type,
w.week_number,
d.day_date AS start_date
FROM event AS e
LEFT JOIN place AS p ON p.id = e.place_id
LEFT JOIN event_type AS et ON et.id = e.event_type_id
LEFT JOIN days AS d ON d.id = e.day_id
LEFT JOIN week AS w ON w.id = d.week_id ";
$sql .= $where;
$events = $db->get_results($sql);
return $events;
}
function render_event($event)
{
$request_uri = $_SERVER['REQUEST_URI'];
$output = <<<EOD
<tr class="week-$event->week_number">
<td class="topic"></td>
<td class="time">$event->start_time</td>
<td class="summary">
<a href="$request_uri&event_id=$event->id">$event->title</a>
</td>
<td class="type">$event->type</td>
<td class="location">
<span class="addr">$event->place</span>
</td>
</tr>
EOD;
echo $output;
}
function get_day_header( $day_number )
{
global $db;
$sql = "SELECT
d.day_number,
DATE_FORMAT( d.day_date, '%W %e %M %Y' ) AS date,
DATE_FORMAT( d.day_date, '%b' ) AS month,
DATE_FORMAT( d.day_date, '%e' ) AS day
FROM days AS d
WHERE day_number = " . $day_number;
$day = $db->get_results($sql);
$day = $day[0];
$output = <<<EOD
<tr class="day">
<th colspan="5">
<em class="date">
<abbr class="dtstart" title="20090605T1806Z">
<span title="$day->date">
<span class="month">$day->month</span>
<span class="day">$day->day</span>
</span>
</abbr>
</em>
$day->date
<span class="event-day">Day $day->day_number</span>
</th>
</tr>
EOD;
echo $output;
}
?>
Refactorings
No refactoring yet !
This is part of an events page which can be filtered by date (using pre-defined date ranges or a date picker).
I want to avoid repeating the whole 'if ($events)...' etc. loop for every case in the switch.
I'm guessing I should move the whole thing to a function?