0

I need help creating a specific query, The following is an example of my paycheck table, empId is a foreign key that refers to the primary key of my 'users' table which is 'userId'

mysql> SELECT * FROM paycheck
    -> ;
+------------+---------------+---------+-------------+-------------+-------------+-------+
| payCheckId | jobId         | payRate | jobLocation | hoursWorked | startPeriod | empId |
+------------+---------------+---------+-------------+-------------+-------------+-------+
|       1512 | entertainment |      12 | store1      |       10.00 | 2013-03-02  |     1 |
|       1510 | entertainment |      12 | store1      |        8.00 | 2013-03-01  |     1 |
|       1507 | retail        |      10 | store1      |        8.00 | 2013-03-18  |     1 |
|       1506 | retail        |      10 | store1      |       20.00 | 2013-03-19  |     1 |
+------------+---------------+---------+-------------+-------------+-------------+-------+

What i want is to calculate the sum of all hours for all specific jobId , in this case if i did the
query correctly it would look like this:

+---------------+---------------+---------+
| payID         | payRate       | hours   | 
+---------------+---------------+---------+
|  entertainment| 12            |      18 | 
|        retail | 10            |      28 | 
+---------------+---------------+---------+

In this case there is only two jobIds but it could have more than 2

This is the query i have and its only showing one payId, so I need help fixing it

also note that email is an attribute of my users table

<table>";
                 $query = "SELECT jobId,payRate,SUM(hoursWorked) AS 'All_Hours'
                          FROM users,paycheck
                          WHERE users.email = '" . $_SESSION['email'] .
                          "' AND userId = empId";  
                          $result = mysqli_query($db,$query);

                          if (!$result) { //if the query failed
                              echo("Error, the query could not be executed: " .
                              mysqli_error($db) . "</p>");
                              mysqli_close($db); //close the database
                          } //by now we have made a successful query  
                          while ($row = mysqli_fetch_assoc($result)){
                          echo "<tr><td>" .$row['jobId'] . "</td>
                                <td>" .$row['payRate'] . "</td>
                                <td>" .$row['All_Hours'] . "</td>
                                </tr>";
                          }
                  echo"</table>

Edited by israruval007

2
Contributors
3
Replies
12
Views
4 Years
Discussion Span
Last Post by pixelsoul
0

It maybe a silly question but how many results do you get back when you run that same exact query directly against your database, like in phpmyadmin?

0

You would need some type of join like this example:

SELECT
p.jobId,
p.payRate,
SUM(p.hoursWorked) AS 'All_Hours',
u.userid,
u.email

FROM users u JOIN paycheck p

ON p.empId = u.userId

WHERE u.email = '" . $_SESSION['email'] . "' AND u.userId = p.empId

Not positive that the above would work without testing but that is the basics of how it would be done.

This topic has been dead for over six months. 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.