SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Getting current players Rank?

    I'm trying to figure out how to get a specific users rank depending on the users average points, but with no luck...

    Here is what I have tried:
    Code:
    $sql = mysql_query("SELECT COUNT(*) rank FROM ".$prefix."_users WHERE average_points > (SELECT points/played AS average_points FROM ".$prefix."_users WHERE id = $currplayer) AND played > 24") or die (mysql_error());
    This can not be done due to the average_points is set within the second SELECT... What to do?

    Thanks in advance.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    what is played>24 for?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    what is played>24 for?
    The player should atleast have played 25 games before the player can get a rank.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    two step solution:
    Code:
    CREATE VIEW player_averages 
    AS
    SELECT points/played AS average_points 
      FROM ".$prefix."_users 
     WHERE played > 24
    ;
    SELECT COUNT(*) + 1 AS rank 
      FROM player_averages
     WHERE average_points > 
           ( SELECT average_points
               FROM player_averages
              WHERE id = $currplayer )
    ;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Tried this:

    PHP Code:
    $sql mysql_query("CREATE VIEW player_averages 
    AS
    SELECT points/played AS average_points 
      FROM "
    .$prefix."_users 
     WHERE played > 24
    ;
    SELECT COUNT(*) + 1 AS rank 
      FROM player_averages
     WHERE average_points > 
           ( SELECT average_points
               FROM player_averages
              WHERE id = 
    $currplayer )
    ;"
    ) or die (mysql_error()); 
    But got this error:
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT COUNT(*) + 1 AS rank FROM player_averages WHERE average_points > ' at line 7

    This is a complicated solution and I can't figure out where it does go wrong? Please help...

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i don't do php, but i seem to recall that php can only execute one SQL statement at a time

    you should only need to create the view once, though, so you might as well do that outside of php
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    You might as well do that outside of php
    Sorry, not sure what you mean?

    Now I tried this:
    PHP Code:
    $sql mysql_query("CREATE VIEW player_averages 
    AS
    SELECT points/played AS average_points 
      FROM "
    .$prefix."_users 
     WHERE played > 24
    "
    );

    $sql mysql_query("SELECT COUNT(*) + 1 AS rank 
      FROM player_averages
     WHERE average_points > 
           ( SELECT average_points
               FROM player_averages
              WHERE id = 
    $currplayer )
    ;"
    ) or die (mysql_error());
    $row mysql_fetch_assoc($sql); 
    Got this error: Unknown column 'id' in 'where clause'

    Any ideas?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jmansa View Post
    Sorry, not sure what you mean?
    you only need to create the view once -- in fact, you cannot create it more than once

    so putting it into your php program seems pointless (to me), because you're just going to have to bypass it on every execution other than the very first... ever

    running it outside of php means through some front end utility program like the mysql browser, phpmyadmin, heidisql, or similar

    Quote Originally Posted by jmansa View Post
    Got this error: Unknown column 'id' in 'where clause'
    aaaaack!!! my bad

    the view actually needs the id as well...
    Code:
    CREATE VIEW player_averages  
    AS 
    SELECT id
         , points/played AS average_points  
      FROM user_points  
     WHERE played > 24
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not sure I understand views... Is the view then allways updated?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jmansa View Post
    Not sure I understand views... Is the view then allways updated?
    no

    a view is a saved SELECT statement

    it can be used, as in this case, to reduce complexity

    it can also be used together with GRANT to implement a security scheme where certain people don't get to see certain columns or rows in a table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    no

    a view is a saved SELECT statement

    it can be used, as in this case, to reduce complexity

    it can also be used together with GRANT to implement a security scheme where certain people don't get to see certain columns or rows in a table
    So every time I call the this it will update?

  12. #12
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you only need to create the view once -- in fact, you cannot create it more than once
    Code:
     CREATE 
         OR
    REPLACE VIEW player_averages  
    AS 
     SELECT id
          , points/played AS average_points  
       FROM user_points  
      WHERE played > 24
    This is not to say that the code for view creation should be repeated every time the view is used by a query. It just saves an explicit DROP VIEW command.

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jmansa View Post
    So every time I call the this it will update?
    no

    all it is, is the actual SQL statement

    it's kind of like a code variable, in that when you write a query that references a view, mysql goes and grabs the SQL for the view, and stuffs it into your query where the view name is, and then proceeds to interpret/parse/execute your query with the view's SQL where the view's name is

    in the case of your ranking query, we need to make two references to the view, and it's more awkward to write a subquery in there where the view name is

    however, it is possible to do it without the view --
    Code:
    SELECT COUNT(*) + 1 AS rank  
      FROM ( SELECT id
                  , points/played AS average_points  
               FROM user_points  
              WHERE played > 24
           ) AS player_averages_1
     WHERE average_points >  
           ( SELECT average_points 
               FROM ( SELECT id
                           , points/played AS average_points  
                        FROM user_points  
                       WHERE played > 24
                    ) AS player_averages_2
              WHERE id = $currplayer )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •