php

Question

I have a log` that saves log records (amount earned, etc) of employees and a code that separates the data into tables grouped under each employee id:

Empid: 0001
---------------------------
| Logid   | Hours   | Pay |
---------------------------
|  1001   | 10      | 50  |
---------------------------
|  1002   | 2       | 10  |
---------------------------

Empid: 0003
---------------------------
| Logid   | Hours   | Pay |
---------------------------
|  1003   | 3       | 9   |
---------------------------
|  1004   | 6       | 18  |
---------------------------

I managed this with the following semi-pseudocode:

$query = mysql_query("SELECT * FROM `log` ORDER BY empid");
$id = 0;

while ($list = mysql_fetch_assoc($query)) {
    if ($id != $list['logid']) {
          create header (Logid, Hours, Pay)
          $id = $list['logid'];
          }
    add each data row for the empid
}

But now I would like to add the total of the Pay column and put it at the bottom of each table for each empid.

By putting the code $total_pay = $total_pay + $list['pay'] in the while loop I can get the total pay but I can't figure out how I might be able to show the total at the bottom.

Would really appreciate any advice on this!

score 3 · Answer 1 · edited May 25 '19 at 14:29

This should do it. You basically sum up until the id is changing.

$sum = 0;
while ($list = mysql_fetch_assoc($query)) {    
    if ($id != $list['logid']) {
          //create the totals using $sum !!!
          // after that re-set sum to 0
          $sum = 0;
          //create header (Logid, Hours, Pay)
          $id = $list['logid'];
    }
    $sum += $list['Pay'];
    //add each data row for the empid
}

Also...

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

Oh but how do I show the total at the bottom of each empid table? Like for Empid: 0001, beneath the column pay it would show 60 and for empid 0003 it would show 27. I've tried mysqli when I was trying to teach myself php and mysql but it didnt work for me, unfortunately. I think the php version of my host is still 5.2 — Cuppy, Feb 22 '13 at 13:33

Chris · Accepted Answer · 2013-02-22T14:52:35.867

There are two ways that you can do this.

Keep a running total of all of the "pay" values, and add it into your table at the bottom. For example:

$i=0;
while ($list = mysql_fetch_assoc($query)) {   // for each row in your results
    if ($id != $list['EmployeeId']) {  // We only enter this loop if the EmployeeId doesn't equal $id. This can happen because either $id doesn't exist yet, or it doesn't match the previous EmployeeId
          $i++;  // increase $i by 1
          if($i>1) {  // Enter this loop only if $i is greater than or equal to 2 (if it is less than two, then this is our first time running this script, and adding a footer row wouldn't make any sense).
              create footer (EmployeeId, Hours, Pay);  // Log Id is irrelevant here
          }
          //  reset your variables here
          $id = $list['EmployeeId'];  // set $id = the first or the new Employee ID
          $total_pay = $list['pay'];  // This is our first time for this Employee, so don't just add it to the running total
          create header (EmployeeId, Hours, Pay) // Create the top half of your table
    } else {  // The EmployeeId has been established: we only need to change the running total
          $total_pay = $total_pay + $list['pay'];
    }
    //  add a data row for each LogId. This executes every time we go through the loop
    create_normal_row(LogId, EmployeeId, Hours, Pay)
}

// At this point, both Employees have a header, and all data rows. However, we left the loop before we could add the last Employee's footer row
// Let's add one more footer row for the last user
create_footer (Logid, Hours, Pay);

SQL

MySQL has a function that does something very similar to what you are trying to do called ROLLUP. You can read more about it here:

http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html

Basically, you would change your query to work like this:

SELECT LogId, EmployeeId, SUM(Hours), SUM(Pay) FROM `log` 
GROUP BY empid, logid WITH ROLLUP

This query will return a dataset that looks like this:

---------------------------------------
| Logid   | EmployeeId| Hours   | Pay |
---------------------------------------
|  1001   | 1         | 10      | 50  |
---------------------------------------
|  1002   | 1         | 2       | 10  |
---------------------------------------
|  NULL   | 1         | 12      | 60  |
---------------------------------------
|  1003   | 2         | 3       | 9   |
---------------------------------------
|  1004   | 2         | 6       | 18  |
---------------------------------------
|  NULL   | 2         | 9       | 27  |
---------------------------------------
|  NULL   | NULL      | 21      | 87  |
---------------------------------------

Whenever $list['Logid'] is null, you know that you have a "total" row. Be careful though, this will add a "sum of all employees" row at the bottom of your dataset. If $list['EmployeeId'] is null, then you know you're in this "total" row.

On a related note (I'm not sure if this is what you're asking for), you can show this stuff in a table by using HTML <table> elements.

Each row would look like this:

<table> <!-- shown at the beginning of each table -->
<tr> <!-- shown at the beginning of each row -->
<td> <!-- shown at the beginning of each table cell -->
Your text goes here
</td> <!-- shown at the end of each table cell -->
<td>
More text can go here
</td>
</tr> <!-- shown at the end  of each row -->
</table> <!-- shown at the end of each table -->

<tr>s can be repeated indefinitely within each <table>, and <td>s can be repeated within <tr>s.

I'm just starting out with programming and still struggling with logics, may I clarify this part of the code the part of the code beginning from the if statement first checks if the id matches. First iteration it won't match so it will add 1 to i which will have a new value of 1. new value is still < 1 so it will not create the footer and goes on to create the headers. — Cuppy, Feb 22 '13 at 14:29
next iteration since id matches it will move on to create a new data row under the heading until it encounters a new id, at the same time it will also add to pay into a variable $total_pay. In that case it will add 1 to $i which will now have value of 2 and therefore create the footer. And it repeats and once the loop is done, it will create a final footer. Did I understand it correctly? Sorry for that wall of text, just trying to learn from the help of others. I'm also reading up on the ROLLUP function — Cuppy, Feb 22 '13 at 14:31
I made a mistake in the code (which I just fixed). Previously, we were seeing if `LogId=$id`. However, we want to see if `EmployeeId=$id`. Here's how the program flows: We start with Row #1. We start with row 1. `$id (null) != $list['EmployeeId'] (1)`, so we enter the `if` statement. `$i` is set to `1`. `$i` is not greater than `1`, so we skip the footer command. We set `$id=$list['EmployeeId']` and set `$total_pay=$list['pay']`. We create the header for this user and leave the `if` statement. We add a normal row for this Employee and go back to the start of the loop. — Chris, Feb 22 '13 at 14:56
We're now on row 2. `$id (1) == $list['EmployeeId'] (1)`, so we skip the `if` statement.We add a normal row for this Employee and go back to the start of the loop. Now we're on row 3. `$id (1) != $list['EmployeeId'] (2)`, so we enter the `if` statement. `$i` is set to `2`. `$i` is greater than `1`, so we add a footer for the previous Employee. We set `$id=$list['EmployeeId']` and set `$total_pay=$list['pay']`. We create the header for this user, leave the `if` statement, and add a normal row. We continue like this until we're done with all rows, then add one last footer row for the last guy. — Chris, Feb 22 '13 at 14:59
neat thanks! Yep, I realised I typed it wrong in my question too. However I was getting some errors that I isolated to this line: **else { $total_pay = $total_pay + $list['pay']; } create_normal_row(LogId, EmployeeId, Hours, Pay) }** — Cuppy, Feb 22 '13 at 15:13
This gave me an empty data line under the 1st header so I moved the data row creation into the else {} condition. This threw me another error that didn't show the 1st line of data for each empid. The else condition was skipping a line so I removed the else {} and forced it to create a dataline regardless if new or old empid and it somehow worked! I really appreciated your help and the time you took to explain it to me too, thanks!! — Cuppy, Feb 22 '13 at 15:14

php - Finding the total in a column

2 Answers2

PHP

SQL