SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Zealot
    Join Date
    Oct 2010
    Posts
    167
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Data in Calendar Format

    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...

    1) 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...

    Code PHP:
    // 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.

    Code PHP:
    $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:

    Code PHP:
    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.

  2. #2
    SitePoint Guru bronze trophy
    Join Date
    Feb 2013
    Posts
    772
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Ahh for the fun of it. Gives you something to play around with anyway.

    PHP Code:
    <?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.
    Sample136.jpg


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •