0

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.

2
Contributors
14
Replies
16
Views
5 Years
Discussion Span
Last Post by diafol
0

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.

Edited by diafol

0

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

0
Double post sorry

Edited by wooshman: Double Post

0

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.

0

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 :(

0

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.

0

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.

0

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

0

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

0

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;
0

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

0

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;

Edited by diafol

0

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.

0

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.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.