Jump to content

Hours Worked Do Not WERK


Moorcam
 Share

Go to solution Solved by Barand,

Recommended Posts

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

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.

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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()) {

 

Link to comment
Share on other sites

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 by Moorcam
Link to comment
Share on other sites

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

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;

 

Link to comment
Share on other sites

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.

image.png.deb61286a930af3b22c066a7fd3424eb.png

Link to comment
Share on other sites

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.

image.png.deb61286a930af3b22c066a7fd3424eb.png

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.

Link to comment
Share on other sites

  • Solution

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 by Barand
Link to comment
Share on other sites

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

    }

 

Link to comment
Share on other sites

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

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