PHP MYSQL query result "RANKING"

I need to get a list of users Ranking by points and from my command line (MySQL) is was able to generate the necessary code:

SET @rank=0;
SELECT rank, iduser, pontos FROM (
SELECT @rank:=@rank+1 AS rank,
       SUM(points.points) AS pontos,
       points.iduser,
       users.name,
       users.idade
  FROM points
       INNER JOIN
       users
       ON (points.iduser = users.id)
 WHERE (users.idade >= %s) AND (users.idade <= %s)
GROUP BY points.iduser ORDER BY pontos DESC) AS totals WHERE iduser = %s

The problem is that I need this to run on AMFPHP and I´ve tested it in a test PHP file and seems that I can´t use the SET and SELECT in the same "mysql_query".

I´ve looked and some used to mysql_query to do this (I´ve tested it and it works), but can I trust this to be effective and error free? Does it work like in MySQL transactions or setting the @rank in a seperated query may cause unexpected results?

This question and answers originated from www.stackoverflow.com
Question by (12/31/2010 3:25:42 AM)

Answer

Use this query without SET:

SELECT rank, iduser, pontos FROM (
SELECT @rank:=@rank+1 AS rank,
       SUM(points.points) AS pontos,
       points.iduser,
       users.name,
       users.idade
  FROM points
       INNER JOIN
       users
       ON (points.iduser = users.id)
             INNER JOIN
             (SELECT @rank :=0)
 WHERE (users.idade >= %s) AND (users.idade <= %s)
GROUP BY points.iduser ORDER BY pontos DESC) AS totals WHERE iduser = %s
Answer by
Find More Answers
Related Topics  php  mysql  ranking
Related Questions

  • PHP/MYSQL query ranking using secondary tables

    Working with a ranking algorithm, but I'm getting frustrated with the multiple database calls and data gyrations i'm having to go through to calculate the rank of a specific item, output it in to an…

  • Swiss Ranking System Mysql Query Question

    I'm creating a video ranking website. Each video goes up against another video in a match up. Then the user moves forward to the next matchup. The following query returns results sorted by win lo…

  • MySQL query to dynamic "Ranking rows"

    I'm having problems running a query ranking. The inner SELECT gives the rows in order of ranking, for each line, the variable @rank increases, if not a position equal to the previous ranking. But th…

  • mySQL Ranking (and draws)

    Next weekend we're having a competition with 3 qualifications a semifinal and a final. Only the best 15 participants could compete in the semifinal. Only the best 6 compete in the Finals. in the …

  • MySQL Query Ranking

    I have this MySQL query for list a "Vote Ranking" for "Actions" and it work fine, but I want what the "id_user" doesn't repeat, like made a "DISTINCT" in this field. SELECT count(v.id) votos, v.…

  • How can I reduce the size of this Ranking Query in MySql?

    I've got a ranking query that ranks the performance of teams in challenges. The hierarchy of data is as follows: teams have members members have activities activities have activitytypes challenge…

  • Optimize slow ranking query

    I need to optimize a query for a ranking that is taking forever (the query itself works, but I know it's awful and I've just tried it with a good number of records and it gives a timeout). I'll b…

  • MySQL- Trigger updating ranking

    I'm creating a DB with "Team" table for some NFL teams, and I have assigned them all a ranking (standing in NFL), the attribute is called "Ranking". I want to create a trigger such that if the ra…

  • PHP MySQL Query most popular in last 24 hours

    Say I want to get ten records with the MOST likes in the last 24 hours. Here's what I have so far: $date = date("o-m-d"); $query = "SELECT date_created,COUNT(to),from,to FROM likes WHERE date_cre…

  • MySQL/PHP - Query a result set?

    I have a result set that I want to filter. Question is how can I run a query on a result set? Using PHP and MySQL. Thanks