Just wanted to say thanks to all for your help on this. It is very much appreciated. I basically worked out how to get what I wanted in the end, with a lot of help from others it must be said, but in case anyone wants to know how to do what I was trying to achieve, here's the final code:
<?php
$sql = "SELECT post_id,
DATE_FORMAT(start,'%m/%d/%Y') AS eventStart,
DATE_FORMAT(end,'%m/%d/%Y') AS eventEnd,
DATE_FORMAT(CURDATE(),'%m/%d/%Y') AS today,
DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 7 DAY),'%m/%d/%Y') AS endWeek
FROM $ec3->schedule
WHERE ";
$clause = array();
for( $i = 0; $i < 7; $i++ ) {
$clause[] = "DATE_ADD(CURDATE(), INTERVAL $i DAY) BETWEEN DATE(start) AND DATE(end)";
}
$sql .= implode(' OR ', $clause);
$sql .= ' ORDER BY start';
$result = mysql_query($sql) or trigger_error($sql . ' has failed. <br />' . mysql_error()); //pull data from database.
$dates = array(); //define dates array.
for($i = 0; $i < 7; $i++) {
$dates[] = date('m/d/Y', strtotime("+$i days")); //fill dates array with every date from now until 7 days from now.
}
foreach($dates as $days) { //go through dates array.
$data[$days] = NULL; //fill data array with keys for every date from now until 7 days.
}
if(mysql_num_rows($result) > 0) {
while($r = mysql_fetch_assoc($result)) { //while data exists in the database result resource.
if(strtotime($r['eventStart']) < strtotime($r['today'])) { //if the event started before today.
$r['eventStart'] = $r['today']; //make it's startdate today.
}
if(strtotime($r['eventEnd']) > strtotime($r['endWeek'])) { //if the event ends after this week is over.
$r['eventEnd'] = $r['endWeek']; //make the end of the week, it's end.
}
// echo '<pre>' . print_r($r,true) . '</pre>'; //de-bugging.
$started = false; //define a false variable.
foreach($data as $key => $value) { //loop through our data array.
if($key == $r['eventStart'] && $started == false) { //if the event starts at the present key, put it in the array.
$data[$key][] = $r['post_id'];
$started = ($key == $r['eventEnd']) ? false : true; //set started to true.
}
elseif($key == $r['eventEnd']) { //if the eventEnd is the present key, put it in the array,
$data[$key][] = $r['post_id'];
$started = false; //set started to false;
}
elseif($started == true) { //if started is true, then put the event in the array.
$data[$key][] = $r['post_id'];
}
}
}
foreach($data as $date => $v) { //loop through the data array.
echo date('D d', strtotime($date)) . '<br />---------<br />'; //echo the $date, followed by a line.
foreach($v as $event) {
$event_post = get_post($event);
$title = $event_post->post_title;
echo $event . " - " . $title . '<br />'; //each event is then echo'd to the page, followed by a break rule.
}
echo '<br />'; //after all of the events on this day, print another break rule, double spacing before the next date.
}
}
else {
echo 'No rows to show!';
}
?>
