用另一个查询按变量排序？

Question

我想按变量中的最高数字排序，如下图所示：

https://ibb.co/CVh4n5L

我怎么能做到呢？或者如果你有更好的解决方案..。请解释并给我举几个例子。这是我的代码：

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$num_a1 = $conn->query('SELECT COUNT(*),a_topics.forum_id,a_forums.id,a_forums.parent_id,a_forums.gameid 
                        FROM a_topics 
                        JOIN a_forums ON a_topics.forum_id = a_forums.id 
                        WHERE a_forums.gameid = 63')->fetchColumn(); 
$num_a2 = $conn->query('SELECT COUNT(*),a_topics.forum_id,a_forums.id,a_forums.parent_id,a_forums.gameid 
                        FROM a_topics 
                        JOIN a_forums ON a_topics.forum_id = a_forums.id 
                        WHERE a_forums.gameid = 4')->fetchColumn(); 

$stmt = $conn->prepare('SELECT id,name,parent_id,thisisgame,f_img 
                        FROM a_forums 
                        WHERE parent_id = 1 AND thisisgame = 1 
                        GROUP BY id ORDER BY $num ? ... ASC');
$stmt->execute();
$result = $stmt->fetchAll();
foreach($result as $row) {
    echo "Name: $row[1] Topics:";
    switch (true) {
    case $row[0] === '63' :
        echo "$num_a1";
        break;
    case $row[0] === '4' :
        echo "$num_a2";
        break;
    }

Answer 1

您应该使用一个JOIN在一个查询中获取所有信息，然后可以根据计数进行排序。

$stmt = $conn->prepare("
    SELECT f.id, f.name, f.parent_id, f.thisisgame, f.img, IFNULL(t.count, 0) AS count
    FROM a_forums AS f
    LEFT JOIN (
        SELECT t.forum_id, COUNT(*) AS count
        FROM a_topics AS t
        JOIN a_forums AS f ON t.forum_id = f.id
        WHERE f.gameid IN (4, 63)
        GROUP BY gameid
    ) AS t ON t.forum_id = f.forum_id
    WHERE f.parent_id = 1 AND f.thisisgame = 1
    ORDER BY count ASC");
$stmt->execute();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo "Name: {$row['name']} Topics: {$row['count']}";
}