How to make a ranking list in PHP?

Question

I'm making a ranking list based on user earned,

I made the top 10 ranking when it was displayed but i need to show the ranking from the user but he doesn't have the top 10 ranking

example:

rank1: Jhon
rank2: Jhon 2
rank3: Jhon 3
rank4: Jhon 4
rank5: Jhon 5
rank6: Jhon 6
rank7: Jhon 7
rank8: Jhon 8
rank9: Jhon 9
rank10: Jhon 10
rank465: Jhon 465 (based on user login)

I need Jhon 465 and his rank displayed in the ranking list

here the php sql code

i use smarty framework to show myphp

/** PHP SQL CODE **/

$q = $db->query("SELECT user_id,earned," . $query24h . ", " . $query7d . ", " . $query30d . " FROM bonusads_stats ORDER BY " . $orderby . " DESC LIMIT 10");

while ($r = $db->fetch_array($q)) {

$r['username'] = $db->fetchOne("SELECT username FROM members WHERE id=" . $r['user_id']);
$r['country'] = $db->fetchOne("SELECT country FROM members WHERE id=" . $r['user_id']);
$r['type'] = $db->fetchOne("SELECT type FROM members WHERE id=" . $r['user_id']);
$flag = $bacontest_flags[$r['country']];
$r['country'] = strtolower($flag);
$r['type'] = $membership_name[$r['type']];
$top10[] = $r;
$r['earned'] = $db->fetchOne("SELECT earned FROM bonusads_stats");

}


/** SHOW PHP CODE (USE SMARTY) **/

{foreach from=$top10 item=foo key=k}
            <tr style="text-align:center">
                <td><span class="rc-position">{$k+1}</span></td>
                <td>{$foo.type}</td>
                <td> <img src="images/forum/flags/{$foo.country}.png"  style="margin-right : 8px" title="{$item.member.country}" />{$foo.username}</td>
                <td>{$foo.last24hours}</td>
                <td>${$foo.earned}</td>
                <td>${$foo.earned * 10}</td>
            </tr>
{/foreach}

how to display the ranking of users login but he is not in the top 10 ranks

You should also look into using [joins](http://www.mysqltutorial.org/mysql-join/) to get all the data from one single query instead of making multiple requests to the database inside a loop like that. — M. Eriksson, Aug 28 '19 at 14:22
like @MagnusEriksson said you ideally should be joining ... You are "DoSsing" your RDMS with selects now a [N+1 select problem](https://stackoverflow.com/questions/97197/what-is-the-n1-selects-problem-in-orm-object-relational-mapping) is not ideal most likely you should be fine on primary keys, assuming the JOIN can use indexes it would be still faster then this code. — Raymond Nijland, Aug 28 '19 at 14:40
. Well the reverserd ORDER BY maybe makes it a bit more tricky to optimize a JOIN on the more older MySQL (5.1 - 5.6) versions optimizers as it most likely would need to make a (memory/disk) internal temporary table and do CPU sorting if MySQL access the wrong table first.. The newer optimizer form MySQL 5.7+ would tempt the avoid the temporary table in most cases meaning accessing the table first on which the `ORDER BY` is needed.. — Raymond Nijland, Aug 28 '19 at 14:45
Also you should read about [preventing SQL injections](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1).. — Raymond Nijland, Aug 28 '19 at 14:46
What make `John 465` different to all other John's? I guess you want Top 10 users + one particular user? — Juan Carlos Oropeza, Aug 28 '19 at 14:49

score 0 · Answer 1 · answered Aug 28 '19 at 14:52

0

The best solution is to have two queries, one for the top 10 and one for user score.

For example, make a class Score. Which one will have two methods. One for the top 10 and one for a single user score.

Let's say both methods return arrays. So then you just need to marge arrays and print out them.

answered Aug 28 '19 at 14:52

Nemanja Jeremic

334
4
18

Imagine if i was the topicstarter... How would i do this? The point off answering is not to generate new questions by the topicstarter? – Raymond Nijland Aug 28 '19 at 14:53
Yes, I know, but if He goes with this logic which one He uses right now. He will have a lot's problems later. That's why my answer is like this – Nemanja Jeremic Aug 28 '19 at 14:56

Juan Carlos Oropeza · Answer 2 · 2019-08-28T14:57:39.463

-1

Just add the desire user at the end. If user is already on the top 10. UNION ALL remove the duplicated. You include user_id to avoid cases where two user had same name.

 SELECT user_id, user
 FROM YourTable
 ORDER BY orderField
 LIMIT 10

 UNION ALL

 SELECT user_id, user
 FROM YourTable
 WHERE user_id = @user_id

edited Aug 28 '19 at 14:57

answered Aug 28 '19 at 14:27

Juan Carlos Oropeza

47,252
12
78
118

Not one of the downvoters... But SQL is defined to be **orderless** in tables and resultsets, this assumes a order.. – Raymond Nijland Aug 28 '19 at 14:50
Thanks @RaymondNijland You are right I forgot the order by. But not sure that is the reason of downvotes :? – Juan Carlos Oropeza Aug 28 '19 at 14:51
Don't forgot to mention aswell that `ORDER BY orderField` has to be done on a deterministic column or the ORDER BY expression needs to be deterministic in total... Otherwise the results are still non deterministic ("random") on equal values.. – Raymond Nijland Aug 28 '19 at 14:59
@RaymondNijland Well he already uses `ORDER BY` in his query. So I hope he already considers how to handle ties. But my worries is question isn't clear. Don't know why so many johns, what is the order criteria, where calculate the rank for display. etc. – Juan Carlos Oropeza Aug 28 '19 at 15:02

How to make a ranking list in PHP?

2 Answers2