MySQL & PHP Sum of rows in foreach

Question

Im trying to get the sum of workouts in a row and then order all rows by the sums to get what place the team is in and so there listed in the correct order. Im getting a little lost in my own logic right now trying to figure this out. I understand the MYSQL sum function but cant seem to see how i could use it to help me in this instance.

So something like this: Example

Here is my current Table schema:

CREATE TABLE workouts
(
team_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
username VarChar(255) NOT NULL,
team_name VarChar(50) NOT NULL,
week1 INT NOT NULL,
week2 INT NOT NULL,
week3 INT NOT NULL,
week4 INT NOT NULL,
week5 INT NOT NULL,
week6 INT NOT NULL,
week7 INT NOT NULL,
week8 INT NOT NULL,
week9 INT NOT NULL,
week10 INT NOT NULL,
week11 INT NOT NULL,
week12 INT NOT NULL
) engine=innodb;

And Here is my display so far:

<?php 
$count = 0;
$statement = $db->query('SELECT * FROM workouts');
foreach($statement as $row):
?>
<tr>
<td><?php $count++; ?></td>
<td><?php $row['team_name']; ?></td>
<td><?php $row['week1']; ?></td>
<td><?php $row['week2']; ?></td>
<td><?php $row['week3']; ?></td>
<td><?php $row['week4']; ?></td>
<td><?php $row['week5']; ?></td>
<td><?php $row['week6']; ?></td>
<td><?php $row['week7']; ?></td>
<td><?php $row['week8']; ?></td>
<td><?php $row['week9']; ?></td>
<td><?php $row['week10']; ?></td>
<td><?php $row['week11']; ?></td>
<td><?php $row['week12']; ?></td>
<td><?php  ?></td>
</tr>
<?php endforeach; ?>

I can't see the embedded image. Can you describe the output you want? — Gordon Linoff
– Gordon Linoff, Commented Aug 7, 2012 at 21:43
Sure Id Like it to have the place, then the team name week 1 2 3 then the total for all the teams in the database table. I also added a new image on my host to help — John Ayers
– John Ayers, Commented Aug 7, 2012 at 21:49
Your data appears to be at the user level, but you want the teams in order. What is the relationship between users and teams? — Gordon Linoff
– Gordon Linoff, Commented Aug 7, 2012 at 21:52
Unless the username is the singular login name for the whole team. I've seen competition-tracking systems before that regard the team themselves as the user, not the members. — Adrian
– Adrian, Commented Aug 7, 2012 at 21:56

DaveRandom · Accepted Answer · 2012-08-07 21:48:22Z

Well the simplest answer based on what you already have to sum up the rows is this:

<?php 
$count = 0;
$statement = $db->query('SELECT * FROM workouts');
foreach($statement as $row):
?>
<tr>
<td><?php echo $count++; ?></td>
<td><?php echo $row['team_name']; ?></td>
<td><?php echo $row['week1']; ?></td>
<td><?php echo $row['week2']; ?></td>
<td><?php echo $row['week3']; ?></td>
<td><?php echo $row['week4']; ?></td>
<td><?php echo $row['week5']; ?></td>
<td><?php echo $row['week6']; ?></td>
<td><?php echo $row['week7']; ?></td>
<td><?php echo $row['week8']; ?></td>
<td><?php echo $row['week9']; ?></td>
<td><?php echo $row['week10']; ?></td>
<td><?php echo $row['week11']; ?></td>
<td><?php echo $row['week12']; ?></td>
<td><?php echo $row['week1'] + $row['week2'] + $row['week3'] + $row['week4'] + $row['week5'] + $row['week6'] + $row['week7'] + $row['week8'] + $row['week9'] + $row['week10'] + $row['week11'] + $row['week12']; ?></td>
</tr>
<?php endforeach; ?>

But in order to use this value to control the order in which the rows are displayed you will need to get MySQL to do this work for you.

What you will be wanting to do is something like this:

<?php 

  $count = 0;
  $statement = $db->query('
    SELECT *, week1 + week2 + week3 + week4 + week5 + week6 + week7 + week8 + week9 + week10 + week11 + week12 AS Total
    FROM workouts
    ORDER BY Total
  ');
  foreach ($statement as $row):
?>
<tr>
<td><?php echo $count++; ?></td>
<td><?php echo $row['team_name']; ?></td>
<td><?php echo $row['week1']; ?></td>
<td><?php echo $row['week2']; ?></td>
<td><?php echo $row['week3']; ?></td>
<td><?php echo $row['week4']; ?></td>
<td><?php echo $row['week5']; ?></td>
<td><?php echo $row['week6']; ?></td>
<td><?php echo $row['week7']; ?></td>
<td><?php echo $row['week8']; ?></td>
<td><?php echo $row['week9']; ?></td>
<td><?php echo $row['week10']; ?></td>
<td><?php echo $row['week11']; ?></td>
<td><?php echo $row['week12']; ?></td>
<td><?php echo $row['Total']; ?></td>
</tr>
<?php endforeach; ?>

This works i just had to add DESC to the end of the ORDER BY to get it to sort properly. Thanks for the help. Good solution.

Adrian · Accepted Answer · 2012-08-07 21:47:21Z

1

I would do this as part of the SQL, not in PHP. Instead of making use of Sum(), which is designed to add up one column down an entire grouping sequence, just add up the columns and assign them the alias 'Total', like this:

SELECT week1 + week2 + week3 + weekN Total FROM workouts ORDER BY Total

answered Aug 7, 2012 at 21:47

Adrian

2,8751 gold badge25 silver badges33 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2012-08-07 21:54:53Z

1

If I understand correctly, you want the teams in order, with the users in each team (perhaps in their own order).

To get the team order, you will need a join.

with wo as (select wo.*,
                   (week1 + week2 + . . . weekn) as weektotal
            from workouts
           )
select wo.*
from wo join
     (select team_id, sum(weektotal) as weektotal
      from wo
      group by team_id
     ) wot
     on wo.team_id = wot.team_id
order by wot.weektotal desc, wot.team_id, wo.weektotal desc

answered Aug 7, 2012 at 21:54

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Paul T. Rawkeen · Accepted Answer · 2012-08-07 22:02:54Z

1

Here is my solution.

Instead of just echoing the database values as your process them, I dump them into an associative array with the total number of workouts as a key. Then you can sort them using ksort. Once that is done you can print out the array using a foreach loop.

I didn't type out the full array function but it should give you an idea.

<?php
$count = 0;

$statement = $db->query('SELECT * FROM workouts');

$workouts = array();

while ($row = mysql_fetch_array($statement)) {

    $total = 0;

    for($i = 0;$i < 13; $i++)
        $total = $total + $row['week'.$i];

    //Build the array containing each teams workouts
    $workouts[$total] = array('team_name' => $row['team_name'],'week1' => $row['week1']);
}

ksort($workouts);

$rowcount = 0;

foreach ($workouts as $total => $team): ?>
    <tr>
    <td><?php echo $rowcount++; ?></td>
    <td><?php echo $team['team_name']; ?></td>
    <td><?php echo $team['week1']; ?></td>
    <td><?php echo $team['week2']; ?></td>
    <td><?php echo $team['week3']; ?></td>
    <td><?php echo $team['week4']; ?></td>
    <td><?php echo $team['week5']; ?></td>
    <td><?php echo $team['week6']; ?></td>
    <td><?php echo $team['week7']; ?></td>
    <td><?php echo $team['week8']; ?></td>
    <td><?php echo $team['week9']; ?></td>
    <td><?php echo $team['week10']; ?></td>
    <td><?php echo $team['week11']; ?></td>
    <td><?php echo $team['week12']; ?></td>
    <td><?php echo $total  ?></td>
    </tr>
<?php endforeach ?>

edited Aug 7, 2012 at 22:02

Paul T. Rawkeen

4,1133 gold badges38 silver badges53 bronze badges

answered Aug 7, 2012 at 21:50

Romuloux

1,2221 gold badge11 silver badges31 bronze badges

2 Comments

John Ayers Over a year ago

This could work the problem im trying to see now is my server doesnt like mysql_fetch_array($statement)

Romuloux Over a year ago

Sorry, perhaps the statement foreach($statement as $row) instead.

Collectives™ on Stack Overflow

MySQL & PHP Sum of rows in foreach

4 Answers 4

1 Comment

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related