SQL query

[Kingy]

I am trying to make a statistic on my irc for the person that shouts the most (ie. the user that users the most uppercase letters). What i am trying to do is select info from the mysql database with two coloums (letters, shout). Letters represents the amount of letters the user has typed, shout represents the amount of uppercase letters the user typed.

Simple enough to make a percentage of this and display it, but im confused

 

$result = mysql_query("SELECT * FROM stats ORDER BY letters, shout DESC LIMIT 1");
while($row = mysql_fetch_array( $result )) {
  $letters = $row['letters'];
  $shout = $row['shout'];
  $user = $row['user'];
  $percent = (($letters/$shout)*100);
  $percent = round($percent ,2);
  fputs($conn,"PRIVMSG $room The top shouter on the network is $user with a shout percentage of $percent% \r\n");
       }       

 

this doesn't return the right amount. Any ideas? If any help could be given to the next bit as well, i will eventually add to this to maybe show the 2nd user on  this list or even the user with the lowest percentage? thanks

[BillyBoB]

What do you mean it doesnt show the right amount could you give us an example?

[sasa]

 

  $percent = (($shout/$letters)*100);

[Kingy]

I mean it doesn't show the person with the highest percent

 

heres apart of the table

 

id user letters shout

1 user1  1000  100

2 user2  500    300

 

so with the sql query i would want it to show that user2 has the highest percentage of shouting

 

but with that query above it doesn't display the right user. What im asking is, does that query look right or not?

[burnside]

use

 

$sql = "SELECT * FROM `db` ORDER BY shout DESC ";

 

will list the entries in order of higest to lowest

[Kingy]

thanks all working now cheers

[Kingy]

How would i get this to show the second person on this list? would i just add a LIMIT 2

 

And how about the lowest person on this list?

[burnside]

Yeah just add a limit , and if you want to change order use ASC i think will show lowest first

[Kingy]

ok there is a problem now, this script doesn't show the user with the top percentage, it shows the user with the top shout characters, so the script is working fine, but displaying the wrong user because it show the user with top Shout not top percentage

[burnside]

ah i see, let me have a little think.

[Kingy]

Im not totally sure that its the top SHOUT that is getting displayed but it sure isn't the top percentage of (shout,letters).

[resago]

why not just store the percentage, are you using the other 2 values for anything?

 

you can do some math in the select statement. ex sort by (shout/letters) desc

 

[sasa]

use

... ORDER BY shout/letters

[Kingy]

Ok that there works perfectly now, thanks for that one..

 

My next question for this query is how do i display 2 people but with different wording,

 

eg: in the while look ive put

 

while (...) {

echo "The top shouter on the network is $user with $percent%";

}

 

But if i put in the sql query LIMIT 2, im going to get the that echo two times.

 

What i would like to do is echo something like this...

 

The top shouter on the network is $user with $percent%

Another person that shouts is $user2 with $percent2%

 

or something along those lines??