Jump to content

Archived

This topic is now archived and is closed to further replies.

bartibus

Adding values & update total

Recommended Posts

Hi all,

I have difficulties coming up with the right script for the following:
I have a mysql database table in which I store a (large) amount of data (numbers) for different users. Each row has a column with a unique username, and several columns in which the numbers are stored. I want to add those numbers for each username and save the total in another column (preferably in the same table), and would like to do this automatically in stead of doing the same over and over again for each user.
Does anybody know how to get there?
Thanks in advance!
Bart

Share this post


Link to post
Share on other sites
So I guess you have a table like

username
colA
colB
colC
Total

to add contents of colA, colB and colC and put result in Total for all records

[code]mysql_query ("UPDATE tablename SET Total = colA + colB + colC");[/code]

Although I wouldn't bother, you can always get the total when you need it by

[code]$query = "SELECT colA, colB, colC, (colA + colB + colC) as Total FROM tablename";[/code]

This way, if any of the numbers has changed, you still get the correct total without having to recalculate them all again.

Share this post


Link to post
Share on other sites
Try looping to do that:
provide you code to see more details,then could help you

Share this post


Link to post
Share on other sites
I tried Barand's code and that seems to do it. That simple! I had been playing around with looping codes but that's not necessary it seems. Anyway, thanks a lot!
Bart

Share this post


Link to post
Share on other sites
Now that I have the totals in a separate column, I would like to show a competition ranking. If I select the relevant columns, sort them on 'totals' in descending order, and show this in a table, the list shows the following:
Rank Name Totals
1 User A 200
2 User B 150
3 User C 150
4 User D 100

Is it possible to assign ranks like you would do in a competition (A:1, B:2, C:2, D:4)?
Below is the code I use right now:

[code]<?

function showRanglijst(){
   global $database;
                    $query = "SELECT username, name, totaal FROM punten ORDER BY totaal DESC LIMIT 0, 10";
                    $result = mysql_query($query) or die(mysql_error());

   $num_rows = mysql_numrows($result);
   if(!$result || ($num_rows < 0)){
      echo "Error displaying info";
      return;
   }
   if($num_rows == 0){
      echo "Database table empty";
      return;
   }

   echo "<table align=\"left\" border=\"0\" cellspacing=\"0\" cellpadding=\"1\" width=\"380\">\n";
   echo "<tr><td><b>Nr.</b></td><td><b>Naam</b></td><td><b>Punten</b></td></tr>\n";
   for($i=0; $i<$num_rows; $i++){
      $username  = mysql_result($result,$i,"username");
    $name   = mysql_result($result,$i,"name");
    $totaal   = mysql_result($result,$i,"totaal");
    $nummer = $i+1;

      echo "<tr><td>$nummer</td><td><a href=\"userinfo.php?user=$username\" title=\"$username\">$name</a></td><td>$totaal</td></tr>\n";
   }
   echo "</table><br>\n";
}
?>[/code]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.