[Resolved] [Resolved] Results only get first row, not all rows, for certain user

[helz]

The code below gets a list of the users in the database which is defined by their name, but the problem is now that the noofgame field only shows the first row for that user, not the sum of all the noofgame in the rows that the user has.

eg: (below is the data in the table)
user | noofgame
test | 5
test | 4
test2 | 3

(below is the data that is meant to be shown in the list)
User | Total Number of Games
test | 9
test2 | 3

(below is what is shown in the list atm)
User | Total Number of Games
test | 5
test2 | 3

As you should see the total number of games is only the first row/record of the user, not the total... any help please?

echo    "<table border=1 cellspacing=0 cellpadding=1><tr><td>Bowlers Username</td><td>Total Number of Games</td></tr>";
			$bowler = mysql_query("SELECT bowlername, noofgame FROM ".$score." GROUP BY bowlername");   

				if(mysql_num_rows($bowler)>0) { 
					while ($row = mysql_fetch_array($bowler)) {   

						if($row['bowlername'] != $bowlername) { 
							$display = 1;  

							$bowlername = $row['bowlername']; 
							$noofgame = $row['noofgame']; 
							$totgame = $noofgame; 
						}  

						else {  

							$display = 0; 
							$noofgame = $row['noofgame']; 
							$totgame = $totgame + $noofgame; 
						}  

						echo "<tr><td>";  

						if($display == 1) {  

							echo "<a href=\"index.php?module=Bowling_Scores&bowlername=".$bowlername."\">".$bowlername."</a>";  

						}  

						else {  

							echo "&nbsp;";  

						}  

						echo "</td><td>".$totgame."</td></tr>";  

					}  

				}
echo "</table>";

[Sxooter]

Congratulations, you've found yet another bug in MySQL (that isn't likely to get fixed, because to the MySQL developers, it's not a bug, it's a feature...)

Normally, in a RDBMs, a select statement like this:

select dept, enum from phonebook_main group by dept;

You'll get an error (like the Spec says you should) of:

ERROR: Attribute phonebook_main.enum must be GROUPed or used in an aggregate function

Since you want a sum of noofgames, just do use an aggregate function sum() like so:

SELECT bowlername, sum(noofgame) FROM ".$score." GROUP BY bowlername

[helz]

interesting.. ive not fully looked up on sql funtions so that code was from a member of this forum.

that code works for one user, but still only loads the 1st row for the others.

http://bowling.hellrazer.net/index.php?module=Bowling_Scores

the user helz has two rows, with noofgame as 3 + 7
test has 2 rows with noofgame as 4+6 (sxooter code works displays 10)
rick has 1 row with noofgame as 3 (does display 3 so im not sure whether the code works or not, might add another row to see if it does)

to get the info from the db for noofgame had to use:
$noofgame = $row['sum(noofgame)'];
instead of:
$noofgame = $row['noofgame'];

[Sxooter]

I'm not sure what you mean by "that code works for one user, but still only loads the 1st row for the others. " There should be only one row per user. But that row should list their name and the numbers of games (from the sum() function).

Looking at your site it looks like the query that runs on the top, isn't pulling in all the records for helz, only the first row, whereas the entry for test has two lines and it gets added up right.

Maybe you've got a problem with the usernames being mixed case or something weird? Not sure what's happening here.

[helz]

sorry my fault, the 2nd row for helz was that the noofgame wasnt inserted into the db with the rest of the information, i fixed that a while back but forgot that the record was already in,

works perfectly, thanks alot 🙂