Trying to split a MYSQL query into two parts

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

Your code is likely vulnerable to [**SQL injection attacks**](https://en.wikipedia.org/wiki/SQL_injection). You should use [**mysqli**](https://secure.php.net/manual/en/mysqli.prepare.php) or [**PDO**](https://secure.php.net/manual/en/pdo.prepared-statements.php) prepared statements with bound parameters as described in [**this post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — Alex Howansky, Apr 14 '17 at 02:26
Rewrite your query using JOINS instead of the form you are using. For the tables that may be missing records (ie. no score yet), use LEFT JOINs, and for the columns from those tables use the IFNULL function to return a default value (ie. 0 or "no score yet"). — Sloan Thrasher, Apr 14 '17 at 03:08

Sloan Thrasher · Answer 1 · 2017-04-14T15:28:36.590

0

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;

edited Apr 14 '17 at 15:28

answered Apr 14 '17 at 03:22

Sloan Thrasher

4,953
3
22
40

Thank you so much for this help. I'm using MYSQL, I tried to run this query in phpMYAdmin to test but it keeps throwing syntax errors, is this in a different format? – Joel 'Maximus' Griffin Dodd Apr 14 '17 at 14:11
What is the syntax error? It should tell you where it is. – Sloan Thrasher Apr 14 '17 at 14:18
If you are using phpAdmin, you will need to substitute the value $tournID with a value. – Sloan Thrasher Apr 14 '17 at 14:20
Yes I replaced the$tournID variable with a value. Here is the syntax error. #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'nfojm_djc2_items` i JOIN `select_game` g ON g.`gamecatalogID` = i.`id` JO' at line 5 – Joel 'Maximus' Griffin Dodd Apr 14 '17 at 15:27
Ahah! Missing back tic on avatar on the line above. I'll edit the answer – Sloan Thrasher Apr 14 '17 at 15:28
Did that fix it? – Sloan Thrasher Apr 14 '17 at 15:47
Good catch. Another error now though. #1054 - Unknown column 's.userID' in 'on clause' – Joel 'Maximus' Griffin Dodd Apr 14 '17 at 15:48
Isn't that the column name in submit_score? If not, put the right column name there. – Sloan Thrasher Apr 14 '17 at 17:32

Trying to split a MYSQL query into two parts

1 Answers1