php - 尝试将 MYSQL 查询分成两部分

Question

我正在为游戏联盟构建动态 HTML 表。我遇到的问题是，只有当该特定游戏发布分数时才会绘制表格列。如果没有分数，比如在锦标赛开始时，就根本没有牌 table 。

我需要拆分查询，以便无论是否有分数，都会从游戏列表构建表的标题。

// Write query to retrieve tournament games
$strSQL = "SELECT nfojm_djc2_items.name AS game, nfojm_djc2_items.rom AS rom, nfojm_users.username AS name, submit_score.gamescore AS score, nfojm_comprofiler.avatar AS avatar
FROM nfojm_djc2_items, nfojm_users, submit_score, select_game, nfojm_comprofiler
WHERE submit_score.gameID = select_game.id
AND select_game.gamecatalogID = nfojm_djc2_items.id
AND submit_score.userID = nfojm_users.id
AND submit_score.userID = nfojm_comprofiler.user_id
AND submit_score.tournID = $tournID
ORDER BY submit_score.gamescore DESC";

// Execute the query.
$query = mysqli_query($con, $strSQL); 

while ($row = mysqli_fetch_array($query)) {  

    if (!in_array($row["game"], $games)) {
        $i=0;
        $indexes[$row["game"]] = 0;
        $games[] = $row["game"];     
    } else {    
        $indexes[$row["game"]]++;   
        $i = $indexes[$row["game"]];
    }

    if (!in_array($row["rom"], $roms)) {
        $i=0;
        $indexes[$row["rom"]] = 0;
        $roms[] = $row["rom"];     
    } else {    
        $indexes[$row["rom"]]++;   
        $i = $indexes[$row["rom"]];
    }

    $scores[$i][$row["game"]] ="<div style='float:left; margin-right:7px;'><img src='http://www.arcadeicons.com/images/comprofiler/" . $row["avatar"] . "' height='35' width='35' style='border-radius:50%'></div>" . $row["name"] . "<br><h3>" . $row["score"] . "</h3>";

}
// Close the connection
mysqli_close($con);

print ('<table><tr><td><IMG SRC="http://www.arcadeicons.com/images/jem/venues/small/'.$loc_image.'" style="height:86px; margin-right:8px; border-radius:5%"></td>');
print ('<td><p style="text-align: left; margin-bottom:15px;"><h1 style="color:#222222;">'.$tourn_name.'</h1></p>');
print ('<p style="text-align: left;"><h3>'.$venue_name.' , '.$city_name.' , '.$state_name.' , '.$country_name.'</h2></p>');
print ('<p style="text-align: left;"><h3>'.$tourn_date.'</h2></p></td></tr></table>');


// PUT SCORES TO SCREEN
// GAMES
print('<table class="rounded" style="border:2px solid #999999;"><thead><tr>');

foreach ($roms as $rom) {
    print("<th class='block'><div style=\"width:130px;height:35px;background-image: url(http://www.arcadeicons.com/media/marquees/{$rom}1.png);background-size:cover;background-position:center center;\"></div></th>");
}
print("</tr></thead><tbody><tr>");
foreach ($games as $game) {
    print("<td class='block' style='text-align:center; background-color:#444444; color:white;'><h4>{$game}</h4></td>");
}

print("</tr>");

foreach ($scores as $score) {

    print("<tr>");        

      foreach ($games as $game) {

        if ( isset($score[$game]) ){
            $ps = $score[$game];
        } else {
            $ps = "";
        }

        print("<td style=\"padding-left:8px;\" class=\"block\"><h4>{$ps}</h4></td>");
    }
}

print("</tr></tbody></table>");

发布分数后，结果如下所示。

picture of table when rendered

Answer 1

我重写了您的查询，以便它选择所有记录，包括那些没有分数的记录，但您的 where 子句和 order by 子句都引用分数表中的列。

您需要决定使用什么来包含记录并适当修改 where 子句，并将其添加到 order by 子句中。

也许您有一个字段可以在其他表之一中标识锦标赛？

SELECT i.`name` AS `game`,
    i.`rom` AS `rom`,
    u.`username` AS `name`,
    IFNULL(s.`gamescore`,'No Score Yet') AS `score`,
    p.`avatar` AS `avatar`
FROM `nfojm_djc2_items` i
JOIN `select_game` g
    ON g.`gamecatalogID` = i.`id`
JOIN `nfojm_users` u
    ON s.`userID` = u.`id`
JOIN `nfojm_comprofiler` p
    ON u.`id` = p.`user_id`
LEFT JOIN `submit_score` s
    ON s.`gameID` = g.`id`
        AND s.`userID` = u.`id`
WHERE s.`tournID` = '$tournID'
ORDER BY s.gamescore DESC;