Highlighting days on a calendar based on query results

[dprichard]

I have a calendar and I need to highlight the days that the employee has off.  I have one row for each employee with each day of the month showing.  I think I need to setup an array with the results, but I am really confused as to where in the code and how to do this.  The first day they have off highlights correctly, but the rest don't.  Any help would be greatly appreciated.

 

			do {
		echo "<tr><td nowrap class='cal_emp_name'>".$row_employees['emp_id']." - ".$row_employees['emp_lname'].", ".$row_employees['emp_fname']."</td>";
		$emp_id = '';
		$emp_id = mysql_real_escape_string($row_employees['emp_id']);
		$time_off = mysql_query("SELECT req_dates, req_ident, req_status, req_type FROM test_request WHERE req_emp = '$emp_id'") or die(mysql_error());
		$row_time_off = mysql_fetch_array($time_off);
		$req_dates = $row_time_off['req_dates'];


//Sets the first day of the month to 1
$day_num = 1;

//count up the days, until we have done them all in the month
while ($day_num <= $days_in_month) {
                $comp_date = strtotime($year . '-' . $month . '-' . $day_num);
                $request_start_comp = strtotime($req_dates);
                $start = date('Y-m-j',strtotime($req_dates));
	if ($comp_date == $request_start_comp) { 
		echo "<td class='cal_day_current'>"; 
		echo $day_num; 
		echo"</td>";
		} else { 
			echo "<td class='cal_day'>"; 
			echo $day_num; }
	$day_num++;
	$day_count++;
   }

 

[revraz]

Did we already go through this?

http://www.phpfreaks.com/forums/index.php/topic,181112.msg808694.html#msg808694

[aschk]

Also, I would say from the sounds of your initial question

I have one row for each employee with each day of the month showing.

 

that your database tables are not normalised properly.

[dprichard]

Well, I took your suggestion and changed it so it saves the individual days to the database instead of the date range, but to be honest with you I just don't get the array thing.  I have been reading about them all morning and I understand what an array is, but I am having issues getting this to work.  I tried setting the request dates to an array, but I just get errors when I do.  My thinking was that I needed to put all the request dates into an array then say if the comp date = any value in the array then highlight the date.  Does that sound like the right direction?

[dprichard]

aschk,

 

That is my output on the page not my database setup.  My database has an employee table with an employee id and then each request has an employee id that matches it up to the employee. 

[aschk]

Just to poke you in the direction that this might take I have a question:

Q. What happens for this year, do you store 365 days (rows) for each employee in your table?

Q. And what about next year, another 365 days (rows) for each employee.

 

With only 20 employees you've already got 20*365 rows most of which the employee will not be taking off I would imagine.

 

note: I do apologise if i have the wrong end of the stick. Perhaps some clarification is needed.

[aschk]

I think you've confused your do+while and while statements.

You appear to have started a do { statement at the top of the page, but the while clause that I would to finish it appears to be it's own statement and loop.

[dprichard]

okay, I will start from the beginning.  I was given an excel spreadsheet to mimic.  Using january as an example...

 

Jan

David 1 2 3 4 5 6 7 8 --- 31

Bob 1 2 3 4 5 6 7 8 --- 31

 

Feb

 

David 1 2 3 4 5 6 7 8 --- 29

Bob 1 2 3 4 5 6 7 8 --- 29

 

In the excel sheet they highlight the days off that the employee takes. 

 

In my database I have the employee table

 

emp id - 1

emp name - David

 

emp id -2

emp name - bob

 

and the time off table

 

req id 1

emp id 1

req date 2008-02-19

 

[dprichard]

PHP is building the calendar for me just fine and I highlight the first day off just fine, but just the first day off they take each month.  I am just getting into PHP and hand coding versus letting dreamweaver write crappy code for me so I am still learning. 

[dprichard]

Here is my complete code

 

<?php
include '../config.php';
include ('../functions.php');
permission_admin();

// Pulls in employee information from the database.
$employees = mysql_query("SELECT emp_id, emp_fname, emp_lname FROM employee ORDER BY emp_lname ASC") or die(mysql_error());
$row_employees = mysql_fetch_array($employees);

//Variables used in testing to show every month in 2008
$firstmonth = 1;
$lastmonth = 1;

while ($firstmonth <= $lastmonth) { 

//Starts the first calendar at Jan 2008 for Testing
$date = strtotime("1-$firstmonth-2008");

//$date = time();

//This puts the day, month, and year in seperate variables
$day = date('d', $date);
$month = date('m', $date);
$year = date('Y', $date);

//Generate the first day of the month
$first_day = mktime(0,0,0,$month, 1, $year);

//Generate the month
$title = date('F', $first_day);

//Find out what day of the week the first day falls on
$day_of_week = date('D', $first_day);

//Get the days in the current month
$days_in_month = cal_days_in_month(0, $month, $year);

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>

<title>Calendar</title>
<style>
	.calendar_table {
		border: solid;
		border-color: #999;
		border-size: 1px;
		margin: 0 0 30px 0;
		}
	.calendar_table td {
	}
	.cal_month_title {
			background-color: #0c4a93;
			text-align: center;
			font-size: 12px;
			color: #FFFFFF;
			font-family: Verdana;
	}
	.cal_day_title {
		background-color: #688ebd;
		text-align: center;
		font-size: 12px;
		color: #FFFFFF;
		font-family: Verdana;
		font-weight: bold;
		padding: 0 8px 0 8px;
		width: 3%;
	}
	.cal_day_blank {
		background-color: #999999;
		width: 3%;
	}
	.cal_day_current {
		background-color: #d3481f;
		text-align: center;
		font-size: 12px;
		color: #FFF;
		font-family: Verdana;
		font-weight: bold;
	}
	.cal_day {
		background-color: #bccde1;
		text-align: center;
		font-size: 12px;
		color: #FFF;
		font-family: Verdana;
		font-weight: bold;
	}
	.cal_emp_title {
		background-color: #688ebd;
		text-align: right;
		font-size: 12px;
		color: #FFFFFF;
		font-family: Verdana;
		font-weight: bold;
		padding: 0 8px 0 8px;
	}
	.cal_emp_name {
		background-color: #8fb0d9;
		text-align: right;
		font-size: 12px;
		color: #FFFFFF;
		font-family: Verdana;
		font-weight: bold;
		padding: 0 8px 0 8px;
	}
</style>
</head>
<body>
<?php 
echo "<table width='100%' class='calendar_table'>";
echo "<tr><th colspan=42 class='cal_month_title'> $title $year </th></tr>";
echo "<tr>";
	$day_count = 1;

		echo "<tr><td nowrap class='cal_emp_title'>Employee Name</td>";

	//Sets the first day of the month to 1
	$day_num = 1;

	//count up the days, until we have done them all in the month
	while ($day_num <= $days_in_month) {
		echo "<td class='cal_day_title'>".date('D', mktime(0,0,0,$month, $day_num, $year))."</td>";
		$day_num++;
		$day_count++;

	} echo "</tr>";

$day_count = 1;

		do {
		echo "<tr><td nowrap class='cal_emp_name'>".$row_employees['emp_id']." - ".$row_employees['emp_lname'].", ".$row_employees['emp_fname']."</td>";
		$emp_id = '';
		$emp_id = mysql_real_escape_string($row_employees['emp_id']);
		$time_off = mysql_query("SELECT req_dates, req_ident, req_status, req_type FROM test_request WHERE req_emp = '$emp_id'") or die(mysql_error());
		$row_time_off = mysql_fetch_array($time_off);
		$req_dates = $row_time_off['req_dates'];


//Sets the first day of the month to 1
$day_num = 1;

//count up the days, until we have done them all in the month
while ($day_num <= $days_in_month) {
                $comp_date = strtotime($year . '-' . $month . '-' . $day_num);
                $request_start_comp = strtotime($req_dates);
                $start = date('Y-m-j',strtotime($req_dates));
	if ($comp_date == $request_start_comp) { 
		echo "<td class='cal_day_current'>"; 
		echo $day_num; 
		echo"</td>";
		} else { 
			echo "<td class='cal_day'>"; 
			echo $day_num; }
	$day_num++;
	$day_count++;
   }


while ( $day_count >1 && $day_count <=7 )
{
echo "<td class='cal_day_blank'>   </td>";
$day_count++;
}

echo "</tr>";

} while ($row_employees = mysql_fetch_array($employees));

mysql_data_seek($employees, 0);


echo "</table>";

$firstmonth++;
}
print_r ($req_dates)
?>

</body>
</html>

[aschk]

I'm curious to see what html is being output because i'm seeing lots of while loops which i'm not sure are terminating properly. Do you have an online viewable version?

[dprichard]

http://portal.baldwinconnelly.com/test/

 

 

I just changed it so it only pulls in me.