FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


fastest way to get all rank from database?





Mgccl
here is the query
Code:
   $query = "SELECT count(stat_id)
      FROM user_stat
      WHERE stat_exp > (
          SELECT stat_exp
          FROM user_stat
          WHERE stat_id=$id
       )";


as you can see, this query return only 1 result. and the result is where a stat_id will be located when the stat_id = $id
but, here is the trick, what if, there are a LOT of them?
I mean, count could be used with group by. But the problem is, I used a subquery, where the subquery is supplying data that is larger for 1 $id only.
then, what could I do if I want to return the location of stat_id and for more than 1 id?

and what if. I want more?
if there is more than stat_exp, there are stat_win, stat_lose, stat_draw and such?

how could that be possible?
hexkid
Mgccl wrote:
what could I do if I want to return the location of stat_id and for more than 1 id?

You may be able to come up with a SQL based solution for this.
Maybe using "IN" in your SQL???
I didn't test it but something like
Code:
SELECT ...
FROM ...
WHERE somecalculatedstuff IN (
  SELECT somecalculatedstuff
  FROM ...
  WHERE ...
)
GROUP BY ...
where "somecalculatedstuff" would create a link between the different columns, eg. user_id*1000+points

Again, I'm just thinking out loud, I didn't test any of this.
Your other option (doing a query for every ID) may still be the best.

Mgccl wrote:
and what if. I want more?
if there is more than stat_exp, there are stat_win, stat_lose, stat_draw and such?

how could that be possible?

For this particular combination I'm (almost) willing to bet you can't do it.
Mgccl
yes... you are right about it... all I can come up is... get more field result for one id like this one



Code:
$query = "SELECT (SELECT count(stat_id)
      FROM user_stat
      WHERE stat_exp > (
          SELECT stat_exp
          FROM user_stat
          WHERE stat_id=$id
       )) AS stat_exp_rank,
 (SELECT count(stat_id)
      FROM user_stat
      WHERE stat_elo > (
          SELECT stat_elo
          FROM user_stat
          WHERE stat_id=$id
       )) AS stat_elo_rank
FROM user_stat";


cool.... subsubqueries.... I never know them untill I found no one in the forum can solve the problem... so I think I just have to take care myself....
Related topics
WTF! A strange database just appeared lol
script backup database
Worlds Second Fastest Supercomputer
Linking from a high page rank site
How much database
What is the best antivirus progrem?
database problem
The Best Way to Get Page Rank 10 in GOOGLE
Implementing your database access in PHP as a layer.
2 suggestions
how can i change password in my database?
Database Problem
Including Rank
How to move SQL database to new domain?
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.