Jump to content

Not Calculating Correctly

Moorcam

By Moorcam
in PHP Coding Help

 Share
Go to solution Solved by Barand,

Recommended Posts

Moorcam Advanced Member

Moorcam

Posted
Posted

Hi folks,

Have the following, which is part of a script I am working on to calculate working hours minus break time. However, it's not calculating the correct result.

For example, if I worked 12 hours, take away 1 hour break, should be 11 hours. But it will show it as something like 7 hours.

Here is the part for Monday: 

                <div class="col-lg-12">
                    <div class="card">
                      <div class="card-header"><strong>Add </strong><small>Timesheet <?php 
                      if($message = isset($message) ? $message : ''){
                      printf($message); 
                      }
                      ?></small></div>
                      <div class="card-body card-block">
                        <form role="form" method="post" action="">
                                <input type="hidden" name="new" value="1" />
                            <div class="modal-body">
                          <div class="row form-group">
                              <div class="col-6">
                            <label for="driver_name" class=" form-control-label">Driver Name (required)</label>
                        <input type="text" class="form-control" id="driver_name" name="driver_name" placeholder="" value="<?php echo $_SESSION['NAME']; ?>" required >
                        </div>
                        <div class="col-6">
                        <label for="week_ending" class=" form-control-label">Week Ending (required)</label>
                        <input type="date" class="form-control" id="week_ending" name="week_ending" placeholder="" value="<?php echo isset($_POST['week_ending']) ? $_POST['week_ending'] : '' ?>" required >
                        </div>
                        </div>
<hr>
<h3>Monday</h3>
                        <div class="row form-group">
                        <div class="col-4">
                        <label for="monday_start" class=" form-control-label">Start Time</label>
                        <input type="text" class="form-control" id="monday_start" name="monday_start" placeholder="" value="<?php echo isset($_POST['monday_start']) ? $_POST['monday_start'] : '' ?>" >
                        </div>
                        <div class="col-4">
                        <label for="monday_start" class=" form-control-label">Break Period</label>
                        <input type="text" class="form-control" id="monday_break" name="monday_break" placeholder="Example: 1:30 = 1hr 30min" value="<?php echo isset($_POST['monday_break']) ? $_POST['monday_break'] : '' ?>" >
                        </div>
                        <div class="col-4">
                        <label for="monday_finish" class=" form-control-label">Finish Time</label>
                        <input type="text" class="form-control" id="monday_finish" name="monday_finish" placeholder="" value="<?php echo isset($_POST['monday_finish']) ? $_POST['monday_finish'] : '' ?>" >
                        </div>
                    </div>
                                        <?php
if(isset($_POST['monday_start']) && $_POST['monday_finish'] && $_POST['monday_break'] != "") 
{
$monday_start = new DateTime($_POST['monday_start']);
$monday_finish = new DateTime($_POST['monday_finish']);

list($h, $m) = explode(":", $_POST['monday_break']);
$monday_break = new DateInterval("PT{$h}H{$m}M");

$mondiff = $monday_start->add($monday_break)->diff($monday_finish);

?>
<div class="form-group">
                        <label for="monday" class=" form-control-label">Monday Hours</label>
<input type="text" readonly class="form-control" id="monday_hours" name="monday_hours" value="<?php echo $mondiff->format('%H:%I'); ?>">
</div>
<?php }
?>

Here is the calculating code: 

<?php 
if(isset($_POST['calculate']) != ""){
class times_counter {

    private $hou = 0;
    private $min = 0;
    private $sec = 0;
    private $totaltime = '00:00:00';

    public function __construct($times){
        
        if(is_array($times)){

            $length = sizeof($times);

            for($x=0; $x <= $length; $x++){
                    $split = explode(":", @$times[$x]); 
                    $this->hou += (int)$split[0];
                    $this->min += @$split[1];
                    $this->sec += @$split[2];
            }

            $seconds = $this->sec % 60;
            $minutes = $this->sec / 60;
            $minutes = (integer)$minutes;
            $minutes += $this->min;
            $hours = $minutes / 60;
            $minutes = $minutes % 60;
            $hours = (integer)$hours;
            $hours += $this->hou % 24;
            $this->totaltime = $hours.":".$minutes;
        }
    }

    public function get_total_time(){
        return $this->totaltime;
    }

}
$times = array(
   $mondiff->format('%H:%I'),
   $tuediff->format('%H:%I'),
   $weddiff->format('%H:%I'),
   $thurdiff->format('%H:%I'),
   $fridiff->format('%H:%I'),
   $satdiff->format('%H:%I'),
   $sundiff->format('%H:%I'),
);
$counter = new times_counter($times);

?>
                        <div class="row form-group">
                        <div class="col-6">
                            <div class="form-group">
                        <label for="total_hours" class=" form-control-label">Total Hours (required)</label>
                        <input name="total_hours" readonly class="form-control" value= "<?php echo $counter->get_total_time();  ?>" />
                        </div>
                    </div>
<?php 
}
?>

Can someone with the geneius knowledge have a look and point me to where I am going wrong?

Also, is there a way, to leave a time field blank and not have the above code crack the poops and call an Undefined Array Key on it?

TIA :)

Link to comment
https://forums.phpfreaks.com/topic/313750-not-calculating-correctly/
Share on other sites
requinix Prolific Member

requinix

Posted
Posted
40 minutes ago, Moorcam said:

For example, if I worked 12 hours, take away 1 hour break, should be 11 hours. But it will show it as something like 7 hours.

What exact inputs did you give for the various fields?

40 minutes ago, Moorcam said:

Also, is there a way, to leave a time field blank and not have the above code crack the poops and call an Undefined Array Key on it?

By... writing code that says "if the field is empty then don't do stuff"?

Moorcam Advanced Member

Moorcam

Posted
  • Author
Posted
13 minutes ago, requinix said:

What exact inputs did you give for the various fields?

So for example, for Monday:

Start: 09:00 Break: 1:00 Finish: 17:00

Have also tried 09:00 AM etc

15 minutes ago, requinix said:

By... writing code that says "if the field is empty then don't do stuff"?

Tried it. That's why I asked.

requinix Prolific Member

requinix

Posted
Posted
7 minutes ago, Moorcam said:

So for example, for Monday:

Start: 09:00 Break: 1:00 Finish: 17:00

1 hour ago, Moorcam said:

But it will show it as something like 7 hours.

9am to 5pm minus one hour of break = 7 hours.

 

7 minutes ago, Moorcam said:

Tried it. That's why I asked.

Great. So what was that code?

Moorcam Advanced Member

Moorcam

Posted
  • Author
Posted (edited)
11 minutes ago, requinix said:

9am to 5pm minus one hour of break = 7 hours.

So, I added up all days, which gave a total of 32:00, which included the breaks. Total Hours show as 8:00

11 minutes ago, requinix said:

Great. So what was that code? 

                          <?php if(empty($_POST['monday_start'])) {
?>
<input type="text" class="form-control" id="monday_start" name="monday_start" placeholder="" value="00:00" >
                          <?php
}else{
?>
                        <input type="text" class="form-control" id="monday_start" name="monday_start" placeholder="" value="<?php echo isset($_POST['monday_start']) ? $_POST['monday_start'] : '' ?>" >
                          <?php
}
?>

Still shows:

Undefined array key '$mondiff'...

Edited by Moorcam
requinix Prolific Member

requinix

Posted
Posted
7 hours ago, Moorcam said:

So, I added up all days, which gave a total of 32:00, which included the breaks. Total Hours show as 8:00

That would be due to the % 24 you have in the $hours code.

 

7 hours ago, Moorcam said:

Undefined array key '$mondiff'...

There is no error message "undefined array key". Do you mean "undefined offset"? If it says that the undefined offset was $mondiff then that means somewhere, presumably on the line of the file that was identified by the error message, something that looks like 

$variable['$mondiff']

If you meant "undefined variable" then that would be surprising because the only two places in the code you posted that attempt to use that variable do so by expecting the variable is an object and calling a method on it - something that would not simply report "undefined variable" but instead cause a fatal error/exception about attempting to call a method on a non-object.

Moorcam Advanced Member

Moorcam

Posted
  • Author
Posted (edited)
19 minutes ago, requinix said:

That would be due to the % 24 you have in the $hours code.

 

There is no error message "undefined array key". Do you mean "undefined offset"? If it says that the undefined offset was $mondiff then that means somewhere, presumably on the line of the file that was identified by the error message, something that looks like 

$variable['$mondiff']

If you meant "undefined variable" then that would be surprising because the only two places in the code you posted that attempt to use that variable do so by expecting the variable is an object and calling a method on it - something that would not simply report "undefined variable" but instead cause a fatal error/exception about attempting to call a method on a non-object.

Yeah seems to be ok if times are inserted in 24hr format.

I am on localhost (xampp) so the error shows as Undefined Array Key but loaded onto live server and it shows as Undefined Variable. Bit strange but hey. So is Covid ;)

Edited by Moorcam
Moorcam Advanced Member

Moorcam

Posted
  • Author
Posted

This is getting annoying now.

If I fill in the Start, Break and Finish times for Monday and Tuesday, it shows the correct Total Hours. But, if I add in Wednesday, it goes bonkers.

Here is the full code. If anyone can figure it our, please be my guest. In the meantime, I feel like filling the Recycling Bin with diff this and diff that, 

<?php
include_once('includes/header.php');
if(isset($_POST['submit_timesheet'])){//if the submit button is clicked

    $week_ending = mysqli_real_escape_string($con, $_POST['week_ending']);
    $staff_name = mysqli_real_escape_string($con, $_SESSION["USERNAME"]);
    $monday_start = mysqli_real_escape_string($con, $_POST['monday_start']);
    $monday_finish = mysqli_real_escape_string($con, $_POST['monday_finish']);
    $tuesday_start = mysqli_real_escape_string($con, $_POST['tuesday_start']);
    $tuesday_finish = mysqli_real_escape_string($con, $_POST['tuesday_finish']);
    $wednesday_start = mysqli_real_escape_string($con, $_POST['wednesday_start']);
    $wednesday_finish = mysqli_real_escape_string($con, $_POST['wednesday_finish']);
    $thursday_start = mysqli_real_escape_string($con, $_POST['thursday_start']);
    $thursday_finish = mysqli_real_escape_string($con, $_POST['thursday_finish']);
    $friday_start = mysqli_real_escape_string($con, $_POST['friday_start']);
    $friday_finish = mysqli_real_escape_string($con, $_POST['friday_finish']);
    $saturday_start = mysqli_real_escape_string($con, $_POST['saturday_start']);
    $saturday_finish = mysqli_real_escape_string($con, $_POST['saturday_finish']);
    $sunday_start = mysqli_real_escape_string($con, $_POST['sunday_start']);
    $sunday_finish = mysqli_real_escape_string($con, $_POST['sunday_finish']);
    $total_hours =  mysqli_real_escape_string($con, $_POST['total_hours']);
    $timesheet_comment =  mysqli_real_escape_string($con, $_POST['timesheet_comment']);

    $ins_query="insert into timesheets (`week_ending`, `staff_name`, `monday_start`, `monday_finish`, `tuesday_start`, `tuesday_finish`, `wednesday_start`, `wednesday_finish`, `thursday_start`, `thursday_finish`, `friday_start`, `friday_finish`, `saturday_start`, `saturday_finish`, `sunday_start`, `sunday_finish`, `total_hours`, `timesheet_comment`)
    values (
        '$week_ending', 
        '$staff_name', 
        '$monday_start',
        '$monday_finish',
        '$tuesday_start',
        '$tuesday_finish',
        '$wednesday_start',
        '$wednesday_finish',
        '$thursday_start',
        '$thursday_finish',
        '$friday_start',
        '$friday_finish',
        '$saturday_start',
        '$saturday_finish',
        '$sunday_start',
        '$sunday_finish',
        '$total_hours',
        '$timesheet_comment'
        )";
    mysqli_query($con,$ins_query)
    
    or die(mysqli_error($con));
    if(mysqli_affected_rows($con)== 1 ){
    $message = '<p class="text-success"><i class="fa fa-check"></i> - Timesheet Submitted Successfully</p>';
    }
}

?>
        <!-- Header-->

        <div class="breadcrumbs">
            <div class="col-sm-4">
                <div class="page-header float-left">
                    <div class="page-title">
                        <h1><i class="fa fa-clock"></i> Timesheets</h1>
                    </div>
                </div>
            </div>            <div class="col-sm-8">

            </div>
        </div>

        <div class="content mt-3">
            <div class="animated fadeIn">
                <div class="row">

                 <div class="col-lg-12">
                    <div class="card">
                      <div class="card-header"><strong>Add </strong><small>Timesheet <?php 
                      if($message = isset($message) ? $message : ''){
                      printf($message); 
                      }
                      ?></small></div>
                      <div class="card-body card-block">
                        <form role="form" method="post" action="">
                                <input type="hidden" name="new" value="1" />
                            <div class="modal-body">
                        <p>Please make sure your start times and finish times are correct as it will effect your payments. Start time is start time at Depot and finish time is finish at Depot.<br />For days not worked please place 00:00 in time fields. </p>
                          <div class="row form-group">
                              <div class="col-6">
                            <label for="driver_name" class=" form-control-label">Driver Name (required)</label>
                        <input type="text" class="form-control" id="driver_name" name="driver_name" placeholder="" value="<?php echo $_SESSION['NAME']; ?>" required >
                        </div>
                        <div class="col-6">
                        <label for="week_ending" class=" form-control-label">Week Ending (required)</label>
                        <input type="date" class="form-control" id="week_ending" name="week_ending" placeholder="" value="<?php echo isset($_POST['week_ending']) ? $_POST['week_ending'] : '' ?>" required >
                        </div>
                        </div>
<hr>
<h3>Monday</h3>
                        <div class="row form-group">
                        <div class="col-4">
                        <label for="monday_start" class=" form-control-label">Start Time</label>
                        <input type="text" class="form-control" id="monday_start" name="monday_start" placeholder="" value="<?php echo isset($_POST['monday_start']) ? $_POST['monday_start'] : '' ?>" >
                        </div>
                        <div class="col-4">
                        <label for="monday_break" class=" form-control-label">Break Period</label>
                        <input type="text" class="form-control" id="monday_break" name="monday_break" placeholder="Example: 1:30 = 1hr 30min" value="<?php echo isset($_POST['monday_break']) ? $_POST['monday_break'] : '' ?>" >
                        </div>
                        <div class="col-4">
                        <label for="monday_finish" class=" form-control-label">Finish Time</label>
                        <input type="text" class="form-control" id="monday_finish" name="monday_finish" placeholder="" value="<?php echo isset($_POST['monday_finish']) ? $_POST['monday_finish'] : '' ?>" >
                        </div>
                    </div>
<?php
// MONDAY CALCULATIONS
if(isset($_POST['monday_start']) && $_POST['monday_finish'] && $_POST['monday_break'] != "") 
{
$monday_start = new DateTime($_POST['monday_start']);
$monday_finish = new DateTime($_POST['monday_finish']);

list($h, $m) = explode(":", $_POST['monday_break']);
$monday_break = new DateInterval("PT{$h}H{$m}M");

$mondiff = $monday_start->add($monday_break)->diff($monday_finish);

?>
<div class="form-group">
                        <label for="mondiff" class=" form-control-label">Monday Hours</label>
<input type="text" readonly class="form-control" name="mondiff" value="<?php echo $mondiff->format('%H:%I'); ?>">
</div>
<?php }
?>
<hr>
<h3>Tuesday</h3>
                        <div class="row form-group">
                        <div class="col-4">
                        <label for="tuesday_start" class=" form-control-label">Start</label>
                        <input type="text" class="form-control" id="tuesday_start" name="tuesday_start" placeholder="" value="<?php echo isset($_POST['tuesday_start']) ? $_POST['tuesday_start'] : '' ?>" >
                        </div>
                        <div class="col-4">
                        <label for="tuesday_break" class=" form-control-label">Break</label>
                        <input type="text" class="form-control" id="tuesday_break" name="tuesday_break" placeholder="Example: 1:30 = 1hr 30min" value="<?php echo isset($_POST['tuesday_break']) ? $_POST['tuesday_break'] : '' ?>" >
                        </div>
                        <div class="col-4">
                        <label for="tuesday_finish" class=" form-control-label">Finish</label>
                        <input type="text" class="form-control" id="tuesday_finish" name="tuesday_finish" placeholder="" value="<?php echo isset($_POST['tuesday_finish']) ? $_POST['tuesday_finish'] : '' ?>" >
                        </div>
                    </div>
<?php
// TUESDAY CALCULATIONS
if(isset($_POST['tuesday_start']) && $_POST['tuesday_finish'] && $_POST['tuesday_break'] != "") 
{
$tuesday_start = new DateTime($_POST['tuesday_start']);
$tuesday_finish = new DateTime($_POST['tuesday_finish']);

list($h, $m) = explode(":", $_POST['tuesday_break']);
$tuesday_break = new DateInterval("PT{$h}H{$m}M");

$tuediff = $tuesday_start->add($tuesday_break)->diff($tuesday_finish);

?>
<div class="form-group">
                        <label for="tuediff" class=" form-control-label">Tuesday Hours</label>
<input type="text" readonly class="form-control" name="tuediff" value="<?php echo $tuediff->format('%H:%I'); ?>">
</div>
<?php }
?>
<hr>
<h3>Wednesday</h3>
                        <div class="row form-group">
                        <div class="col-4">
                        <label for="wednesday_start" class=" form-control-label">Start</label>
                        <input type="text" class="form-control" id="wednesday_start" name="wednesday_start" placeholder="" value="<?php echo isset($_POST['wednesday_start']) ? $_POST['wednesday_start'] : '' ?>" >
                        </div>
                        <div class="col-4">
                        <label for="wednesday_break" class=" form-control-label">Break</label>
                        <input type="text" class="form-control" id="wednesday_break" name="wednesday_break" placeholder="Example: 1:30 = 1hr 30min" value="<?php echo isset($_POST['wednesday_break']) ? $_POST['wednesday_break'] : '' ?>" >
                        </div>
                        <div class="col-4">
                        <label for="wednesday_finish" class=" form-control-label">Finish</label>
                        <input type="text" class="form-control" id="wednesday_finish" name="wednesday_finish" placeholder="" value="<?php echo isset($_POST['wednesday_finish']) ? $_POST['wednesday_finish'] : '' ?>" >
                        </div>
                    </div>
<?php
// WEDNESDAY CALCULATIONS
if(isset($_POST['wednesday_start']) && $_POST['wednesday_finish'] && $_POST['wednesday_break'] != "") 
{
$wednesday_start = new DateTime($_POST['wednesday_start']);
$wednesday_finish = new DateTime($_POST['wednesday_finish']);

list($h, $m) = explode(":", $_POST['wednesday_break']);
$wednesday_break = new DateInterval("PT{$h}H{$m}M");

$weddiff = $wednesday_start->add($wednesday_break)->diff($wednesday_finish);

?>
<div class="form-group">
                        <label for="weddiff" class=" form-control-label">Wednesday Hours</label>
<input type="text" readonly class="form-control" name="weddiff" value="<?php echo $weddiff->format('%H:%I'); ?>">
</div>
<?php }
?>
<hr>
<h3>Thursday</h3>
                        <div class="row form-group">
                        <div class="col-4">
                        <label for="thursday_start" class=" form-control-label">Start</label>
                        <input type="text" class="form-control" id="thursday_start" name="thursday_start" placeholder="" value="<?php echo isset($_POST['thursday_start']) ? $_POST['thursday_start'] : '' ?>" >
                        </div>
                        <div class="col-4">
                        <label for="thursday_break" class=" form-control-label">Break</label>
                        <input type="text" class="form-control" id="thursday_break" name="thursday_break" placeholder="Example: 1:30 = 1hr 30min" value="<?php echo isset($_POST['thursday_break']) ? $_POST['thursday_break'] : '' ?>" >
                        </div>
                        <div class="col-4">
                        <label for="thursday_finish" class=" form-control-label">Finish</label>
                        <input type="text" class="form-control" id="thursday_finish" name="thursday_finish" placeholder="" value="<?php echo isset($_POST['thursday_finish']) ? $_POST['thursday_finish'] : '' ?>" >
                        </div>
                    </div>
<?php
// THURSDAY CALCULATIONS
if(isset($_POST['thursday_start']) && $_POST['thursday_finish'] && $_POST['thursday_break'] != "") 
{
$thursday_start = new DateTime($_POST['thursday_start']);
$thursday_finish = new DateTime($_POST['thursday_finish']);

list($h, $m) = explode(":", $_POST['thursday_break']);
$thursday_break = new DateInterval("PT{$h}H{$m}M");

$thurdiff = $thursday_start->add($thursday_break)->diff($thursday_finish);

?>
<div class="form-group">
                        <label for="thurdiff" class=" form-control-label">Thursday Hours</label>
<input type="text" readonly class="form-control" name="thurdiff" value="<?php echo $thurdiff->format('%H:%I'); ?>">
</div>
<?php }
?>
<hr>
<h3>Friday</h3>
                        <div class="row form-group">
                        <div class="col-4">
                        <label for="friday_start" class=" form-control-label">Start</label>
                        <input type="text" class="form-control" id="friday_start" name="friday_start" placeholder="" value="<?php echo isset($_POST['friday_start']) ? $_POST['friday_start'] : '' ?>" >
                        </div>
                        <div class="col-4">
                        <label for="friday_break" class=" form-control-label">Break</label>
                        <input type="text" class="form-control" id="friday_break" name="friday_break" placeholder="Example: 1:30 = 1hr 30min" value="<?php echo isset($_POST['friday_break']) ? $_POST['friday_break'] : '' ?>" >
                        </div>
                        <div class="col-4">
                        <label for="friday_finish" class=" form-control-label">Finish</label>
                        <input type="text" class="form-control" id="friday_finish" name="friday_finish" placeholder="" value="<?php echo isset($_POST['friday_finish']) ? $_POST['friday_finish'] : '' ?>" >
                        </div>
                    </div>
<?php
// FRIDAY CALCULATIONS
if(isset($_POST['friday_start']) && $_POST['friday_finish'] && $_POST['friday_break'] != "") 
{
$friday_start = new DateTime($_POST['friday_start']);
$friday_finish = new DateTime($_POST['friday_finish']);

list($h, $m) = explode(":", $_POST['friday_break']);
$friday_break = new DateInterval("PT{$h}H{$m}M");

$fridiff = $friday_start->add($friday_break)->diff($friday_finish);

?>
<div class="form-group">
                        <label for="fridiff" class=" form-control-label">Friday Hours</label>
<input type="text" readonly class="form-control" name="fridiff" value="<?php echo $fridiff->format('%H:%I'); ?>">
</div>
<?php }
?>
<hr>
<h3>Saturday</h3>
                        <div class="row form-group">
                        <div class="col-4">
                        <label for="saturday_start" class=" form-control-label">Start</label>
                        <input type="text" class="form-control" id="saturday_start" name="saturday_start" placeholder="" value="<?php echo isset($_POST['saturday_start']) ? $_POST['saturday_start'] : '' ?>" >
                        </div>
                        <div class="col-4">
                        <label for="saturday_break" class=" form-control-label">Break</label>
                        <input type="text" class="form-control" id="saturday_break" name="saturday_break" placeholder="Example: 1:30 = 1hr 30min" value="<?php echo isset($_POST['saturday_break']) ? $_POST['saturday_break'] : '' ?>" >
                        </div>
                        <div class="col-4">
                        <label for="saturday_finish" class=" form-control-label">Finish</label>
                        <input type="text" class="form-control" id="saturday_finish" name="saturday_finish" placeholder="" value="<?php echo isset($_POST['saturday_finish']) ? $_POST['saturday_finish'] : '' ?>" >
                        </div>
                    </div>
<?php
// SATURDAY CALCULATIONS
if(isset($_POST['saturday_start']) && $_POST['saturday_finish'] && $_POST['saturday_break'] != "") 
{
$saturday_start = new DateTime($_POST['saturday_start']);
$saturday_finish = new DateTime($_POST['saturday_finish']);

list($h, $m) = explode(":", $_POST['saturday_break']);
$saturday_break = new DateInterval("PT{$h}H{$m}M");

$satdiff = $saturday_start->add($saturday_break)->diff($saturday_finish);

?>
<div class="form-group">
                        <label for="satdiff" class=" form-control-label">Saturday Hours</label>
<input type="text" readonly class="form-control" name="satdiff" value="<?php echo $satdiff->format('%H:%I'); ?>">
</div>
<?php }
?>
<hr>
<h3>Sunday</h3>
                        <div class="row form-group">
                        <div class="col-4">
                        <label for="sunday_start" class=" form-control-label">Start</label>
                        <input type="text" class="form-control" id="sunday_start" name="sunday_start" placeholder="" value="<?php echo isset($_POST['sunday_start']) ? $_POST['sunday_start'] : '' ?>" >
                        </div>
                        <div class="col-4">
                        <label for="sunday_break" class=" form-control-label">Break</label>
                        <input type="text" class="form-control" id="sunday_break" name="sunday_break" placeholder="Example: 1:30 = 1hr 30min" value="<?php echo isset($_POST['sunday_break']) ? $_POST['sunday_break'] : '' ?>" >
                        </div>
                        <div class="col-4">
                        <label for="sunday_finish" class=" form-control-label">Finish</label>
                        <input type="text" class="form-control" id="sunday_finish" name="sunday_finish" placeholder="" value="<?php echo isset($_POST['sunday_finish']) ? $_POST['sunday_finish'] : '' ?>" >
                        </div>
                    </div>
<?php
// SATURDAY CALCULATIONS
if(isset($_POST['sunday_start']) && $_POST['sunday_finish'] && $_POST['sunday_break'] != "") 
{
$sunday_start = new DateTime($_POST['sunday_start']);
$sunday_finish = new DateTime($_POST['sunday_finish']);

list($h, $m) = explode(":", $_POST['sunday_break']);
$sunday_break = new DateInterval("PT{$h}H{$m}M");

$sundiff = $sunday_start->add($sunday_break)->diff($sunday_finish);

?>
<div class="form-group">
                        <label for="sundiff" class=" form-control-label">Sunday Hours</label>
<input type="text" readonly class="form-control" name="sundiff" value="<?php echo $sundiff->format('%H:%I'); ?>">
</div>
<?php }
?>
<hr>

<?php
if(isset($_POST['calculate']) != ""){
class times_counter {

    private $hou = 0;
    private $min = 0;
    private $sec = 0;
    private $totaltime = '00:00:00';

    public function __construct($times){
        
        if(is_array($times)){

            $length = sizeof($times);

            for($x=0; $x <= $length; $x++){
                    $split = explode(":", @$times[$x]); 
                    $this->hou += (int)$split[0];
                    $this->min += @$split[1];
                    $this->sec += @$split[2];
            }

            $seconds = $this->sec % 60;
            $minutes = $this->sec / 60;
            $minutes = (integer)$minutes;
            $minutes += $this->min;
            $hours = $minutes / 60;
            $minutes = $minutes % 60;
            $hours = (integer)$hours;
            $hours += $this->hou % 24;
            $this->totaltime = $hours.":".$minutes;
        }
    }

    public function get_total_time(){
        return $this->totaltime;
    }

}
$times = array(
   $mondiff->format('%H:%I'),
   $tuediff->format('%H:%I'),
   $weddiff->format('%H:%I'),
   $thurdiff->format('%H:%I'),
   $fridiff->format('%H:%I'),
   $satdiff->format('%H:%I'),
   $sundiff->format('%H:%I'),
);
$counter = new times_counter($times);

?>
                        <div class="row form-group">
                        <div class="col-6">
                            <div class="form-group">
                        <label for="total_hours" class=" form-control-label">Total Hours (required)</label>
                        <input name="total_hours" id="total_hours" readonly class="form-control" value= "<?php echo $counter->get_total_time();  ?>" />
                        </div>
                    </div>
<?php 
}
?>
                        <div class="col-6">
                            <div class="form-group">
                        <label for="timesheet_comment" class=" form-control-label">Comment (optional)</label>
                        <input type="text" class="form-control" id="timesheet_comment" name="timesheet_comment" placeholder="" value="<?php echo isset($_POST['timesheet_comment']) ? $_POST['timesheet_comment'] : '' ?>" >
                        </div>
                        </div>
                    </div>

                        <div class="modal-footer">
                        <button type="submit" name="calculate" id="calculate" class="btn btn-primary">Calculate Hours</button> <button type="submit" name="submit_timesheet" id="submit_timesheet" class="btn btn-primary">Submit Timesheet</button>
                        </div>
                        
                            </form>
                  </div>
                </div>
            </div><!-- .animated -->
        </div><!-- .content -->


    </div><!-- /#right-panel -->

    <!-- Right Panel -->


    <script src="assets/js/vendor/jquery-2.1.4.min.js"></script>
    <script src="assets/js/popper.min.js"></script>
    <script src="assets/js/plugins.js"></script>
    <script src="assets/js/main.js"></script>


    <script src="assets/js/lib/data-table/datatables.min.js"></script>
    <script src="assets/js/lib/data-table/dataTables.bootstrap.min.js"></script>
    <script src="assets/js/lib/data-table/dataTables.buttons.min.js"></script>
    <script src="assets/js/lib/data-table/buttons.bootstrap.min.js"></script>
    <script src="assets/js/lib/data-table/jszip.min.js"></script>
    <script src="assets/js/lib/data-table/pdfmake.min.js"></script>
    <script src="assets/js/lib/data-table/vfs_fonts.js"></script>
    <script src="assets/js/lib/data-table/buttons.html5.min.js"></script>
    <script src="assets/js/lib/data-table/buttons.print.min.js"></script>
    <script src="assets/js/lib/data-table/buttons.colVis.min.js"></script>
    <script src="assets/js/lib/data-table/datatables-init.js"></script>


</body>
</html>

Have a good day/evening/night/morning etc....

  • Solution
Barand Prolific Member

Barand

Posted
  • Solution
Posted

Never store data with multiple values like this 

'$week_ending', 
        '$staff_name', 
        '$monday_start',
        '$monday_finish',
        '$tuesday_start',
        '$tuesday_finish',
        '$wednesday_start',
        '$wednesday_finish',
        '$thursday_start',
        '$thursday_finish',
        '$friday_start',
        '$friday_finish',
        '$saturday_start',
        '$saturday_finish',
        '$sunday_start',
        '$sunday_finish',
        '$total_hours',
        '$timesheet_comment'

You should always normalize your data when using an RDBMS. Once that's done, the processing can become a whole lot simpler.

For example, if you table were like this... 

TABLE: driver                                                                                          TABLE: driver_timesheet
+-----------+-----------+----------+                                                                   +--------------+-----------+-------------+-------------------+
| driver_id | firstname | lastname |                                                                   | timesheet_id | driver_id | week_ending | timesheet_comment |
+-----------+-----------+----------+                                                                   +--------------+-----------+-------------+-------------------+
| 1         | Peter     | Dowt     |                                                                   | 1            | 1         | 2021-09-05  | 4-day week        |
| 2         | Laura     | Norder   | ----------------------------------------------------------------< | 2            | 2         | 2021-09-05  | No Saturday work  |
| 3         | Tom       | DiCanari |                                                                   | 3            | 3         | 2021-09-05  | No Sunday work    |
+-----------+-----------+----------+                                                                   +--------------+-----------+-------------+-------------------+
                                                                                                              |
                                                                                                              |
                         +------------------------------------------------------------------------------------+
TABLE: driver_time       |
+----------------+--------------+-------------+------------+----------+------------+
| driver_time_id | timesheet_id | driver_date | time_start | time_end | break_time |
+----------------+--------------+-------------+------------+----------+------------+
| 1              | 1            | 2021-08-30  | 08:15:00   | 18:05:00 | 01:20:00   |
| 2              | 1            | 2021-08-31  | 08:30:00   | 17:35:00 | 01:10:00   |
| 3              | 1            | 2021-09-01  | 08:20:00   | 18:00:00 | 01:00:00   |
| 4              | 1            | 2021-09-05  | 08:00:00   | 17:55:00 | 01:10:00   |

| 19             | 2            | 2021-08-30  | 08:16:00   | 17:17:00 | 01:19:00   |
| 20             | 2            | 2021-08-31  | 08:19:00   | 17:25:00 | 01:06:00   |
| 21             | 2            | 2021-09-01  | 08:18:00   | 17:41:00 | 01:24:00   |
| 22             | 2            | 2021-09-02  | 08:07:00   | 17:26:00 | 01:20:00   |
| 23             | 2            | 2021-09-03  | 08:09:00   | 17:21:00 | 01:01:00   |
| 25             | 2            | 2021-09-05  | 08:06:00   | 17:46:00 | 01:24:00   |

| 26             | 3            | 2021-08-30  | 08:23:00   | 17:38:00 | 01:02:00   |
| 27             | 3            | 2021-08-31  | 08:04:00   | 17:23:00 | 01:21:00   |
| 28             | 3            | 2021-09-01  | 08:21:00   | 17:30:00 | 01:13:00   |
| 29             | 3            | 2021-09-02  | 08:22:00   | 17:36:00 | 01:01:00   |
| 30             | 3            | 2021-09-03  | 08:23:00   | 17:32:00 | 01:09:00   |
| 31             | 3            | 2021-09-04  | 08:21:00   | 17:05:00 | 01:00:00   |
+----------------+--------------+-------------+------------+----------+------------+

then all you need to do is write the data from your input form to the tables.

To get the daily hours worked ... 

        SELECT 
              concat(firstname,' ', lastname) as name
            , dayname(driver_date) as day
            , date_format(driver_date, '%b %d') as date
            , time_start
            , time_end
            , break_time
            , timediff(timediff(time_end, time_start), break_time) as hrs_worked
        FROM driver_time
             JOIN driver_timesheet USING (timesheet_id)
             JOIN driver USING (driver_id)
        WHERE week_ending = '2021-09-05'
        ORDER BY driver_id, driver_date;

        +--------------+-----------+--------+------------+----------+------------+------------+
        | name         | day       | date   | time_start | time_end | break_time | hrs_worked |
        +--------------+-----------+--------+------------+----------+------------+------------+
        | Peter Dowt   | Monday    | Aug 30 | 08:15:00   | 18:05:00 | 01:20:00   | 08:30:00   |
        | Peter Dowt   | Tuesday   | Aug 31 | 08:30:00   | 17:35:00 | 01:10:00   | 07:55:00   |
        | Peter Dowt   | Wednesday | Sep 01 | 08:20:00   | 18:00:00 | 01:00:00   | 08:40:00   |
        | Peter Dowt   | Sunday    | Sep 05 | 08:00:00   | 17:55:00 | 01:10:00   | 08:45:00   |

        | Laura Norder | Monday    | Aug 30 | 08:16:00   | 17:17:00 | 01:19:00   | 07:42:00   |
        | Laura Norder | Tuesday   | Aug 31 | 08:19:00   | 17:25:00 | 01:06:00   | 08:00:00   |
        | Laura Norder | Wednesday | Sep 01 | 08:18:00   | 17:41:00 | 01:24:00   | 07:59:00   |
        | Laura Norder | Thursday  | Sep 02 | 08:07:00   | 17:26:00 | 01:20:00   | 07:59:00   |
        | Laura Norder | Friday    | Sep 03 | 08:09:00   | 17:21:00 | 01:01:00   | 08:11:00   |
        | Laura Norder | Sunday    | Sep 05 | 08:06:00   | 17:46:00 | 01:24:00   | 08:16:00   |

        | Tom DiCanari | Monday    | Aug 30 | 08:23:00   | 17:38:00 | 01:02:00   | 08:13:00   |
        | Tom DiCanari | Tuesday   | Aug 31 | 08:04:00   | 17:23:00 | 01:21:00   | 07:58:00   |
        | Tom DiCanari | Wednesday | Sep 01 | 08:21:00   | 17:30:00 | 01:13:00   | 07:56:00   |
        | Tom DiCanari | Thursday  | Sep 02 | 08:22:00   | 17:36:00 | 01:01:00   | 08:13:00   |
        | Tom DiCanari | Friday    | Sep 03 | 08:23:00   | 17:32:00 | 01:09:00   | 08:00:00   |
        | Tom DiCanari | Saturday  | Sep 04 | 08:21:00   | 17:05:00 | 01:00:00   | 07:44:00   |
        +--------------+-----------+--------+------------+----------+------------+------------+

and to get the weekly hours for each driver for payroll ... 

        SELECT 
               date_format(week_ending, '%d %b %Y') as wk_ending
             , concat(firstname,' ', lastname) as name
             , round(sum(time_to_sec( timediff(timediff(time_end, time_start), break_time) ))/3600, 2) as tot_hrs_dec
        FROM driver_time
             JOIN driver_timesheet USING (timesheet_id)
             JOIN driver USING (driver_id)
        WHERE week_ending = '2021-09-05'
        GROUP BY week_ending, driver_id

        +-------------+--------------+-------------+
        | wk_ending   | name         | tot_hrs_dec |
        +-------------+--------------+-------------+
        | 05 Sep 2021 | Peter Dowt   | 33.83       |
        | 05 Sep 2021 | Laura Norder | 48.12       |
        | 05 Sep 2021 | Tom DiCanari | 48.07       |
        +-------------+--------------+-------------+

All your calculations done with a couple of queries.

Moorcam Advanced Member

Moorcam

Posted
  • Author
Posted
4 minutes ago, Barand said:

Never store data with multiple values like this 

'$week_ending', 
        '$staff_name', 
        '$monday_start',
        '$monday_finish',
        '$tuesday_start',
        '$tuesday_finish',
        '$wednesday_start',
        '$wednesday_finish',
        '$thursday_start',
        '$thursday_finish',
        '$friday_start',
        '$friday_finish',
        '$saturday_start',
        '$saturday_finish',
        '$sunday_start',
        '$sunday_finish',
        '$total_hours',
        '$timesheet_comment'

You should always normalize your data when using an RDBMS. Once that's done, the processing can become a whole lot simpler.

For example, if you table were like this... 

TABLE: driver                                                                                          TABLE: driver_timesheet
+-----------+-----------+----------+                                                                   +--------------+-----------+-------------+-------------------+
| driver_id | firstname | lastname |                                                                   | timesheet_id | driver_id | week_ending | timesheet_comment |
+-----------+-----------+----------+                                                                   +--------------+-----------+-------------+-------------------+
| 1         | Peter     | Dowt     |                                                                   | 1            | 1         | 2021-09-05  | 4-day week        |
| 2         | Laura     | Norder   | ----------------------------------------------------------------< | 2            | 2         | 2021-09-05  | No Saturday work  |
| 3         | Tom       | DiCanari |                                                                   | 3            | 3         | 2021-09-05  | No Sunday work    |
+-----------+-----------+----------+                                                                   +--------------+-----------+-------------+-------------------+
                                                                                                              |
                                                                                                              |
                         +------------------------------------------------------------------------------------+
TABLE: driver_time       |
+----------------+--------------+-------------+------------+----------+------------+
| driver_time_id | timesheet_id | driver_date | time_start | time_end | break_time |
+----------------+--------------+-------------+------------+----------+------------+
| 1              | 1            | 2021-08-30  | 08:15:00   | 18:05:00 | 01:20:00   |
| 2              | 1            | 2021-08-31  | 08:30:00   | 17:35:00 | 01:10:00   |
| 3              | 1            | 2021-09-01  | 08:20:00   | 18:00:00 | 01:00:00   |
| 4              | 1            | 2021-09-05  | 08:00:00   | 17:55:00 | 01:10:00   |

| 19             | 2            | 2021-08-30  | 08:16:00   | 17:17:00 | 01:19:00   |
| 20             | 2            | 2021-08-31  | 08:19:00   | 17:25:00 | 01:06:00   |
| 21             | 2            | 2021-09-01  | 08:18:00   | 17:41:00 | 01:24:00   |
| 22             | 2            | 2021-09-02  | 08:07:00   | 17:26:00 | 01:20:00   |
| 23             | 2            | 2021-09-03  | 08:09:00   | 17:21:00 | 01:01:00   |
| 25             | 2            | 2021-09-05  | 08:06:00   | 17:46:00 | 01:24:00   |

| 26             | 3            | 2021-08-30  | 08:23:00   | 17:38:00 | 01:02:00   |
| 27             | 3            | 2021-08-31  | 08:04:00   | 17:23:00 | 01:21:00   |
| 28             | 3            | 2021-09-01  | 08:21:00   | 17:30:00 | 01:13:00   |
| 29             | 3            | 2021-09-02  | 08:22:00   | 17:36:00 | 01:01:00   |
| 30             | 3            | 2021-09-03  | 08:23:00   | 17:32:00 | 01:09:00   |
| 31             | 3            | 2021-09-04  | 08:21:00   | 17:05:00 | 01:00:00   |
+----------------+--------------+-------------+------------+----------+------------+

then all you need to do is write the data from your input form to the tables.

To get the daily hours worked ... 

        SELECT 
              concat(firstname,' ', lastname) as name
            , dayname(driver_date) as day
            , date_format(driver_date, '%b %d') as date
            , time_start
            , time_end
            , break_time
            , timediff(timediff(time_end, time_start), break_time) as hrs_worked
        FROM driver_time
             JOIN driver_timesheet USING (timesheet_id)
             JOIN driver USING (driver_id)
        WHERE week_ending = '2021-09-05'
        ORDER BY driver_id, driver_date;

        +--------------+-----------+--------+------------+----------+------------+------------+
        | name         | day       | date   | time_start | time_end | break_time | hrs_worked |
        +--------------+-----------+--------+------------+----------+------------+------------+
        | Peter Dowt   | Monday    | Aug 30 | 08:15:00   | 18:05:00 | 01:20:00   | 08:30:00   |
        | Peter Dowt   | Tuesday   | Aug 31 | 08:30:00   | 17:35:00 | 01:10:00   | 07:55:00   |
        | Peter Dowt   | Wednesday | Sep 01 | 08:20:00   | 18:00:00 | 01:00:00   | 08:40:00   |
        | Peter Dowt   | Sunday    | Sep 05 | 08:00:00   | 17:55:00 | 01:10:00   | 08:45:00   |

        | Laura Norder | Monday    | Aug 30 | 08:16:00   | 17:17:00 | 01:19:00   | 07:42:00   |
        | Laura Norder | Tuesday   | Aug 31 | 08:19:00   | 17:25:00 | 01:06:00   | 08:00:00   |
        | Laura Norder | Wednesday | Sep 01 | 08:18:00   | 17:41:00 | 01:24:00   | 07:59:00   |
        | Laura Norder | Thursday  | Sep 02 | 08:07:00   | 17:26:00 | 01:20:00   | 07:59:00   |
        | Laura Norder | Friday    | Sep 03 | 08:09:00   | 17:21:00 | 01:01:00   | 08:11:00   |
        | Laura Norder | Sunday    | Sep 05 | 08:06:00   | 17:46:00 | 01:24:00   | 08:16:00   |

        | Tom DiCanari | Monday    | Aug 30 | 08:23:00   | 17:38:00 | 01:02:00   | 08:13:00   |
        | Tom DiCanari | Tuesday   | Aug 31 | 08:04:00   | 17:23:00 | 01:21:00   | 07:58:00   |
        | Tom DiCanari | Wednesday | Sep 01 | 08:21:00   | 17:30:00 | 01:13:00   | 07:56:00   |
        | Tom DiCanari | Thursday  | Sep 02 | 08:22:00   | 17:36:00 | 01:01:00   | 08:13:00   |
        | Tom DiCanari | Friday    | Sep 03 | 08:23:00   | 17:32:00 | 01:09:00   | 08:00:00   |
        | Tom DiCanari | Saturday  | Sep 04 | 08:21:00   | 17:05:00 | 01:00:00   | 07:44:00   |
        +--------------+-----------+--------+------------+----------+------------+------------+

and to get the weekly hours for each driver for payroll ... 

        SELECT 
               date_format(week_ending, '%d %b %Y') as wk_ending
             , concat(firstname,' ', lastname) as name
             , round(sum(time_to_sec( timediff(timediff(time_end, time_start), break_time) ))/3600, 2) as tot_hrs_dec
        FROM driver_time
             JOIN driver_timesheet USING (timesheet_id)
             JOIN driver USING (driver_id)
        WHERE week_ending = '2021-09-05'
        GROUP BY week_ending, driver_id

        +-------------+--------------+-------------+
        | wk_ending   | name         | tot_hrs_dec |
        +-------------+--------------+-------------+
        | 05 Sep 2021 | Peter Dowt   | 33.83       |
        | 05 Sep 2021 | Laura Norder | 48.12       |
        | 05 Sep 2021 | Tom DiCanari | 48.07       |
        +-------------+--------------+-------------+

All your calculations done with a couple of queries.

Thanks mate.

Will have a look at all this in the next day or so. It's like 1:30am now and my brain has literally fried. :D

Moorcam Advanced Member

Moorcam

Posted
  • Author
Posted (edited)

Afternoon,

Now that I have refreshed the brain cells, which don't work anyway, I decided to have a crack at this.

At the moment I am trying to get data as below (suggested by your good self), and getting an error of Parse error: syntax error, unexpected identifier "concat"

Here is the code I am using to get the data, which is just a modify of what Barand posted above: 

<?php
        SELECT 
              concat(firstname,' ', lastname) as name
            , dayname(staff_date) as day
            , date_format(staff_date, '%b %d') as date
            , time_start
            , time_end
            , break_time
            , timediff(timediff(time_end, time_start), break_time) as hrs_worked
        FROM driver_time
             JOIN timesheets USING (timesheet_id)
             JOIN users USING (user_id)
        WHERE week_ending = date
        ORDER BY user_id, staff_date;
?>

Doing this the way Barand suggested, I think is definitely the way to go. If I can get past this I am having Guinness tonight ;)

Edited by Moorcam
Moorcam Advanced Member

Moorcam

Posted
  • Author
Posted

Ok, have tried what was suggested and just getting errors:

Warning: Attempt to read property "num_rows" on bool in D:\xampp\htdocs\protour\admin\timesheets.php on line 60

All tables have a value.

Have also tried the following with same error: 

$sql = "SELECT timesheets.*, users.*, st.*
        FROM 
        staff_time AS st
        LEFT JOIN users
        LEFT JOIN timesheets
        ON st.time_id ON users.user_id = timesheets.timesheet_id
        WHERE timesheet_id = $timesheet_id LIMIT 1";
$result = $con->query($sql);

if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {

I give up

Moorcam Advanced Member

Moorcam

Posted
  • Author
Posted
25 minutes ago, requinix said: 
        LEFT JOIN users
        LEFT JOIN timesheets
        ON st.time_id ON users.user_id = timesheets.timesheet_id

Want to give that another shot?

I can't get it. Been fiddling, moving, swapping, searching google, trying what other people have tried, and my Irish Brain Syndrome, coupled with the lack thereof said Guinness fix, results in a Nadda approach. 😕

Moorcam Advanced Member

Moorcam

Posted
  • Author
Posted
1 hour ago, requinix said: 
        LEFT JOIN users
        LEFT JOIN timesheets
        ON st.time_id ON users.user_id = timesheets.timesheet_id

Want to give that another shot? 

$sql = "SELECT timesheets.*, users.*, staff_time.*
        FROM 
        timesheets 
        INNER JOIN users 
        ON users.user_id = timesheets.user_id
        INNER JOIN staff_time
        ON staff_time.timesheet_id = timesheet.timesheet_id";
$result = $con->query($sql);

if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {

Still no good.

Barand Prolific Member

Barand

Posted
Posted

Given the adage "Test data is that data for which the code works", here is the data I used for the queries I posted 

-- MySQL dump 10.13  Distrib 8.0.25, for Win64 (x86_64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version	5.7.21-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `driver`
--

DROP TABLE IF EXISTS `driver`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `driver` (
  `driver_id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(45) DEFAULT NULL,
  `lastname` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`driver_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `driver`
--

LOCK TABLES `driver` WRITE;
/*!40000 ALTER TABLE `driver` DISABLE KEYS */;
INSERT INTO `driver` VALUES (1,'Peter','Dowt'),(2,'Laura','Norder'),(3,'Tom','DiCanari'),(4,'Scott','Chegg'),(5,'Polly','Vinyl'),(6,'Polly','Styrene'),(7,'Tom','Catt');
/*!40000 ALTER TABLE `driver` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `driver_time`
--

DROP TABLE IF EXISTS `driver_time`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `driver_time` (
  `driver_time_id` int(11) NOT NULL AUTO_INCREMENT,
  `timesheet_id` int(11) DEFAULT NULL,
  `driver_date` date DEFAULT NULL,
  `time_start` time DEFAULT NULL,
  `time_end` time DEFAULT NULL,
  `break_time` time DEFAULT NULL,
  PRIMARY KEY (`driver_time_id`),
  KEY `idx_driver_time_timesheet_id` (`timesheet_id`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `driver_time`
--

LOCK TABLES `driver_time` WRITE;
/*!40000 ALTER TABLE `driver_time` DISABLE KEYS */;
INSERT INTO `driver_time` VALUES (1,1,'2021-08-30','08:15:00','18:05:00','01:20:00'),(2,1,'2021-08-31','08:30:00','17:35:00','01:10:00'),(3,1,'2021-09-01','08:20:00','18:00:00','01:00:00'),(4,1,'2021-09-05','08:00:00','17:55:00','01:10:00'),(19,2,'2021-08-30','08:16:00','17:17:00','01:19:00'),(20,2,'2021-08-31','08:19:00','17:25:00','01:06:00'),(21,2,'2021-09-01','08:18:00','17:41:00','01:24:00'),(22,2,'2021-09-02','08:07:00','17:26:00','01:20:00'),(23,2,'2021-09-03','08:09:00','17:21:00','01:01:00'),(25,2,'2021-09-05','08:06:00','17:46:00','01:24:00'),(26,3,'2021-08-30','08:23:00','17:38:00','01:02:00'),(27,3,'2021-08-31','08:04:00','17:23:00','01:21:00'),(28,3,'2021-09-01','08:21:00','17:30:00','01:13:00'),(29,3,'2021-09-02','08:22:00','17:36:00','01:01:00'),(30,3,'2021-09-03','08:23:00','17:32:00','01:09:00'),(31,3,'2021-09-04','08:21:00','17:05:00','01:00:00');
/*!40000 ALTER TABLE `driver_time` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `driver_timesheet`
--

DROP TABLE IF EXISTS `driver_timesheet`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `driver_timesheet` (
  `timesheet_id` int(11) NOT NULL AUTO_INCREMENT,
  `driver_id` int(11) DEFAULT NULL,
  `week_ending` date DEFAULT NULL,
  `timesheet_comment` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`timesheet_id`),
  KEY `idx_driver_timesheet_driver_id` (`driver_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `driver_timesheet`
--

LOCK TABLES `driver_timesheet` WRITE;
/*!40000 ALTER TABLE `driver_timesheet` DISABLE KEYS */;
INSERT INTO `driver_timesheet` VALUES (1,1,'2021-09-05','4-day week'),(2,2,'2021-09-05',NULL),(3,3,'2021-09-05',NULL);
/*!40000 ALTER TABLE `driver_timesheet` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2021-09-18 10:44:22

Moorcam Advanced Member

Moorcam

Posted
  • Author
Posted

Got it: 

$sql = "SELECT timesheets.*, users.*, staff_time.*,
timediff(timediff(time_end, time_start), break_time) as hrs_worked
        FROM 
        timesheets 
        LEFT JOIN users
        ON timesheets.user_id = users.id
        LEFT JOIN staff_time
        ON timesheets.timesheet_id = staff_time.timesheet_id";
$result = $con->query($sql);

if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {

Thanks guys for your patience etc. Had never heard of JOIN this and that until it was mentioned on these forums a couple months ago :D

Barand Prolific Member

Barand

Posted
Posted
1 hour ago, Moorcam said:

Had never heard of JOIN this and that until it was mentioned on these forums a couple months ago :D

I think you'll find they were mentioned a little earlier than that  :)...

image.png.a4752efd69e9f4ceccae8be4bb7bfa4d.png

... and I doubt that is the earliest.

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.