尝试将MYSQL查询拆分为两部分

Question

I'm building a dynamic HTML table for a gaming league. The problem I am having is that the table columns are only drawn when there is a score posted for that particular game. If there are no scores, say at the beginning of a tournament, there's no table at all.

I need to split my query so that building the headers of my table from my games list happens regardless of whether there are any scores or not.

// 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>");

Results look like this when there are scores posted.

picture of table when rendered

Answer 1

I've re-written your query so that it will select all records, including those without a score, except that your where clause and order by clause both reference a column from the score table.

You will need to decide what to use to include records and modify the where clause appropriately, and add to the order by clause.

Maybe you have a field that identifies the tournament in one of the other tables?

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;