...

View Full Version : How can I total the field values of a mysql table row and display them



Skip_B
01-13-2010, 12:23 AM
Currently I use the following to find a list of players and arrange them according to their current points totals. I need a cumulative points total.
I need to add the values of the $row['points'], $row['Pts_Q1'], $row['Pts_Q2'], $row['Pts_Q3'], $row['Pts_Q4'] and then display them in order of the largest to smallest.

I could add a field to the table to store the total then order them by that field, something like:
Update Players
Set Total_Points = points+Pts_Q1+Pts_Q2+Pts_Q3Pts_Q4
Then query the table and order it by Total_Points

Or could I just store them to a variable and list them without having to add the field to the table?

From the following code I just need to display the total of all the points values instead of displaying them individually.


echo '<div id="indent_50">';
$counter = 1;
$playerQuery = "SELECT * FROM `players` WHERE `tavern_id` = '{$tavern_id}' ORDER BY `points` DESC";
$player_set = mysql_query($playerQuery) or die(mysql_error());
echo '<table><tbody>';
echo '<tr>
<td class = lbl1>'."Rank" ."</td>
<td class = lbl1>". "Points" ."</td>
<td class = lbl2>". "Name" ."</td>
<td class = lbl2>". "Quarter 1" ."</td>
<td class = lbl2>". "Quarter 2" ."</td>
<td class = lbl2>". "Quarter 3" ."</td>
<td class = lbl2>". "Quarter 4" ."</td>
</tr>";
while ($row = mysql_fetch_array($player_set))
{
echo '<tr>
<td class = data1>'."$counter" ."</td>
<td class = data1>". $row['points'] ."</td>
<td class = data2>". $row['name'] ."</td>
<td class = data2>". $row['Pts_Q1'] ."</td>
<td class = data2>". $row['Pts_Q2'] ."</td>
<td class = data2>". $row['Pts_Q3'] ."</td>
<td class = data2>". $row['Pts_Q4'] ."</td>
</tr>";
$counter++;
}
echo '</tbody></table>';
echo'</div>';

JAY6390
01-13-2010, 01:10 AM
SELECT *, (`points` + `Pts_Q1` + `Pts_Q2` + `Pts_Q3` + `Pts_Q4`) as `totalpts` FROM `players` WHERE `tavern_id` = '{$tavern_id}' ORDER BY `totalpts`, `points` DESC
Something like that?

Len Whistler
01-13-2010, 01:20 AM
An all PHP solution could be something like:


<?php
$db_data = array('234','123','6712','23','64');
sort($db_data);

$total = 0;

foreach ($db_data as $value) {
$total = $value + $total;
echo "$value<br>";
}

echo "<br>$total";

?>


output



23
64
123
234
6712

Total 7156



Sort would have to be changed to descending.



-----------

JAY6390
01-13-2010, 01:28 AM
You mean rsort() (http://www.php.net/rsort)? :)

Len Whistler
01-13-2010, 01:36 AM
You mean rsort() (http://www.php.net/rsort)? :)

Yes ....... rsort . When I was proof reading my post and code I noticed the OP wanted it ordered in reverse.



--------------------

JAY6390
01-13-2010, 01:44 AM
hehe I never proof read...throw code at it and hope for the best is my motto :D

Skip_B
01-13-2010, 05:33 AM
I think I am very close but I can't display totalpts.


echo '<div id="indent_50">';
$counter = 1;
$playerQuery = "SELECT *, (`points` + `Pts_Q1` + `Pts_Q2` + `Pts_Q3` + `Pts_Q4`) as `totalpts` FROM `players` WHERE `tavern_id` = '{$tavern_id}' ORDER BY `totalpts`, `points` DESC";
$player_set = mysql_query($playerQuery) or die(mysql_error());
echo '<table><tbody>';
echo '<tr>
<td class = lbl1>'."Rank" ."</td>
<td class = lbl1>". "Points" ."</td>
<td class = lbl2>". "Name" ."</td>
</tr>";
while ($row = mysql_fetch_array($player_set))
{

echo '<tr>
<td class = data1>'."$counter" ."</td>
<td class = data1>". "$totalpts" ."</td>
<td class = data2>". $row['name'] ."</td>

</tr>";
$counter++;
}
echo '</tbody></table>';
echo'</div>';

<td class = data1>". "$totalpts" Yields undefined variable.
How do I convert totalpts to a variable so I can echo it onto the screen?

hinch
01-13-2010, 10:15 AM
<td class = data1>". "$totalpts" ."</td>

should be

<td class = data1>". $row['totalpts'] ."</td>

Skip_B
01-13-2010, 01:08 PM
Of course that works, you guys are the best. But help me understand why.
totalpts is not actually a field of a row in the table. It is a value we created by adding together several several fields from a row. Does it then become like a field in the row from the list created by the query?

I was thinking of it as a temporary variable but it appears to be more like a field in a row of a table without actually being added to the table.

In other words, while it is not a row in the table it is a row in the query, right?

hinch
01-13-2010, 05:57 PM
what your doing in your query is your saying

(`points` + `Pts_Q1` + `Pts_Q2` + `Pts_Q3` + `Pts_Q4`) as `totalpts`

which means your creating a new "virtual" if you like row called totalpts which is the sum of all the columns listed int he brackets.

This means that the array returned by the query now has * (all columns) and on the end of it a new column called totalpts.

so you can call the contents of that column by using the $row['totalpts'] reference.

$row is simply an array of elements.

Skip_B
01-14-2010, 12:26 AM
Thank you. That was a concise explanation. I totally get it and that is saying a lot.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum