Bradley Hodges Bradley Hodges - 1 month ago 6
MySQL Question

Ordering SQL results by combined value of row in database

This is going to be a confusing one.. I'll try to explain it and provide as much information as possible. Basically, what I have is a website where people can upload the amount of kills they get in a game. What I'd like to do, is display people's combined, confirmed kills on a leaderboard.. This is proving harder than I expected (LOL).

Here's what my database looks like:

Database Screenshot

submission
is the unique id of the entry.

usergt
is the user's gamertag.

image
is not important.

userkilled
is the person they have killed.

status
is the status of the confirmation of the kill.

conftotal
is the total kills that we confirmed from the image (eg. entry 5 recorded 24 kills).

submitted
is simply a unix timestamp of the submission date and time.

Here's my PHP on the page itself:

<?php
function getKills($gamertag) {
$conn = new mysqli("localhost", "rpsanet_seals", "k_,2N2Xbu}mr", "rpsanet_seals");
if ($conn->connect_error) {die("Connection failed: " . $conn->connect_error);}

$sql = "SELECT conftotal FROM kills WHERE usergt='" . $gamertag . "'";
$result = $conn->query($sql);

$ctotalklls = 0;

if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
$ctotalklls = $ctotalklls + $row["conftotal"];
}
} else {
$ctotalklls = 0;
}

return $ctotalklls;

$conn->close();
}
?>
<div class="mykills_content">
<table class="mykills_table">
<tr>
<th class="mk_first">Avatar</th>
<th>Gamertag</th>
<th>Latest Victim</th>
<th class="mk_last">Total Kills</th>
</tr>
<?php

$conn = new mysqli("localhost", "rpsanet_seals", "k_,2N2Xbu}mr", "rpsanet_seals");
if ($conn->connect_error) {die("Connection failed: " . $conn->connect_error);}

$sql = "SELECT *, COUNT(*) AS magnitude
FROM kills
WHERE status='confirmed'
GROUP BY usergt
ORDER BY magnitude ASC
LIMIT 5";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo '
<tr>
<td class="mykills_submission" data-submission="' . $row["submission"] . '">
<div class="mykills_avatar">
<img src="https://avatar-ssl.xboxlive.com/avatar/' . $row["usergt"] . '/avatarpic-l.png" class="mykills_victim_avatar" draggable="false" />
</div>
</td>

<td>
' . $row["usergt"] . '
</td>

<td>
' . $row["userkilled"] . '
</td>

<td>
' . getKills($row["usergt"]) . '
</td>
</tr>

';
}
echo '</table>';
} else {
echo '</table><div class="fw-nokills">You have not submitted any kills yet.</div>';
}

$conn->close();
?>
</div>
</div>


and this is what it looks like on the page:

page sample

I have no idea what I've done wrong or how to fix it. As you can see, the order is incorrect. It should go CheapApples12, then KILLER C00KIE X, then ancrobbo97.

If anything else is not clear, or any other info is needed, just comment and I'll get that to anyone as soon as I can..

I appreciate any help in advance :)

IMPORTANT INFO: The "Total Kills" that are being shown in the screenshot above are being generated from the getKills() function, not the database query result.

Answer

Try this simple one liner use sum(conftotal) and use order by sum(conftotal)

SELECT *,sum(conftotal) as totalkills FROM kills WHERE status='confirmed' GROUP BY usergt ORDER BY totalkills desc