PHP - Building a Learderboard from MySQL data

Question

I have two sets (users and their tests data) of data from MySQL. I'm going to build a leaderboard from these data in such a way where every user should have an average grade (%), scores (sum of all tests results) and total number of tests that user attended. At the end, when all data is formed for the leaderboard it should be sorted by descending, so first highest average grade and scores with associated user etc. I started with while-loop, but stacked with linking the data into user names.

PHP code:

// contains set of data with fields user_id(INT) and name(VARCHAR)
$users = mysqli_fetch_assoc($result_users);

// contains set of data with fields user_id(INT), socres(DECIMAL), passed(BOOL)
$data = mysqli_fetch_assoc($result_data);

$pos = null; // collect positive tests
$neg = null; // collect negative tests
while ($data = mysqli_fetch_assoc($result_all)) {
    if ($data['quiz_passed'] == 1) {
        $pos += 1;
    } elseif ($data['quiz_passed'] == 0) {
        $neg += 1;
    }
}

Expected Leaderboard Results:

Name     Average (Pos/Neg)     Scores (Sum of scores field)     Total tests (Pos+Neg)
-------------------------------------------------------------------------------------
John     80%                   143                              9

         // 4 Pos / 5 Neg                                       // 4 Pos + 5 Neg

Any help would be appreciated.

UPDATES:

User Table
----------
CREATE TABLE IF NOT EXISTS `user` (
  `user_id` int(11) NOT NULL,        // PRIMARY KEY
  `name` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `fullname` varchar(255) NOT NULL,
  `token` varchar(128) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

(4, 'test1', 'password_here1', 'Tim Roth', 'token_here1'),
(5, 'test2', 'password_here2', 'Christoph Waltz', 'token_here2'),
(6, 'test3', 'password_here3', 'John Travolta', 'token_here3'),


Data Table
----------
CREATE TABLE IF NOT EXISTS `data` (
  `id` int(11) NOT NULL,            // PRIMARY KEY
  `user_id` int(11) NOT NULL,
  `scores` decimal(3,2) NOT NULL,
  `passed` tinyint(1) NOT NULL,
  `time` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

(1, 6, '0.60', 0, '2014-11-12 01:24:43'),
(2, 4, '0.75', 1, '2014-10-31 06:33:48'),
(3, 4, '0.90', 1, '2014-11-02 15:11:09'),
(4, 4, '0.50', 0, '2014-11-06 19:29:19'),
(5, 5, '0.75', 1, '2014-11-07 08:21:44'),
(6, 5, '0.60', 0, '2014-11-10 17:34:00'),
(7, 6, '0.60', 0, '2014-11-11 16:13:50'),
(8, 4, '0.85', 1, '2014-11-12 13:22:49')

My guess is that you want to use a join when querying your data, rather than two individual queries. You should show your current queries as well as schema for your tables so you can get better advice on how to formulate your query. — Mike Brant, Nov 18 '14 at 14:45
To follow @MikeBrant - this should be possible with a single query. It should be possible to calculate the vales and order them within that query. Can you post the table declares and a bit of sample data? — Kickstart, Nov 18 '14 at 14:51
@MikeBrant, please have a look to the **UPDATED** part of my question — bofanda, Nov 18 '14 at 15:35

score 1 · Accepted Answer · answered Nov 18 '14 at 16:26

1

Based on the data, there is one minor issue. Your calculation for average appears to be the count of where pass = 1 divided by where pass = 0. So if someone hasn't failed an exam there will be a divide by zero. While easy enough to code around I would need to know what you want to do in this situation.

SELECT u.user_id, SUM(d.passed) / SUM(IF(d.passed = 0, 1, 0)) AS `Average`, SUM(d.scores) AS `Scores`, COUNT(d.id) AS `Total Tests`
FROM user u
INNER JOIN data d
ON u.user_id = d.user_id
GROUP BY u.user_id;

Not sure if it would be more appropriate to count the number of passes by the number of exams.

SELECT u.user_id, SUM(d.passed) / COUNT(d.passed) AS `Average`, SUM(d.scores) AS `Scores`, COUNT(d.id) AS `Total Tests`
FROM user u
INNER JOIN data d
ON u.user_id = d.user_id
GROUP BY u.user_id;

SQL fiddle to show the 2 results:-

http://www.sqlfiddle.com/#!2/ecf3d2/1

answered Nov 18 '14 at 16:26

Kickstart

21,403
2
21
33

perhaps I was not clear enough during the explanation of question, but regarding the **average** I want to count total number of positive (true or 1) results divded by total number of negatives (false or 1) for each student. This way I want to get the percentage of positive grades for a particular student. – bofanda Nov 18 '14 at 16:50
if we look at the **Expected Leaderboard Results:** we can see that our user John has 4 Positive (passed: TRUE) and 5 Negative (passed: FALSE) tests, in total he attended 9 tests. And the sum of all these tests gives him 143 scores (its just sum of the scores field in mysql table). – bofanda Nov 18 '14 at 16:55
1

You were clear up to that point. But what if user John has 9 positive and 0 negative? This would give 9 / 0 which is infinity (which will probably come out as NULL, which is meaningless, or in php would give a warning error), which is what the first solution would give. The sum of the scores should already be there in both the solutions I have given. – Kickstart Nov 18 '14 at 17:08
In case of 0 total negative for a given user, the result should be 100% which means he/she never failed. – bofanda Nov 18 '14 at 17:17
That is pretty much what the 2nd solution gives. Number of passes divided by number of attempts (multiply it by 100 if you want a percentage). – Kickstart Nov 18 '14 at 17:25
yes I just tested both of them and your second solution did what I want. Thanks for your answers and explanations :) – bofanda Nov 18 '14 at 17:32

score 0 · Answer 2 · answered Nov 18 '14 at 14:53

0

SELECT
    ROUND(SUM(d.passed) / COUNT(*) * 100, 2) AS average,
    SUM(d.score) AS score,
    COUNT(*) AS total
FROM user AS u
INNER JOIN data AS d ON d.user_id = u.id
GROUP BY u.id

answered Nov 18 '14 at 14:53

BreyndotEchse

2,192
14
20

PHP - Building a Learderboard from MySQL data

2 Answers2