Hello guys, I'm having a bit of a coding problem ... can't find the solution to a certain problem so hoping maybe some of you can help. I'm creating a table where each member has a certain number of points and I have to display their overall position in terms of points. Thing is I don't know where to start ... Example Points Member +--------------+------ 34 Rob 12 Jon 8 Mik 45 Pin 15 Did Code (markup): If we order the points by ascending order they become 8,12,15,34,45 and from there we can work out that Rob is in 4th position. Question is how do I translate this to code? Any insight appreciated
There is a way to do it all in mysql, by creating a temporary variable and incrementing it, but i dont know how So what id do is return the query to php, loop round the results incrementing a variable until i found the right username, and there is your postiion
Yeah I realise I could do a loop in php however I'd rather there was a mysql solution to it. The loop thing could take a while especially if I have 1,000 members and I have to work out two positions for them each. Hmm.
look up temporary mysql variables, youll prob be able to find what you need, else hope someone else on here knows more about them
If you just want to know Robs Position try: (this is from memory, w/o much testing); $sql="Select * from users where Member='Rob'"; $res=mysql_query($sql); $row=mysql_fetch_object($res); //Count is quick, much quicker than a PHP loop, or a bid mysql querry. $sql2="select count(1) from users where Points>".$row->Points; $userRanka=mysql_fetch_row(mysql_query($sql2)); $userRank=$userRanka[0]+1; PHP: If you want to display the top 10 users I'd do it a more conventional way with an order by and a fetch_object. -Jason
While the DopeDomains way is probably most elegant, here is the SQL way: SELECT a.*, @num := @num + 1 Rank FROM users a, (SELECT @num := 0) d ORDER BY points;