Tying of database to staff's timetable

[tpstudent]

Hi,

 

I am creating a staff directory board whereby it would show the status of the staff whether they are in or not. However, i would need to tie the timetable table to the status whether they in or not. Meaning if the timetable shows that the staff is having lessons, it would turn the status to out. When the timetable shows that they do not have lessons , then the status would set to off.

 

This is example of how the timetable columns in the database:

 

This is the staff table which would capture the status in or out of office:

 

I did some codes, however was unsure on whether what we did were correct.

 

Code#1

 

require_once('staffSql.php');

mysql_select_db($dbname); 

//Query the database to see if the given username/password combination is valid.
$query = "Select * from timetable where staffid = '$staffid'";

$result = mysql_query($query); 

while ($row = mysql_fetch_array ($result)) {
$staffid=$row['staffid'];
$day=$row['day'];
$start=$row['start'];
$end=$row['end'];
$mod=$row['mod'];
$room=$row['room'];
$roomPart=$row['roomPart'];
$sem=$row['sem'];
$weeks=$row['weeks'];
$lecturer=$row['lecturer'];
$date= date("D");
$time = date("H:i"); 
$staffid = $_POST['staffid']; 
$status = mysql_real_escape_string($_POST["status"]); 
require_once('staffSql.php');
mysql_select_db($dbname); 

if($start < $time && $date == $day){

$query = "UPDATE staff set status= 'In' where staffid='$staffid'"; 
$result = mysql_query($query); 
}

else if($end > $time && $date == $day){ 
$query = "UPDATE staff set status= 'Out' where staffid='$staffid'"; 
$result = mysql_query($query); 

}
}//close of while

 

Code #2

<?php
     
     require_once('staffSql.php');
     
     $query2 = "Select * from timetable where staffid='$staffid'";
     
     $result2 = mysql_query($query2); 
     
     while ($row = mysql_fetch_array ($result2)) {
     $staffid = $row['staffid'];
     $day = $row['day'];
     $start = $row['start'];
     $end = $row['end'];
     $mod = $row['mod'];
     $grp = $row['grp'];
     $room = $row['room'];
     $roomPart = $row['roomPart'];
     $sem = $row['sem'];
     $weeks = $row['weeks'];
     $lecturer = $row['lecturer'];
     
     $date= date("D",time() + 8*60*60);
     $time = date("H:i",time() +8*60*60);
     
     if($start < $time){
       if($end> $time){
       if( $time & $day == $day){
       
       $query3 = "UPDATE staff set status = 'Out' where staffid= $staffid";
       $result3 = mysql_query($query3); 
       }
      }
      else{
      
      $query3 = "UPDATE staff set status = 'In' where staffid= $staffid";
      $result3 = mysql_query($query3); 
      
      }
   }
     
  ?> 

 

Can anyone advise which codes are better to use (Code #1 or code#2)? Thanks so much for the help.

[Zane]

if you need to have this as a live time update type of thing then you'll need to setup a cron job to:

 

SELECT all the the staff from the timetable....like you have already

set the cron job script to check each and every row....to see if the start time is the same or less than the current time...

if it is (less than) then check the end time and see if it's greater....if it is..

take the staffid and set his status accordingly

 

although you would have you order the timetable by the start time so you don't overwrite the status.

 

I'm sure that's no ALL you have to do ...but a cron job is the general area where you need to go..and the two PHP scripts you have already grab more fields than you need..all you need is the start time, end time, and staff id, and day

[tpstudent]

Hi,

 

Thanks for replying. I would like to ask how do you setup a cron job? Can you give some examples on it? I tried to search online but do not really understand how to create it.

[waynew]

Your host should allow you to do a cron job. If you're on Windows, use a scheduled task.

[tpstudent]

I am not really sure how to do it as the host belongs to one of my lecturer. What do mean of a scheduled task? Is it I have to adjust it in the host? Must I do anything to my codings to check it every moment?