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

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

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

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
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

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
