SQL Data in Calendar Format

CBResources · September 4, 2013, 4:08am

Hey there!

I am attempting to retrieve data from a MySQL Database and display that data in calendar format for a work rosta. There are, however, two problems…

The data that I am retrieving needs to be split into two groups -
group a) All data where a specific column (date) is before a set date
group b) All data where a specific column (date) is after that set date

This is because I want 2 calendars - 1 for this week, 1 for next week.

So I have the dates sorted…

// Getting Monday's Date
$thisMon = date('Y-m-d',strtotime('Monday this week'));
$nextMon = date('Y-m-d',strtotime('Monday next week'));

And I have my SQL Select statement that fetches all records for a specific employee where the scheduledDate is equal to or above the beginning date of this week.


$query= " SELECT * from rota  WHERE ".$db->nameQuote('employeeNumber')." = ". $db->Quote($employeeNumber)." AND ".$db->nameQuote('scheduleDate')." >= ". $db->Quote($thisMon)." ";

$db->setQuery($query);
$result = $db->query();

Then I have my results fetched:


while ($row = mysqli_fetch_assoc($result)) {
        $date = $row["scheduleDate"];
        $start = $row["scheduledClockIn"];
        $end = $row["scheduledClockOut"];
}

Now the tricky part - How do I split them so that all dates before $nextMon are displayed in 1 group and all entries on or after $nextMon are in another so that I can display them on 2 separate calendars…

Then - How do I draw a weekly calendar and populate it with the data from this query? The calendar needs to be 7 columns across (1 for each day of the week) and 4 rows down (1 for each 4hrs of the day with the top of the table being 0800-1200, 1200-1600, 1600-2000, 2000-2400).

I’ve seen lots of lessons online for creating caledars with PHP but they are all Month/Year calendars and I have no idea how to get the data I have fetched from the database to populate the calendar.

Drummin · September 7, 2013, 9:04am

Ahh for the fun of it. Gives you something to play around with anyway.

<?php
$hours_array = array("0800-1200", "1200-1600", "1600-2000", "2000-2400");

// Getting Monday's Date
$thisMon = date('Y-m-d',strtotime('Monday this week'));
$thislast = date('Y-m-d', strtotime($thisMon . " + 6 days"));
$nextMon = date('Y-m-d',strtotime('Monday next week'));	
$nextlast = date('Y-m-d', strtotime($nextMon . " + 6 days"));

   	function createDateRangeArray($strDateFrom,$strDateTo){
	
			    $aryRange=array();
			
			    $iDateFrom=mktime(1,0,0,substr($strDateFrom,5,2),     substr($strDateFrom,8,2),substr($strDateFrom,0,4));
			    $iDateTo=mktime(1,0,0,substr($strDateTo,5,2),     substr($strDateTo,8,2),substr($strDateTo,0,4));
			
			    if ($iDateTo>=$iDateFrom)
			    {
			        array_push($aryRange,date('Y-m-d',$iDateFrom)); // first entry
			        while ($iDateFrom<$iDateTo)
			        {
			            $iDateFrom+=86400; // add 24 hours
			            array_push($aryRange,date('Y-m-d',$iDateFrom));
			        }
			    }
			    return $aryRange;
			}
//Create arrays of dates
$thisweek_dates = createDateRangeArray($thisMon,$thislast);
$nextweek_dates = createDateRangeArray($nextMon,$nextlast);

////////////////////////
/*
$query= " SELECT * from rota  WHERE ".$db->nameQuote('employeeNumber')." = ". $db->Quote($employeeNumber)." AND ".$db->nameQuote('scheduleDate')." >= ". $db->Quote($thisMon)." ";
$db->setQuery($query);
$result = $db->query();	
while ($row = mysqli_fetch_assoc($result)) {
*/
$emp_hours = array();
$query= " SELECT * from rota  WHERE employeeNumber = '$employeeNumber' AND scheduleDate >= '$thisMon' ";
$result = $db->query($query);
while ($row = $result->fetch_array(MYSQLI_ASSOC)){
	$date = $row["scheduleDate"];
	$start = $row["scheduledClockIn"];
	$end = $row["scheduledClockOut"];
	$emp_hours[$date]['ClockIn'][] = $start;
	$emp_hours[$date]['ClockOut'][] = $end;				
}
   ksort($emp_hours);
   /*
    echo "<pre>";
    print_r($emp_hours);
    echo "</pre>";
	*/
$content = "";


/////////////////////////
//////Current Week///////
/////////////////////////
$content .= "<table class=\\"table\\" width=\\"99%\\" cellspacing=\\"1\\" cellpadding=\\"1\\">\\r";
	
	$content .= "<tr>\\r";
	$showthisMon = date('F j, Y',strtotime($thisMon));
	$showthislast = date('F j, Y',strtotime($thislast));
	$content .= "<th align=\\"center\\" colspan=\\"7\\">This Week - $showthisMon - $showthislast</th>\\r";
	$content .= "</tr>\\r";

	//calendar heading
	$content .= "<tr>\\r";
	foreach($thisweek_dates as $date){
		$day = date('l',strtotime($date));
		$showday = date('M j, Y',strtotime($date));
		$content .= "<td align=\\"center\\" width=\\"14%\\" class=\\"heading\\"><b>$day</b><br />$showday</td>\\r";
	}
	$content .= "</tr>\\r";

	
	
	$content .= "<tr>\\r";
	$Tdailyminutes = array();
	foreach($thisweek_dates as $date){
		$content .= "<td align=\\"center\\" width=\\"14%\\">\\r";
		$dailyminutes = 0;
		foreach($hours_array as $hour){
			$times = explode("-",$hour);
			$min = $times[0];
			$max = $times[1];
			
			if (array_key_exists($date,$emp_hours)){
				//look for ClockIn time within min/max range			
				foreach($emp_hours[$date]['ClockIn'] as $timein){
					if ($timein >= $min && $timein <= $max){
						$in = $timein;
						break;
					}else{
						$in = "";
					}
				}			
				//look for ClockOut time within min/max range			
				foreach($emp_hours[$date]['ClockOut'] as $timeout){
					if ($timeout >= $min && $timeout <= $max){
						$out = $timeout;
						break;
					}else{
						$out = "";
					}
				}
			
			}
			$dailyminutes = $dailyminutes+($out-$in);
			$display = (isset($in) && !empty($in) ? "$in - $out" : '');
			$content .= "<b>$hour</b><br />$display<br />\\r";
		}
		$Tdailyminutes[$date] = $dailyminutes;
		$content .= "</td>\\r";
	}
	$content .= "</tr>\\r";
	
	//Total hours
	$content .= "<tr>\\r";
	foreach($thisweek_dates as $date){
		$Thours = $Tdailyminutes[$date]/100;
		$content .= "<td align=\\"center\\" width=\\"14%\\" class=\\"heading\\">$Thours hrs</td>\\r";
	}
	$content .= "</tr>\\r";

$content .= "</table>\\r";

/////////////////////////
////////NextWeek/////////
/////////////////////////
$content .= "<table class=\\"table\\" width=\\"99%\\" cellspacing=\\"1\\" cellpadding=\\"1\\">\\r";

	$content .= "<tr>\\r";
	$shownextMon = date('F j, Y',strtotime($nextMon));
	$shownextlast = date('F j, Y',strtotime($nextlast));
	$content .= "<th align=\\"center\\" colspan=\\"7\\">Next Week - $shownextMon - $shownextlast</th>\\r";
	$content .= "</tr>\\r";
	
	//calendar heading
	$content .= "<tr>\\r";
	foreach($nextweek_dates as $date){
		$day = date('l',strtotime($date));
		$showday = date('M j, Y',strtotime($date));
		$content .= "<td align=\\"center\\" width=\\"14%\\" class=\\"heading\\"><b>$day</b><br />$showday</td>\\r";
	}
	$content .= "</tr>\\r";
	
	$content .= "<tr>\\r";
	$Tdailyminutes = array();
	foreach($nextweek_dates as $date){
		$content .= "<td align=\\"center\\" width=\\"14%\\">\\r";
		$dailyminutes = 0;
		foreach($hours_array as $hour){
			$times = explode("-",$hour);
			$min = $times[0];
			$max = $times[1];
			
			if (array_key_exists($date,$emp_hours)){
				//look for ClockIn time within min/max range			
				foreach($emp_hours[$date]['ClockIn'] as $timein){
					if ($timein >= $min && $timein <= $max){
						$in = $timein;
						break;
					}else{
						$in = "";
					}
				}			
				//look for ClockOut time within min/max range			
				foreach($emp_hours[$date]['ClockOut'] as $timeout){
					if ($timeout >= $min && $timeout <= $max){
						$out = $timeout;
						break;
					}else{
						$out = "";
					}
				}
			
			}
			$dailyminutes = $dailyminutes+($out-$in);
			$display = (isset($in) && !empty($in) ? "$in - $out<br />" : '');
			$content .= "<b>$hour</b><br />$display\\r";
		}
		$Tdailyminutes[$date] = $dailyminutes;
		$content .= "</td>\\r";
	}
	$content .= "</tr>\\r";
	
	//Total hours
	$content .= "<tr>\\r";
	foreach($nextweek_dates as $date){
		$Thours = $Tdailyminutes[$date]/100;
		$content .= "<td align=\\"center\\" width=\\"14%\\" class=\\"heading\\">$Thours hrs</td>\\r";
	}
	$content .= "</tr>\\r";

$content .= "</table>\\r";
?>
<html>
<head>
<style type="text/css">
body {
margin: 0;
padding:5px;
font-weight: normal;
font-size:13px;
font-family: Arial;
}
.table {
	background-color:#EFEFEF;
	font-size:1em;
	color:#000000;
	padding:0;
	margin:0 auto 25px;
	border:1px solid #DDD7D0;
	-moz-border-radius: 6px 6px 6px 6px;
	-webkit-border-radius: 6px 6px 6px 6px;
	border-radius: 6px 6px 6px 6px;
	-moz-box-shadow: 1px 1px 4px #E9E4DD;
	-webkit-box-shadow: 1px 1px 4px #E9E4DD;
	box-shadow: 1px 1px 4px #E9E4DD;
}
.table th {
	font-size:1.2em;
	font-weight:bold;
	background-color:#EEF2F5; /* for non-css3 browsers */
	filter: progid:DXImageTransform.Microsoft.gradient(startColorstr='#EEF2F5', endColorstr='#A7AAB2'); /* for IE */
	background: -webkit-gradient(linear, left top, left bottom, from(#EEF2F5), to(#A7AAB2)); /* for webkit browsers */
	background: -moz-linear-gradient(top,  #EEF2F5, #A7AAB2); /* for firefox 3.6+ */; color:#171717;
	text-shadow: 1px 1px #E9E9E9;
	-moz-border-radius: 6px 6px 0 0;
	-webkit-border-radius: 6px 6px 0 0;
	border-radius: 6px 6px 0 0;
	padding:4px 0;
}

.table td {
	background-color:#FFFFFF;
	padding:1px 10px;
}
.table .heading{
	font-size:1.1em;
	background-color:#A7AAB2;
	padding:1px 10px;
	-moz-border-radius: 0 0 6px 6px;
	-webkit-border-radius: 0 0 6px 6px;
	border-radius: 0 0 6px 6px;
	border-bottom: 1px solid #808993;
}
</style>
</head>
<body>
<?php
echo $content;
?>
</body>
</html>

I didn’t add much data to it but I looks like image below.