Jump to content


Photo

Adding values & update total


  • Please log in to reply
4 replies to this topic

#1 bartibus

bartibus
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 27 March 2006 - 10:45 AM

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

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,025 posts

Posted 27 March 2006 - 10:59 AM

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

mysql_query ("UPDATE tablename SET Total = colA + colB + colC");

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

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

This way, if any of the numbers has changed, you still get the correct total without having to recalculate them all again.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 Samad

Samad
  • New Members
  • Pip
  • Newbie
  • 2 posts
  • LocationLand of Arya

Posted 27 March 2006 - 11:03 AM

Try looping to do that:
provide you code to see more details,then could help you
The best & most beautiful things in the world , can not be seen nor touched but are felt in the heart
Hellen Keller

#4 bartibus

bartibus
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 27 March 2006 - 11:44 AM

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

#5 bartibus

bartibus
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 27 March 2006 - 02:27 PM

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:

<?

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";
}
?>





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users