php/mysqli : top 10 most scores by a member

Question

I am making a scoreboard from a table where members details are stored in one table and scores are stored in another with members foreign keys. I know that I can show the leaderboard with:

$lbquery = mysqli_query($conn, "SELECT mid, SUM( score ) AS count FROM `memberscores` GROUP BY mid ORDER BY count DESC");
$lbrow = mysqli_fetch_array($lbquery);

$memid = $lbrow["mid"];
$memscore = $lbrow["count"];

    $memquery = mysqli_query($conn, "SELECT firstname, lastname FROM `members` WHERE `mid`=".$memid);
    $memrow = mysqli_fetch_array($memquery);
    $fname = $memrow["firstname"];
    $lname = $memrow["lastname"];

echo $lname." ".$fname." = "$memscore;

But I am confused how will show the first ten as it seems that may I have to use a loop to show the first ten in a table. but not sure how it will be used.

Why are you working with a deprecated api and deleted in PHP 7+ ? — Funk Forty Niner, Apr 23 '20 at 16:54
I dont understand, so you mean to say that if something is deleted in php 7+, should no one use? and apps already running commercially with older versions be deleted? — Hashmi, Apr 23 '20 at 21:11
@Hashmi No, no one should use it, and you should by trying to move away from it in your legacy apps. You're just digging yourself in deeper by doing so. If you're using a PHP version that still has `mysql_query`, then it's **dead**, and not even receiving critical security updates anymore. Begin moving away from it, or at least writing your own layer of abstraction around it, so you aren't using it directly and may have some hope of fixing it. — user229044, Apr 24 '20 at 03:44
Does this answer your question? [Why shouldn't I use mysql\_\* functions in PHP?](https://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) — Dharman, Apr 25 '20 at 11:26

score 2 · Accepted Answer · answered Apr 23 '20 at 16:52

I think that you want a simple join:

SELECT m.mid, m.firstname, m.lastname, SUM(s.score) cnt 
FROM memberscores s 
INNER JOIN members m ON m.mid = s.mid
GROUP BY m.mid, m.firstname, m.lastname 
ORDER BY cnt DESC
LIMIT 10

This generates a resultset the top 10 users by total score, that includes the each user's first and last name. You can then fetch the resultset row by row and display the results in your application.

php/mysqli : top 10 most scores by a member

1 Answers1