75 lines
1.9 KiB
PHP
75 lines
1.9 KiB
PHP
<?php
|
|
/**
|
|
* Created by PhpStorm.
|
|
* User: leanderschmedemann
|
|
* Date: 10.04.17
|
|
* Time: 13:24
|
|
*/
|
|
|
|
require_once('includes.inc.php');
|
|
$data = array();
|
|
$error = 0;
|
|
$cid = isset($_GET['category']) ?
|
|
$_GET['category'] : -1;
|
|
$limit = isset($_GET['limit']) ?
|
|
$_GET['limit'] : 10;
|
|
$offset = isset($_GET['offset']) ?
|
|
$_GET['offset'] : 0;
|
|
$db = new Database();
|
|
|
|
if($cid == -1) {
|
|
$overview = $db->query(
|
|
'SELECT "name", COALESCE("highscore", -1) as "highscore"
|
|
FROM "Users"
|
|
ORDER BY COALESCE("highscore", -1) DESC, "name" ASC
|
|
LIMIT $1 OFFSET $2',
|
|
array($limit, $offset)
|
|
)['data'];
|
|
foreach($overview as $row) {
|
|
$name = $row['name'];
|
|
$score = $row['highscore'];
|
|
$place = $db->query(
|
|
'SELECT COUNT(*)
|
|
FROM "Users"
|
|
WHERE "highscore" > $1',
|
|
array($score)
|
|
)['data'][0]['count'] + 1;
|
|
$data[] = array(
|
|
'place' => $place,
|
|
'name' => $name,
|
|
'score' => $score
|
|
);
|
|
}
|
|
} else {
|
|
$overview = $db->query(
|
|
'SELECT u."name", COALESCE(h."score", -1) AS "c_score"
|
|
FROM "Users" u LEFT OUTER JOIN
|
|
(
|
|
SELECT "Users_ID", "score"
|
|
FROM "Highscores"
|
|
WHERE "Categories_ID" = $1
|
|
) h
|
|
ON u."ID" = h."Users_ID"
|
|
ORDER BY "c_score" DESC, u."name" ASC
|
|
LIMIT $2 OFFSET $3',
|
|
array($cid, $limit, $offset)
|
|
)['data'];
|
|
foreach($overview as $row) {
|
|
$name = $row['name'];
|
|
$score = $row['c_score'];
|
|
$place = $db->query(
|
|
'SELECT COUNT(*)
|
|
FROM "Highscores"
|
|
WHERE "score" > $1 AND "Categories_ID" = $2',
|
|
array($score, $cid)
|
|
)['data'][0]['count'] + 1;
|
|
$data[] = array(
|
|
'place' => $place,
|
|
'name' => $name,
|
|
'score' => $score
|
|
);
|
|
}
|
|
}
|
|
|
|
$response = Database::create_response($data, $error);
|
|
print_r($response); |