php - 订购 while 循环数据

Question

我正在制作一个排名系统。但我想要的是将我得到的结果 ($kn) 从最高到最低排序。我该怎么做？

include "includes/core.inc.php";
require "includes/connect.inc.php"; 

$id = $_GET["id"];

$query = "SELECT * FROM submitted WHERE id= '$id'";
$query_run = $db->query($query);
while($row = mysqli_fetch_assoc($query_run)){

    $name= $row["name"];

    $sql = "SELECT * FROM submitted WHERE name= '$name' AND pending = 'Accept'";
    $sql_run = $db->query($sql);
    $count = $sql_run->num_rows;

    $nums= "SELECT * FROM ranking WHERE name= '$name'";
    $nums_run = $db->query($nums);
    $num = $nums_run->num_rows;


    $kn = ($count * 0.4) + (($num * 0.2) * 3);

    echo '$name';
    echo '$kn';
}

Answer 1

遍历列表并查询每个元素几乎不是一个好主意。相反，您可以将整个逻辑移动到查询中，然后在那里对其进行排序:

$query = 
"SELECT   s.name AS name, (cnt_submitted * 0.4) + ((cnt_ranking * 0.2) * 3) AS kn
 FROM     (SELECT   name, COUNT(*) AS cnt_submitted
           FROM     submitted
           WHERE    id = '$id' AND
                    pending = 'Accept'  
           GROUP BY name) s
 JOIN     (SELECT   name, COUNT(*) AS cnt_ranking
           FROM     ranking
           GROUP BY name) r ON r.name = s.name
 ORDER BY 2 DESC";

$query_run = $db->query($query);
while ($row = mysqli_fetch_assoc($query_run)) {

    $name = $row["name"];
    $kn = $row["kn"];

    echo '$name';
    echo '$kn';
}

注意:
$id 变量应该是 prepared statement 中的绑定(bind)变量以防止 SQL 注入(inject)攻击。不过，我将其保留在 OP 中，因为这不是问题的重点，我不想增加额外的混淆。