Getting current players Rank?

[jmansa]

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:

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

[r937]

what is played>24 for?

[jmansa]

what is played>24 for?

The player should atleast have played 25 games before the player can get a rank.

[r937]

two step solution:

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

[jmansa]

Tried this:

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

[r937]

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

[jmansa]

You might as well do that outside of php

Sorry, not sure what you mean?

Now I tried this:

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

[r937]

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

Got this error: Unknown column 'id' in 'where clause'

aaaaack!!! my bad

the view actually needs the id as well...

CREATE VIEW player_averages  
AS 
SELECT id
     , points/played AS average_points  
  FROM user_points  
 WHERE played > 24

[jmansa]

Not sure I understand views... Is the view then allways updated?

[r937]

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

[jmansa]

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?

[itmitică]

you only need to create the view once -- in fact, you cannot create it more than once

 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.

[r937]

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

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 )