PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!
Moderator: General Moderators
Joe
Forum Regular
Posts: 939 Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow
Post
by Joe » Mon Mar 15, 2004 7:18 pm
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
Unipus
Forum Contributor
Posts: 409 Joined: Tue Aug 26, 2003 2:06 pm
Location: Los Angeles, CA
Post
by Unipus » Mon Mar 15, 2004 7:21 pm
look at the MAX() operation under the SELECT query type.
Goowe
Forum Commoner
Posts: 94 Joined: Mon Mar 15, 2004 9:51 am
Location: Southeast Alaska
Post
by Goowe » Mon Mar 15, 2004 7:22 pm
Where do you keep your points at? Are they stored with the user's information? Or in a seperate "challenge" table?
Joe
Forum Regular
Posts: 939 Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow
Post
by Joe » Mon Mar 15, 2004 7:31 pm
The points are stored in the user information table!
tim
DevNet Resident
Posts: 1165 Joined: Thu Feb 12, 2004 7:19 pm
Location: ohio
Post
by tim » Mon Mar 15, 2004 7:39 pm
Unipus wrote: look at the MAX() operation under the SELECT query type.
Joe
Forum Regular
Posts: 939 Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow
Post
by Joe » Mon Mar 15, 2004 7:54 pm
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!!!
Illusionist
Forum Regular
Posts: 903 Joined: Mon Jan 12, 2004 9:32 pm
Post
by Illusionist » Mon Mar 15, 2004 7:57 pm
$sql = "SELECT * FROM members HAVING MAX(points)";
tim
DevNet Resident
Posts: 1165 Joined: Thu Feb 12, 2004 7:19 pm
Location: ohio
Post
by tim » Mon Mar 15, 2004 7:59 pm
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
Joe
Forum Regular
Posts: 939 Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow
Post
by Joe » Mon Mar 15, 2004 8:01 pm
Nope. Its still not showiing up for some strange reason...
Illusionist
Forum Regular
Posts: 903 Joined: Mon Jan 12, 2004 9:32 pm
Post
by Illusionist » Mon Mar 15, 2004 8:04 pm
tim wrote:
/edit: which has already been told. looks like i was the late one this time
hehe
are oyu getting any errors??
tim
DevNet Resident
Posts: 1165 Joined: Thu Feb 12, 2004 7:19 pm
Location: ohio
Post
by tim » Mon Mar 15, 2004 8:04 pm
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?
Joe
Forum Regular
Posts: 939 Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow
Post
by Joe » Mon Mar 15, 2004 8:05 pm
Not errors. Just the fact that the details for the member with the highest score are not being printed to the screen
Regards
Illusionist
Forum Regular
Posts: 903 Joined: Mon Jan 12, 2004 9:32 pm
Post
by Illusionist » Mon Mar 15, 2004 8:06 pm
$sql = "SELECT * FROM members ORDER BY points DESC LIMIT 1";
Joe
Forum Regular
Posts: 939 Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow
Post
by Joe » Mon Mar 15, 2004 8:12 pm
YAY! I got it. Thanks for that people!!!
Regards
Joe
tim
DevNet Resident
Posts: 1165 Joined: Thu Feb 12, 2004 7:19 pm
Location: ohio
Post
by tim » Mon Mar 15, 2004 8:12 pm
ill's method would be a far better approach/more easy and also a faster method. simple enough too even I can understand it