同时回应i的变量(Echo the variable of i in while)

同时回应i的变量(Echo the variable of i in while)

我尝试这样的事情：

function userrank($userid){ $sql = mysql_query("SELECT * FROM users ORDER BY atacs DESC"); $i = 1; while ($row = mysql_fetch_assoc($sql)) { if ($row['username'] == $userid) { echo 'You are on ' . $i . ' in the general leaderbord'; } } }

在排行榜上，它正确地向我显示排名，但我想在另一页上显示我，在“youraccount”页面上，为此我尝试使用此功能。 哪里不对 ？

i try something like this :

function userrank($userid){ $sql = mysql_query("SELECT * FROM users ORDER BY atacs DESC"); $i = 1; while ($row = mysql_fetch_assoc($sql)) { if ($row['username'] == $userid) { echo 'You are on ' . $i . ' in the general leaderbord'; } } }

On the leaderboard it shows me the rank correctly, but i want to show me on another page too , on the "youraccount" page , for this i try to make this function. what is wrong ?

最满意答案

基本上你在这里做的是计算有多少用户的atacs大于或等于$userid的atacs 。 这个问题：

非常低效。 请注意，数据库检索并向您的while循环发送每个用户的条目，即使是那些atacs小于$userid 。 除了其中一个循环迭代之外的所有循环迭代都没有任何设计。 大量浪费时间将数据从数据库发送到PHP，甚至不使用它。 比必要的更多数据拉回来。 您最终会为整个 users表中的每个用户提供每一行 - 但您的结果只是一个标量数（有多少用户拥有>得分）。 实际上，如果您的分数与其他人相关，则会给您错误的结果。 在这种情况下，具有相同分数的一些用户可以被计为“在用户之上”，其他用户在“用户之下”。

数据库擅长迭代数据; 它是全局“本地”可访问的，如果您可以在SQL中描述您要完成的任务，数据库引擎可以进行许多优化。 因此，不是这样做，为什么不只是在数据库中做所有事情？

set @user_atacs = ( select atacs from users where id = 12 ); select count(*) +1 from users where atacs > @user_atacs;

我在这里嘲笑了这张桌子： http ： //sqlfiddle.com/#！2 / fffaa86 / 3

该解决方案基本上只计算具有比当前用户更高的atacs的用户数量。 具有相同分数的所有用户将获得相同的排名，并且下一排名将适当地更高，因此它不会遭受任何方法的错误。

作为最后一点，最合适的方法是做排行榜，这可能是定期预先计算排行榜，然后使用结果显示每个用户在排行榜中的位置，而不是试图为每个用户动态计算。 但这甚至超出了范围:)

Basically what you're doing here is counting how many users have an atacs greater than or equal to $userid's atacs. Problems with this:

Terribly inefficient. Note the database retrieves and sends to your while loop an entry for every user, even those who have an atacs less than the $userid. All but one of these while loop iterations does nothing by design. Lots of wasted time sending data from the database to PHP, which doesn't even use it. Pulls way more data back than is necessary. You end up with every row for every user in your entire users table - but your result is just a scalar number ( how many users with > score ). Actually gives you wrong results in the case that your score is tied with others'. In this case some users with the same score may be counted as "above" the user, others as "below the users".

Databases are good at iterating over data; it's all "locally" accessible and the database engine can make many optimizations if you can describe in SQL what you are trying to accomplish. So instead of doing it that way, why not just do everything in the database?

set @user_atacs = ( select atacs from users where id = 12 ); select count(*) +1 from users where atacs > @user_atacs;

I've mocked up the table here: http://sqlfiddle.com/#!2/ff9a86/3

This solution essentially just counts the number of users with a higher atacs than the current user. All users with the same score will get the same rank, and the next rank will be appropriately higher, so it doesn't suffer from any of your method's errors.

As a final note, the most appropriate way to do something like leaderboards is probably to precompute the leaderboard periodically and then use the results to show each user's position in the leaderboards, rather than trying to compute it on the fly for each user. But that's even farther out of scope :)