Page 1 of 2

Points system!!!

Posted: Mon Mar 15, 2004 7:18 pm
by Joe
Hi. recently I developed a points system for my site where users can gain points if they manage to complete a challenge. The part I am having a problem with is trying to create a mySQL query that picks out the member who has the most points. Please help?

I am using PHP and mySQL if thats any help?


Regards


Joe 8)

Posted: Mon Mar 15, 2004 7:21 pm
by Unipus
look at the MAX() operation under the SELECT query type.

Posted: Mon Mar 15, 2004 7:22 pm
by Goowe
Where do you keep your points at? Are they stored with the user's information? Or in a seperate "challenge" table?

Posted: Mon Mar 15, 2004 7:31 pm
by Joe
The points are stored in the user information table!

Posted: Mon Mar 15, 2004 7:39 pm
by tim
Unipus wrote:look at the MAX() operation under the SELECT query type.
:)

Posted: Mon Mar 15, 2004 7:54 pm
by Joe
I am trying this:

<?php
$link = mysql_connect("???", "???", "???");
mysql_select_db("???") or die("Could not connect!" . mysql_error());
$sql = "SELECT * FROM members WHERE MAX(points)";
$result = mysql_query($sql) or die("Error!");

if (mysql_num_rows($result))
{
$row = mysql_fetch_array($result);
echo "<img src='".$row['avatar']."'><br>";
echo "<i>Name: </i>".$row['username']."<br>";
echo "<i>Points: </i>".$row['points']."<br>";
mysql_close($link);
}
?>

Still no luck. Please what am I doing wrong!!!

Posted: Mon Mar 15, 2004 7:57 pm
by Illusionist
$sql = "SELECT * FROM members HAVING MAX(points)";

Posted: Mon Mar 15, 2004 7:59 pm
by tim
throw in a HAVING to your sql to specify.

ie:

Code: Select all

<?php
$sql = "SELECT * FROM members HAVING MAX(points)";
?>
/edit: which has already been told. looks like i was the late one this time :wink:

Posted: Mon Mar 15, 2004 8:01 pm
by Joe
Nope. Its still not showiing up for some strange reason...

Posted: Mon Mar 15, 2004 8:04 pm
by Illusionist
tim wrote: /edit: which has already been told. looks like i was the late one this time :wink:
hehe

are oyu getting any errors??

Posted: Mon Mar 15, 2004 8:04 pm
by tim
try:

Code: Select all

<?php
$sql = "SELECT * FROM members";
$quer = mysql_query($sql);
$row = mysql_fetch_array($quer);
$points = $row["points"];
$max = max($points);

?>
maybe?

Posted: Mon Mar 15, 2004 8:05 pm
by Joe
Not errors. Just the fact that the details for the member with the highest score are not being printed to the screen 8)

Regards

Posted: Mon Mar 15, 2004 8:06 pm
by Illusionist
$sql = "SELECT * FROM members ORDER BY points DESC LIMIT 1";

Posted: Mon Mar 15, 2004 8:12 pm
by Joe
YAY! I got it. Thanks for that people!!!

Regards


Joe 8)

Posted: Mon Mar 15, 2004 8:12 pm
by tim
ill's method would be a far better approach/more easy and also a faster method. simple enough too even I can understand it :lol: :lol: