Hi

This is the first time I have really played with calendars in PHP and am a little, no, a lot stuck.

The MySQL database holds "job_id", "visit_date" which is a timestamp and also "engineer" which is one or more names seperated with :

My planner needs to have the dates in the left column, row by row, and the engieers names (pulled from another table) along the top row, column by column.

I have the date and names part working and can also tell if there is any work booked for that day, but what I cannot do, it work out how to put the job_id under the correct engineers name.

With the code below, I get 2 jobs showing on the 1st of the month which is correct, but how do I get the job under the correct engineers column?

Please be gentle:

<table>
<tr><td> </td><td> </td>
<?php
include ("includes/db.php");

$desired_month = 6;
$desired_year = "2012";
$num_days = date('t', mktime(0, 0, 0, $desired_month, 1, $desired_year));

$engarray = array();
$result = mysql_query("SELECT * FROM engineers ORDER BY name ASC");
while ($row=mysql_fetch_object($result)) {
    $engarray[] = $row->name; 
?>
<td><?php echo $row->name;?></td>

<?php
}

?>
</tr>
<?php
for ($index = 1; $index <= $num_days; $index++) {
    $daysdate  = mktime(0, 0, 0, date($desired_month)  , date($index), date($desired_year));
    $day = date('D', mktime(0, 0, 0, date($desired_month)  , date($index), date($desired_year)));
?>
    <tr><td><?php echo $day?></td><td><?php echo date('d M', $daysdate)?></td>
    <td>
    <?php
    $jobs = mysql_query("SELECT * FROM job_details");
    while ($rowjobs = mysql_fetch_object($jobs)) {
        if ($rowjobs->visit_date == $daysdate) {
            echo "job";
        }
    }
    ?>

    </td>
    </tr>

<?php
}
?>
</table>

I have an array with the engineers names in, but am just guessing now.

Thanks in advance for any help.

Member Avatar
diafol

IMO, your table structure is wrong. I'd do this:

jobs
j_id (PK)
j_date
j_title
(other fields??)

engineers
e_id (PK)
e_name
(other fields??)

visits
v_id (PK)
j_id (FK)
e_id (FK)
v_date

To get a nice list of names per job, you can use INNER JOIN and GROUP_CONCAT with a suitable SEPARATOR.

Hi and thanks for your quick reply.

I have the first two tables as above but not the third, but understand what you are pointing to. I use that method for most of my DB.

planner

I have built the attached image, but cannot work out how to associate or skip the Eng columns if they are allocated or not.

if visit_date = 01 and engineer = "eng column name" then echo job_id
then do the same for rest of columns but number of engineers is not pre-defined.

How do I know, even if I restructured the tables, what column I am in and what Eng name I am looking for? if you see what I am getting at?

Thanks
Woosh

Double post sorry

Not saying it is the best way, or cleanest code, but it works and puts the job_id in the table cell for the correct engineer.

<table border="0" cellpadding="5">
<tr class="header"><td colspan="2"> </td>
<?php
include ("includes/db.php");

$desired_month = 6;
$desired_year = "2012";
$num_days = date('t', mktime(0, 0, 0, $desired_month, 1, $desired_year));

$engarray = array();
$result = mysql_query("SELECT * FROM engineers ORDER BY name ASC");
while ($row=mysql_fetch_object($result)) {
    $engarray[] = $row->name;
?>
<td><?php echo $row->name;?></td>

<?php
}
    $totalengs = count($engarray);
?>
</tr>
<?php
for ($index = 1; $index <= $num_days; $index++) {
    $daysdate  = mktime(0, 0, 0, date($desired_month)  , date($index), date($desired_year));
    $day = date('D', mktime(0, 0, 0, date($desired_month)  , date($index), date($desired_year)));
?>
    <tr><td class="header"><?php echo $day?></td><td class="header"><?php echo date('d M', $daysdate)?></td>

    <?php
    $jobs = mysql_query("SELECT * FROM job_details");
    while ($rowjobs = mysql_fetch_object($jobs)) {
        if ($rowjobs->visit_date == $daysdate) {
            for ($count = 0; $count < $totalengs; $count++) {
                if (strstr($rowjobs->engineer, $engarray[$count])) {
                    echo "<td>".$rowjobs->job_id."</td>";
                } else {
                    echo "<td> </td>";
                }
            }           
        }
    }
    ?>
    </tr>

<?php
}
?>
</table>

Thanks for the fast reply again though. It was a case of me sitting here and trying what little php I know.

Member Avatar
diafol

OK. Now that you've shown the mockup, I'm more inclined towards a CrossTab query. If I get the time, I'll post some code. Been a while since I had to do that :(

Well my above code worked very well if an engineer was allocated 1 job that day, if 2 jobs were allocated it threw the tables out as now new <tr> was created.

I will post with an updated version if I can fix it.

Member Avatar
diafol

OK the focus seems to be visits
The date down the side needs to get jobs for each engineer.
I'll have a think too.

Yes, an engineer can be allocated several jobs per day and then maybe more than one engineer can be allocated the same job, so 2 engineers per site.

It is currently pulling the correct info but basically adds a second, then third of columns per job. The engineer Nathan has 3 jobs allocated to him on the Monday.
planner2

I am still stabbing away with my current method with slight changes but not getting very far at present, but this needs to be done one way or another so I am here to stay and any input is welcome.

Please bear in mind that my PHP is limited.

Thanks
Woosh

Well, it appears I have done it. I am sure there will be many bugs on this project but this was more of a "NOT WORKING" more than a bug.

<table border="1" cellpadding="5" width="100%">
<tr class="header"><td>&nbsp;</td>
<?php
include ("includes/db.php");

$desired_month = $_GET['month'];#date("n");
$desired_year = "2012";
$num_days = date('t', mktime(0, 0, 0, $desired_month, 1, $desired_year));

$engarray = array();
$result = mysql_query("SELECT * FROM engineers ORDER BY name ASC");
while ($row=mysql_fetch_object($result)) {
    $engarray[] = $row->name;
?>
<td style="text-align:center"><?php echo $row->name;?></td>

<?php
}
    $totalengs = count($engarray);
?>
</tr>
<?php
for ($index = 1; $index <= $num_days; $index++) {
    $daysdate  = mktime(0, 0, 0, date($desired_month)  , date($index), date($desired_year));
    $day = date('D', mktime(0, 0, 0, date($desired_month)  , date($index), date($desired_year)));
?>
    <tr><td class="header" width="115"><?php echo $day?> <?php echo date('d M', $daysdate)?></td>

    <?php
$jobs = "";
    for ($count = 0; $count < $totalengs; $count++) {
        $engname = $engarray[$count];
        #echo $engname;
        #die();

        $engjob = mysql_query("SELECT * FROM job_details WHERE visit_date ='$daysdate' AND engineer LIKE '%$engname%'");

            if ($totaljobs =(mysql_num_rows($engjob) >0 )) {
                while ($rowengjobs = mysql_fetch_object($engjob)) { 
                    $jobs .= 'Job ID : '.$rowengjobs->job_id.'<br>
Site: '.$rowengjobs->site_1.'<br>                    
<a href="uploads/'.$rowengjobs->job_sheet_location.'" id="mb11" class="mb" title="Job: '.$rowengjobs->job_id.'" rel="width:960,height:600">View Instructions</a>
<div class="multiBoxDesc mb11 mbHidden"></div>
<div style="clear:both; height:0; line-height:0; overflow:hidden"></div>';    
                }
                echo "<td class=\"days\">$jobs</td>";
                $jobs = "";  
            } else {
                echo "<td class=\"days\">&nbsp;</td>";    
            }
    }
    ?>
    </tr>

<?php
}
?>
</table>

planner3

Member Avatar
diafol

Here's a version with a single mysql query:

$r = mysql_query("SELECT v.v_date, e.e_id, e.e_name, j.j_id FROM visits AS v INNER JOIN engineers AS e ON v.e_id = e.e_id INNER JOIN jobs AS j ON v.j_id = j.j_id ORDER BY v.v_date, e.e_id, j.j_id");
$e = array();
$table = "<table><thead><tr><th>Date</th>";
while($d=mysql_fetch_assoc($r)){
    $visits[$d['v_date']][$d['e_id']][] = $d['j_id']; 
    if(!in_array($d['e_name'],$e)) $e[$d['e_id']] = $d['e_name'];
}
foreach($e as $eng){
    $table .= "<th>$eng</th>";    
}
$table .= "</tr></thead><tbody><tr>";
$lastdate = "";
foreach($visits as $k=>$v){
    if($lastdate != $k){
        if($lastdate != "")$table .= "</tr><tr>";
        $table .= "<td>{$k}</td>";
        foreach($e as $k=>$eng){
            $table .= (isset($v[$k])) ? "<td>" . implode("<hr />",$v[$k]) . "</td>" : "<td></td>";   
        }
    }
    $lastdate = $k; 
}
$table .= "</tr></tbody></table>";
echo $table;

To me, that looks complicated, but very efficient.

Could I be a pain and ask you to comment what it is all doing please?

Also, I am presuming (which I shouldn't) that that uses a seperate table for job_id against an engineer id?

Thanks in advance
Woosh

Member Avatar
diafol

OK, here's a modified and commented version:

/*  QUERY for visits table joined to engineers table and jobs table
        visits:     v_id (PK), e_id (FK), j_id (FK), v_date
        engineers:  e_id (PK), e_name ...
        jobs:       j_id (PK), jobname ...
    So recordset: v_date | e_id | e_name | j_id
*/
$r = mysql_query("SELECT v.v_date, e.e_id, e.e_name, j.j_id, j.jobname FROM visits AS v INNER JOIN engineers AS e ON v.e_id = e.e_id INNER JOIN jobs AS j ON v.j_id = j.j_id ORDER BY v.v_date, e.e_id, j.j_id");
//create empty engineers array to prevent error when checking later on (in_array line)
$e = array();
//start creating the output text - table html with date heading
$table = "<table><thead><tr><th>Date</th>";
while($d=mysql_fetch_assoc($r)){
    //place data into multidimensional array, e.g. $visits['2012-06-11'][4] = "content of job" (So engineer 4 did job "content..." on 2012-06-11)
    $visits[$d['v_date']][$d['e_id']][] = "JOB ID : {$d['j_id']}<br />SITE : {$d['jobname']}<br /><a href=\"search.php?job={$d['j_id']}\">View Instructions</a>";
    //build engineer array - so if engineer not used in date range, then no column for him/her, e.g. $e[2] = 'Steve' 
    if(!in_array($d['e_name'],$e)) $e[$d['e_id']] = $d['e_name'];
}
//Loop through and add engineers to the table headings
foreach($e as $eng){
    $table .= "<th>$eng</th>";    
}
//start the table body and add to output variable
$table .= "</tr></thead><tbody><tr>";
//checking variable - initialize
$lastdate = "";
//loop through visits array
foreach($visits as $key=>$visit){
    //check to see that the date is new (not the same as previous record)
    if($lastdate != $key){
        //begin new line
        //if first date - create new row
        if($lastdate != "")$table .= "</tr><tr>";
        //add date cell to the row
        $table .= "<td>{$key}</td>";
        //foreach following cell (based on engineer) in the row, see if data exists for it
        foreach($e as $engkey=>$eng){
            //if visits for engineer exist, place into cell, otherwise create empty cell
            $table .= (isset($visit[$engkey])) ? "<td>" . implode("<hr />",$visit[$engkey]) . "</td>" : "<td></td>"; 
        }
    }
    //set checking variable to the last used date
    $lastdate = $key;   
}
//tidy up the html table
$table .= "</tr></tbody></table>";
echo $table;

Many thanks for the commented version. Sadly, due to my basic PHP after trying to use your code I went back to my previous style of coding but using Join as with my other calls. The main thing is that I have the correct info in the right boxes.

I ran into a couple of bugs such as when the date passed, instead of a new day being added to the bottom of the calendar, my calendar just got a day shorter, but fixed now.

Thanks for your help and time diafol and being a sounding board, I am sure I will be back.

Member Avatar
diafol

No problem. The issue gave me a chance to exercise the old grey matter anyway. If you consider the thread solved, please press the Mark Question Solved below.