Page 1 of 1

How to reduce number of queries in this calendar script

Posted: Fri Sep 05, 2003 4:15 am
by JayBird
You may have seen my previous post about how to create a calendar for a year view....well, i created one myself.

What this is for is a holiday chart for my company's intranet, so at a glance your can see who is on holiday and when.

take a look at the layout here http://www.oconnordesign.co.uk/holiday_chart.gif

Okay, everyones holidays are stores in a table called holiday_chart with the following columns

Code: Select all

id    who    from_date    to_date    fd_hd
(fd_hd means full day or half day)

at the moment, the only way i can think of doing it is running a query for each day, but that would equal at least 444 queries, which takes quite a while.

Anyone got any better ideas how i can populate the calendar with the holiday dates from the DB?

Thanks

Mark

Posted: Fri Sep 05, 2003 8:17 am
by JAM
Just ideas as I cant test nada...

If you get * from holiday_chart, and loop the results populating an array with the looks of (hope it's understandable enough):

Code: Select all

// $startdate = from_date
// $days = calculated amount of days between from_date & to_date
// $who = the employee
$array&#1111;$startdate]&#1111;$days]&#1111;$who] = <field fd_hd value>;
...you should be able to populate your main table, when it's written to screen. while echoing the different days, with a check if the current day => $array['startdate'].

If so, decrease the $days, print $who and the fd_hd val.
During this, if $days < 1, remove or ignore that $who...

Hope i was clear enough. I did something similiar (not as advanced as this tho) that could help, but it's all stored on my server... =(

Posted: Fri Sep 05, 2003 10:15 am
by JayBird
Thanks JAM, i roughly did it how you said except that before the calendar is generated, i create an array with all the days of the year that everone is having off (0 through 366).

Then, on each day, i check to see if the current day, matches a day in the array. This is much quicker than executing a MySQL query on every day.

Also, the way i have done it with day number of year overcome the problem i had when someones holiday fell over 2 months (e.g end of September to begininig of October).

Anyway, just glad i did it eventually, taken me all day. If anyone is interested here is my final code.

If anyone fancies seeing if they can reduce the code, feel free ;)

Now for the admin pages :)

Code: Select all

<?

$BasePath = str_repeat("../", substr_count(dirname($_SERVER["SCRIPT_NAME"]), "/"));

require ($BasePath."ocdaintranet/includes/db_connection.php");

// Get todays date
$todaysEpoch = time();
$todaysEpochArray = getdate($todaysEpoch);

if(!isset($_GET['year'])) {
	$year = $todaysEpochArray['year'];
} else {
	$year = $_GET['year'];
}

$query = "SELECT * FROM holiday_chart where from_date LIKE '%year' OR to_date LIKE '%$year'";
$result = mysql_query($query) or die(mysql_error());
$i = 0;
if (mysql_num_rows($result) != 0) {
	while($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
		$to_dates[$i] = $line['to_date'];
		$from_dates[$i] = $line['from_date'];
		$who[$i] = $line['who'];
	
		$i++;
	}


	$i = 0;
	$z = 0;

	// Get the number of the day of the year that people are having off
	foreach ($from_dates as $temp_dates) {
		$end_date = split("-", $to_dates[$i]);
		$start_date = split("-", $temp_dates);
				
		$day_number_start = date("z", mktime(0,0,0,$start_date[1],$start_date[0],$year));
		$day_number_end = date("z", mktime(0,0,0,$end_date[1],$end_date[0],$year));			
				
		while($day_number_start <= $day_number_end) {
			$days_off[$z] = $day_number_start;
			$who_sorted[$z] = $who[$i];
			$day_number_start++;
			$z++;
		}
	$i++;

	}
}	

?>

<html>
<head>
<title>Holiday Chart</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script type="text/javascript" src="/ocdaintranet/javascript/general.js"></script>
<script type="text/JavaScript" src="/ocdaintranet/javascript/overlib_mini.js"></script>
<link href="/ocdaintranet/styles/stylesheet.css" rel="stylesheet" type="text/css">
<link href="/ocdaintranet/styles/calendar.css" rel="stylesheet" type="text/css">
<style type="text/css"> 
<!-- 
.button { cursor:hand; background-color: #DBEDFF } 
.buttonover { cursor:hand; background-color: #CEE7FF } 
.buttonweekend { background-color: #FFD5D5 } 
.buttonoverweekend { cursor:hand; background-color: #FFC5C5 }
.nodate { background-color: #DDDEDF } 
--> 
</style> 
<script language="JavaScript">
<!-- Change the row colour on mouseOver/Out
 
function BGNew(obj, new_style) { 
obj.className = new_style; 
} 

//-->
</script>
</head>
<body onLoad="MM_preloadImages('/ocdaintranet/images/winxp/btn_add_over.gif')">

<? require ($BasePath."ocdaintranet/menu/menu.php"); ?>

<h1 class="heading">Holiday Chart - <? echo $year; ?></h1>

<?php

echo "<table width="100%">";
echo "\t<tr>\n";

$dayArray = array("Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun");

$dayMonths = 1;
$day = 0;

$january = 1;
$february = 1;
$march = 1;
$april = 1;
$may = 1;
$june = 1;
$july = 1;
$august = 1;
$september = 1;
$october = 1;
$november = 1;
$december = 1;

$month = 1;

echo "\t\t<td width="4%">&nbsp;</td>\n";

while ($month <= 12) {
	echo "\t\t<td width="8%" align="center" class="odd">";
	echo date("M", mktime(0,0,0,$month,1,$year));
	echo "</td>\n";
	$month++;
}

echo "\t</tr>\n";

while ($dayMonths <= 37) { // The maximum number of cells any month will need is 37

	if ($day == 0 || $day > 6) { // If day = sunday, start back at monday
		$day = 0;
		$checkDay = $dayArray[$day];
	} else {
		$checkDay = $dayArray[$day];
	}
	
	if ($checkDay == "Sun" || $checkDay == "Sat") {
			$td_colour_weekend = "buttonweekend";
		} else {
			$td_colour_weekend = "even";
	}
	echo "\t<tr>\n";
	echo "\t\t<td width="4%" class="odd" align="right">$checkDay</td>\n";

	// January check
			
	// If the current day number is equal to the name of the day and we are still in the same month, output the dat
	if (date("D", mktime(0,0,0,1,$january,$year)) == $checkDay && date("M", mktime(0,0,0,1,$january,$year)) == "Jan") {
	
		$x = 0;
		if (is_array($days_off)) {
			foreach ($days_off as $temp_daysoff) {
				if ($temp_daysoff == date("z", mktime(0,0,0,1,$january,$year))) {
					$name = $who_sorted[$x];
				}
				$x++;
			}
		}
		
		echo "\t\t<td width="8%" align="top" class="".$td_colour_weekend."">";
		echo "<div align="right">".$name."&nbsp;&nbsp;".date("d", mktime(0,0,0,1,$january,$year))."</div>";
		echo "</td>\n";
		
		$name = "";
			
		$january++;

	} else { // Otherwise, print a blank cell
		echo "\t\t<td width="8%" class="nodate">&nbsp;</td>\n";
	}
			
	// February check
	if (date("D", mktime(0,0,0,2,$february,$year)) == $checkDay && date("M", mktime(0,0,0,2,$february,$year)) == "Feb") {
	
		$x = 0;
		if (is_array($days_off)) {
		foreach ($days_off as $temp_daysoff) {
			if ($temp_daysoff == date("z", mktime(0,0,0,2,$february,$year))) {
				$name = $who_sorted[$x];
			}
			$x++;
		}
		}

		echo "\t\t<td width="8%" align="top" class="".$td_colour_weekend."">";
		echo "<div align="right">".$name."&nbsp;&nbsp;".date("d", mktime(0,0,0,2,$february,$year))."</div>";
		echo "</td>\n";
		
		$name = "";
			
		$february++;

	} else {
		echo "\t\t<td width="8%" class="nodate">&nbsp;</td>\n";
	}
			
	// March check
	if (date("D", mktime(0,0,0,3,$march,$year)) == $checkDay && date("M", mktime(0,0,0,3,$march,$year)) == "Mar") {
	
		$x = 0;
		if (is_array($days_off)) {
		foreach ($days_off as $temp_daysoff) {
			if ($temp_daysoff == date("z", mktime(0,0,0,3,$march,$year))) {
				$name = $who_sorted[$x];
			}
			$x++;
		}
		}

		echo "\t\t<td width="8%" align="top" class="".$td_colour_weekend."">";
		echo "<div align="right">".$name."&nbsp;&nbsp;".date("d", mktime(0,0,0,3,$march,$year))."</div>";
		echo "</td>\n";
		
		$name = "";
			
		$march++;

	} else {
		echo "\t\t<td width="8%" class="nodate">&nbsp;</td>\n";
	}
			
	// April check
	if (date("D", mktime(0,0,0,4,$april,$year)) == $checkDay && date("M", mktime(0,0,0,4,$april,$year)) == "Apr") {
	
		$x = 0;
		if (is_array($days_off)) {
		foreach ($days_off as $temp_daysoff) {
			if ($temp_daysoff == date("z", mktime(0,0,0,4,$april,$year))) {
				$name = $who_sorted[$x];
			}
			$x++;
		}
		}

		echo "\t\t<td width="8%" align="top" class="".$td_colour_weekend."">";
		echo "<div align="right">".$name."&nbsp;&nbsp;".date("d", mktime(0,0,0,4,$april,$year))."</div>";
		echo "</td>\n";
		
		$name = "";
			
		$april++;

	} else {
		echo "\t\t<td width="8%" class="nodate">&nbsp;</td>\n";
	}
			
	// May check
	if (date("D", mktime(0,0,0,5,$may,$year)) == $checkDay && date("M", mktime(0,0,0,5,$may,$year)) == "May") {
	
		$x = 0;
		if (is_array($days_off)) {
		foreach ($days_off as $temp_daysoff) {
			if ($temp_daysoff == date("z", mktime(0,0,0,5,$may,$year))) {
				$name = $who_sorted[$x];
			}
			$x++;
		}
		}

		echo "\t\t<td width="8%" align="top" class="".$td_colour_weekend."">";
		echo "<div align="right">".$name."&nbsp;&nbsp;".date("d", mktime(0,0,0,5,$may,$year))."</div>";
		echo "</td>\n";
		
		$name = "";
			
		$may++;

	} else {
		echo "\t\t<td width="8%" class="nodate">&nbsp;</td>\n";
	}
			
	// June check
	if (date("D", mktime(0,0,0,6,$june,$year)) == $checkDay && date("M", mktime(0,0,0,6,$june,$year)) == "Jun") {
	
		$x = 0;
		if (is_array($days_off)) {
		foreach ($days_off as $temp_daysoff) {
			if ($temp_daysoff == date("z", mktime(0,0,0,6,$june,$year))) {
				$name = $who_sorted[$x];
			}
			$x++;
		}
		}

		echo "\t\t<td width="8%" align="top" class="".$td_colour_weekend."">";
		echo "<div align="right">".$name."&nbsp;&nbsp;".date("d", mktime(0,0,0,6,$june,$year))."</div>";
		echo "</td>\n";
		
		$name = "";
			
		$june++;

	} else {
		echo "\t\t<td width="8%" class="nodate">&nbsp;</td>\n";
	}
			
	// July check
	if (date("D", mktime(0,0,0,7,$july,$year)) == $checkDay && date("M", mktime(0,0,0,7,$july,$year)) == "Jul") {
	
		$x = 0;
		if (is_array($days_off)) {
		foreach ($days_off as $temp_daysoff) {
			if ($temp_daysoff == date("z", mktime(0,0,0,7,$july,$year))) {
				$name = $who_sorted[$x];
			}
			$x++;
		}
		}

		echo "\t\t<td width="8%" align="top" class="".$td_colour_weekend."">";
		echo "<div align="right">".$name."&nbsp;&nbsp;".date("d", mktime(0,0,0,7,$july,$year))."</div>";
		echo "</td>\n";
		
		$name = "";
			
		$july++;

	} else {
		echo "\t\t<td width="8%" class="nodate">&nbsp;</td>\n";
		}			
			
	// August check
	if (date("D", mktime(0,0,0,8,$august,$year)) == $checkDay && date("M", mktime(0,0,0,8,$august,$year)) == "Aug") {

		$x = 0;
		if (is_array($days_off)) {
		foreach ($days_off as $temp_daysoff) {
			if ($temp_daysoff == date("z", mktime(0,0,0,8,$august,$year))) {
				$name = $who_sorted[$x];
			}
			$x++;
		}
		}

		echo "\t\t<td width="8%" align="top" class="".$td_colour_weekend."">";
		echo "<div align="right">".$name."&nbsp;&nbsp;".date("d", mktime(0,0,0,8,$august,$year))."</div>";
		echo "</td>\n";
		
		$name = "";
			
		$august++;

	} else {
		echo "\t\t<td width="8%" class="nodate">&nbsp;</td>\n";
	}
			
	// September check
	if (date("D", mktime(0,0,0,9,$september,$year)) == $checkDay && date("M", mktime(0,0,0,9,$september,$year)) == "Sep") {
		
		$x = 0;
		if (is_array($days_off)) {
		foreach ($days_off as $temp_daysoff) {
			if ($temp_daysoff == date("z", mktime(0,0,0,9,$september,$year))) {
				$name = $who_sorted[$x];
			}
			$x++;
		}
		}
		
		echo "\t\t<td width="8%" align="top" class="".$td_colour_weekend."">";
		echo "<div align="right">".$name."&nbsp;&nbsp;".date("d", mktime(0,0,0,9,$september,$year))."</div>";
		echo "</td>\n";
		
		$name = "";
		
		$september++;

	} else {
		echo "\t\t<td width="8%" class="nodate">&nbsp;</td>\n";
	}
			
	// October check
	if (date("D", mktime(0,0,0,10,$october,$year)) == $checkDay && date("M", mktime(0,0,0,10,$october,$year)) == "Oct") {

		$x = 0;
		if (is_array($days_off)) {
		foreach ($days_off as $temp_daysoff) {
			if ($temp_daysoff == date("z", mktime(0,0,0,10,$october,$year))) {
				$name = $who_sorted[$x];
			}
			$x++;
		}
		}
		
		echo "\t\t<td width="8%" align="top" class="".$td_colour_weekend."">";
		echo "<div align="right">".$name."&nbsp;&nbsp;".date("d", mktime(0,0,0,10,$october,$year))."</div>";
		echo "</td>\n";
		
		$name = "";
			
		$october++;

	} else {
		echo "\t\t<td width="8%" class="nodate">&nbsp;</td>\n";
	}
			
	// November check
	if (date("D", mktime(0,0,0,11,$november,$year)) == $checkDay && date("M", mktime(0,0,0,11,$november,$year)) == "Nov") {

		$x = 0;
		if (is_array($days_off)) {
		foreach ($days_off as $temp_daysoff) {
			if ($temp_daysoff == date("z", mktime(0,0,0,11,$november,$year))) {
				$name = $who_sorted[$x];
			}
			$x++;
		}
		}

		echo "\t\t<td width="8%" align="top" class="".$td_colour_weekend."">";
		echo "<div align="right">".$name."&nbsp;&nbsp;".date("d", mktime(0,0,0,11,$november,$year))."</div>";
		echo "</td>\n";
		
		$name = "";
			
		$november++;

	} else {
		echo "\t\t<td width="8%" class="nodate">&nbsp;</td>\n";
	}
			
	// December check
	if (date("D", mktime(0,0,0,12,$december,$year)) == $checkDay && date("M", mktime(0,0,0,12,$december,$year)) == "Dec") {

		$x = 0;
		if (is_array($days_off)) {
		foreach ($days_off as $temp_daysoff) {
			if ($temp_daysoff == date("z", mktime(0,0,0,12,$december,$year))) {
				$name = $who_sorted[$x];
			}
			$x++;
		}
		}

		echo "\t\t<td width="8%" align="top" class="".$td_colour_weekend."">";
		echo "<div align="right">".$name."&nbsp;&nbsp;".date("d", mktime(0,0,0,12,$december,$year))."</div>";
		echo "</td>\n";
		
		$name = "";
			
		$december++;

	} else {
		echo "\t\t<td width="8%" class="nodate">&nbsp;</td>\n";
	}
			
echo "\t</tr>\n";
			
$day++;
$dayMonths++;
}

echo "</table>";

?>
<table cellspacing="0" width="100%" border="0" cellpadding="0">
  <tr>
    <td nowrap align="right" class="footerCell"><a href="holiday_chart.php?year=<? echo ($year-1); ?>"><< Previous year</a> | <a href="holiday_chart.php?year=<? echo ($year+1); ?>">Next year >></a></td>
  </tr>
  <tr>
    <td height="5" colspan="2"><img width="1" height="5" border="0" src="../themes/winxp/spacer.gif" alt=""></td>
  </tr>
</table>

</body>
</html>
Mark

Posted: Fri Sep 05, 2003 5:14 pm
by JAM
Glad it got working. =)

I was just about to ask you for the code, so thanks. The interest got me starting at your previous issue (populating the db with the 'year') and I wanted to play around with it too...

(This particular code snippet is now stol... borrowed for later use)

This is a part of me trying to convince my employee moving from multible platform system to unix/bsd alone. But I need 'stuff' that i can throw in their face, like this. ;)