1
\$\begingroup\$

What I want is getting all names (first, middle, last) of the users along with each of their total hours of work which will be calculated by the SQL statement: CAST(SUM(timediff(dateTimeOut,dateTimeIn) as time).

I was able to achieve this with two queries, but somehow, I feel that this is inefficient (?):

$sql = "SELECT id, firstName, middleName, lastName FROM users_info";
$result = $pdo->query($sql);

$rows = array();

while($row = $result->fetch()){
     $time = $pdo->query(
                "SELECT CAST(SUM(timediff(dateTimeOut, dateTimeIn)) 
                 as time) as totalHoursWorked
                 FROM users_time WHERE user_id = {$row['id']}"
            )->fetch();

     $row['totalHoursWorked'] = $time['totalHoursWorked'];
     $rows[] = $row;

}

echo json_encode($rows);

I'll use the json data for a DataTable.

users_info table
+----+-----------+------------+----------+
| id | firstName | middleName | lastName |
+----+-----------+------------+----------+
|  1 | Isabelle  | Luna       | Ibarra   |
|  2 | George    | Boston     | Everett  |
|  3 | Skyler    | Land       | Cohen    |
+----+-----------+------------+----------+


users_time table:
+---------+---------+---------------------+--------------------+
| time_id | user_id |     dateTimeIn      |    dateTimeOut     |
+---------+---------+---------------------+--------------------+
|       1 |       1 | 2019-06-14 00:00:00 | 2019-06-16 09:00:0 |
|       2 |       1 | 2019-06-15 00:00:00 | 2019-06-16 08:00:0 |
|       3 |       2 | 2019-06-14 00:00:00 | 2019-06-16 09:30:0 |
|       4 |       2 | 2019-06-15 00:00:00 | 2019-06-16 08:30:0 |
|       5 |       3 | 2019-06-16 00:00:00 | 2019-06-16 09:00:0 |
+---------+---------+---------------------+--------------------+

db-fiddle link : https://www.db-fiddle.com/f/vpozKbVoXxPoGzcsBkLL36/0

\$\endgroup\$
  • 1
    \$\begingroup\$ Could you provide us the DDL and insert statements? \$\endgroup\$ – dfhwze Jun 16 at 11:35
2
\$\begingroup\$

Assuming that the id's in users_info exist in users_time, INNER JOIN (or just JOIN) is the way to merge the tables. If id's in user_info might not exist in users_time then use LEFT JOIN to allow null values in totalHoursWorked.

Assuming you want all four columns' data to be included in your echo'ed json string (your posted code isn't do that), you can just fetchAll() directly into your json_encode() and echo it -- all at once.

SQL: (Demo)

$sql = "SELECT i.id,
            MAX(i.firstName),
            MAX(i.middleName),
            MAX(i.lastName),
            CAST(SUM(TIMEDIFF(t.dateTimeOut, t.dateTimeIn)) AS TIME) AS totalHoursWorked
        FROM users_info i
        INNER JOIN users_time t ON i.id = t.user_id
        GROUP BY i.id";
$query = $pdo->query($sql);
echo json_encode($query->fetchAll(PDO::FETCH_ASSOC));

If you are truly only returning a single column (totalHoursWorked) without any of the other data, then I don't see the need to involve the user_info table at all.

\$\endgroup\$
1
\$\begingroup\$

$sql = "SELECT id, firstName, middleName, lastName FROM users_info";

and

"SELECT CAST(SUM(timediff(dateTimeOut, dateTimeIn)) 
                 as time) as totalHoursWorked
                 FROM users_time WHERE user_id = {$row['id']}"

can be merged into a single query:

   SELECT r.id, r.totalHoursWorked, t.firstName, t.middleName, t.lastName 
   FROM (
      SELECT q.id, SUM(q.hoursWorked) as totalHoursWorked 
      FROM (
          SELECT 
              id
            , timestampdiff(second, dateTimeIn, dateTimeOut) / 3600.0 as hoursWorked
          FROM users_info
          INNER JOIN users_time ON user_id = id
          ) q
      GROUP BY id
      ) r
    INNER JOIN users_info t on t.id = r.id;

Yielding:

id  totalHoursWorked    firstName   middleName  lastName
1   0.3                 Isabelle    Luna        Ibarra
2   0.3                 George      Boston      Everett
3   0.15                Skyler      Land        Cohen
\$\endgroup\$
  • \$\begingroup\$ It's only returning a single row [{"id":40,"firstName":"Employee 406","middleName":"Generated","lastName":"Auto","totalHoursWorked":"225:00:00"}] instead of this: [{"id":40,"firstName":"Employee 406","middleName":"Generated","lastName":"Auto","totalHoursWorked":"09:00:00"},{"id":41,"firstName":"Employee 692","middleName":"Generated","lastName":"Auto","totalHoursWorked":"152:00:00"},{"id":42,"firstName":"Employee 399","middleName":"Generated","lastName":"Auto","totalHoursWorked":"64:00:00"}] Basically, it was adding all the rows in the table. \$\endgroup\$ – Gene Adrian San Luis Jun 16 at 11:30
  • \$\begingroup\$ @Gene Adrian San Luis Your timediff was not outputted in hours. \$\endgroup\$ – dfhwze Jun 16 at 14:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.