Jump to content

Adding values & update total


bartibus

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
Link to comment
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.
Link to comment
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]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.