Hours Worked Do Not WERK

[Moorcam]

Howdy guys,

First of all, thanks so much for your help so far, especially yesterday. My wife and kids would tell you I was ropeable.

Following Barand's advice and idea on my previous post, I have decided to break things up and put them into separate tables to make it easier to process.

All is working fine but Hours Worked do not WERK (work), I am trying an accent

Here's the code that called the timesheet submitted by a driver for one day. It is showing to the submitter based on their session id.

$session = $_SESSION['ID'];
$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.sheet_id
        WHERE id=$session";
$result = $con->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {

Here is where I am trying to display the hours worked:

<td><?php echo $row['hrs_worked']; ?></td>

But nothing shows. No errors, nothing in Console either.   I am obviously doing this wrong. As mentioned in my last post, I am new to this JOIN thingy-ma-jig.

I know this following piece is meant to calculate between start, end and break. Not sure if that's happening either.

timediff(timediff(time_end, time_start), break_time) as hrs_worked

Any guidance, as always is appreciated.

 

[Moorcam]

if(isset($row['time_start']) && $row['time_end'] && $row['break_time'] != "") 
{
$time_start = new DateTime($row['time_start']);
$time_end = new DateTime($row['time_end']);

list($h, $m) = explode(":", $row['break_time']);
$break_time = new DateInterval("PT{$h}H{$m}M");
   $time_worked = $time_start->add($break_time)->diff($time_end);
echo $time_worked;
}

Have also tried this and nothing.

[Barand]

Put this line immediately before your line that creates your mysqli connection

    mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT); 

That will force mysqli to report any erors it encounters.

[Barand]

4 hours ago, Moorcam said:

I am new to this JOIN thingy-ma-jig.

Follow the link in my sig. There's a section in the tutorials dedicated to JOINS

[Moorcam]

2 hours ago, Barand said:

Follow the link in my sig. There's a section in the tutorials dedicated to JOINS

Thanks mate. Great reading. Very informative.

It appears my query is not getting the data from stafftime. Populating from the other two tables fine but anything from stafftime is just not happening.

 

$session = $_SESSION['ID'];
$sql = "SELECT timesheets.user_id, timesheets.timesheet_id, timesheets.week_ending
, users.id, users.name
, stafftime.time_id, stafftime.sheet_id, stafftime.staff_date, stafftime.time_end, stafftime.time_start, stafftime.break_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 stafftime
        ON stafftime.sheet_id = timesheets.timesheet_id
        WHERE id=$session";
$result = $con->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {

 

[Barand]

Did you read the bit in the JOIN tutorial about LEFT JOIN to a table (user in your case) and then referencing a column from that table in the WHERE clause?

[Barand]

Do you want to post a dump of your data so I can run your query at my end?

Edited September 19, 2021 by Barand

[Moorcam]

Here is a var_dump of the $result

object(mysqli_result)#2 (5) { ["current_field"]=> int(0) ["field_count"]=> int(12) ["lengths"]=> array(12) { [0]=> int(1) [1]=> int(4) [2]=> int(19) [3]=> int(1) [4]=> int(7) [5]=> int(0) [6]=> int(0) [7]=> int(0) [8]=> int(0) [9]=> int(0) [10]=> int(0) [11]=> int(0) } ["num_rows"]=> int(3) ["type"]=> int(0) } object(mysqli_result)#2 (5) { ["current_field"]=> int(0) ["field_count"]=> int(12) ["lengths"]=> array(12) { [0]=> int(1) [1]=> int(4) [2]=> int(19) [3]=> int(1) [4]=> int(7) [5]=> int(0) [6]=> int(0) [7]=> int(0) [8]=> int(0) [9]=> int(0) [10]=> int(0) [11]=> int(0) } ["num_rows"]=> int(3) ["type"]=> int(0) } object(mysqli_result)#2 (5) { ["current_field"]=> int(0) ["field_count"]=> int(12) ["lengths"]=> array(12) { [0]=> int(1) [1]=> int(4) [2]=> int(19) [3]=> int(1) [4]=> int(7) [5]=> int(0) [6]=> int(0) [7]=> int(0) [8]=> int(0) [9]=> int(0) [10]=> int(0) [11]=> int(0) } ["num_rows"]=> int(3) ["type"]=> int(0) }

And a var_dump of $sql

string(494) "SELECT timesheets.user_id, timesheets.timesheet_id, timesheets.week_ending , users.id, users.name , stafftime.time_id, stafftime.sheet_id, stafftime.staff_date, stafftime.time_end, stafftime.time_start, stafftime.break_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 stafftime ON stafftime.sheet_id = timesheets.timesheet_id WHERE id=9" string(494) "SELECT timesheets.user_id, timesheets.timesheet_id, timesheets.week_ending , users.id, users.name , stafftime.time_id, stafftime.sheet_id, stafftime.staff_date, stafftime.time_end, stafftime.time_start, stafftime.break_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 stafftime ON stafftime.sheet_id = timesheets.timesheet_id WHERE id=9" string(494) "SELECT timesheets.user_id, timesheets.timesheet_id, timesheets.week_ending , users.id, users.name , stafftime.time_id, stafftime.sheet_id, stafftime.staff_date, stafftime.time_end, stafftime.time_start, stafftime.break_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 stafftime ON stafftime.sheet_id = timesheets.timesheet_id WHERE id=9"

I really appreciate your time and effort man.

Edited September 19, 2021 by Moorcam

[Barand]

4 minutes ago, Moorcam said:

Here is a var_dump of the $result

Not what I asked for. There is no way that enables me to run your query on your data.

I wanted something similar to what I posted in your previous topic...

 

Edited September 19, 2021 by Barand

[Moorcam]

8 minutes ago, Barand said:

Not what I asked for. There is no way that enables me to run your query on your data.

I wanted something similar to what I posted in your previous topic...

 

*Red Faced*

Sorry. I thought you meant a Var dump

Here you go. Hope this is what you are after:

CREATE TABLE `stafftime` (
  `time_id` int(11) NOT NULL,
  `sheet_id` int(11) NOT NULL,
  `staff_date` date NOT NULL,
  `time_start` varchar(20) NOT NULL,
  `time_end` varchar(20) NOT NULL,
  `break_time` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `stafftime`
--

INSERT INTO `stafftime` (`time_id`, `sheet_id`, `staff_date`, `time_start`, `time_end`, `break_time`) VALUES
(1040, 141826, '2021-09-19', '06:00', '17:00', '1:00'),
(1041, 671766, '2021-09-19', '09:30', '17:30', '1:00'),
(1042, 1080, '2021-09-19', '09:30', '17:30', '1:00');

-- --------------------------------------------------------

--
-- Table structure for table `timesheets`
--

CREATE TABLE `timesheets` (
  `timesheet_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `week_ending` datetime NOT NULL,
  `timesheet_comment` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `timesheets`
--

INSERT INTO `timesheets` (`timesheet_id`, `user_id`, `week_ending`, `timesheet_comment`) VALUES
(1050, 9, '2021-10-10 00:00:00', 'Just a test'),
(1051, 9, '2021-09-19 00:00:00', 'Just a test'),
(1052, 9, '2021-09-19 00:00:00', 'Just a test');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `stafftime`
--
ALTER TABLE `stafftime`
  ADD PRIMARY KEY (`time_id`),
  ADD KEY `timesheet_id` (`sheet_id`);

--
-- Indexes for table `timesheets`
--
ALTER TABLE `timesheets`
  ADD PRIMARY KEY (`timesheet_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `stafftime`
--
ALTER TABLE `stafftime`
  MODIFY `time_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1043;

--
-- AUTO_INCREMENT for table `timesheets`
--
ALTER TABLE `timesheets`
  MODIFY `timesheet_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1053;
COMMIT;

 

[Barand]

Received and loaded. Thanks.

For each timesheet record you should have up to 7 (1 for each day of the week) records in stafftime. These stafftime records for the week should have a sheet_id value which matches the timesheet id of the parent timesheet record.

You stafftime.sheet_id values match none of the timesheet ids.

[Moorcam]

1 minute ago, Barand said:

Received and loaded. Thanks.

For each timesheet record you should have up to 7 (1 for each day of the week) records in stafftime. These stafftime records for the week should have a sheet_id value which matches the timesheet id of the parent timesheet record.

You stafftime.sheet_id values match none of the timesheet ids.

That's what I was wondering how I do. How do I get those IDs to be the same in different tables? I did search Google about that but could find nothing.

[Barand]

How you do it depends on how you are entering the data. The are basically two approaches...

1. Create the timesheet record first then, each day add that days stafftime record
2. Have a form where you enter the timesheet header data and all days' times at once for a user.

If you use the first, have a dropdown so the user can select the timesheet record id and write that id with the day's times into stafftime.

Doing it the second way, on posting the form you would first write the timesheet record (id would be an autoincrement column) then call lastInserId() to get the id of that new record. You then insert the time records with that last inserted id as the sheet_id.

Edited September 19, 2021 by Barand

[Barand]

BTW, the time_start, time_end, break_time columns in your stafftime table should be type TIME, not VARCHAR.

[Moorcam]

10 minutes ago, Barand said:

How you do it depends on how you are entering the data. The are basically two approaches...

1. Create the timesheet record first then, each day add that days stafftime record
2. Have a form where you enter the timesheet header data and all days' ties at once for a user.

If you use the first, have a dropdown so the user can select the timesheet record id and write that id with the day's times into stafftime.

Doing it the second way, on posting the form you would first write the timesheet record (id would be an autoincrement column) then call lastInserId() to get the id of that new record. You then insert the time records with that last inserted id as the sheet_id.

Working. Mate you are a legend.

$sql = "INSERT INTO timesheets (user_id, week_ending, timesheet_comment)
VALUES ('$user_id', '$week_ending', '$timesheet_comment')";
if (mysqli_multi_query($con, $sql)) {
$last_id = $con->insert_id;
}
$sql2 = "INSERT INTO stafftime (sheet_id, staff_date, time_start, time_end, break_time)
VALUES ('$last_id', '$staff_date', '$time_start', '$time_end', '$break_time')";

if (mysqli_multi_query($con, $sql2)) {

      $message = '<p class="text-success"><i class="fa fa-check"></i> - Timesheet Submitted Successfully</p>';
} else {
  $message = '<p class="text-danger"><i class="fa fa-check"> Error:'.  $sql2 . '<br>' . mysqli_error($con).'</p>';
}

mysqli_close($con);

    }

 

[Barand]

While you're in a receptive mood I have another couple of recommendations for you.

1. Use prepared statements so you aren't putting data values directly into the queries.
2. Switch to PDO from mysqli. It's far more streamlined and makes (1) above much easier.