Trex Trex - 2 months ago 7
MySQL Question

MySQL Multiple table data selection

I've got two tables I'm trying to join, the first table stores information related to golfers and scores they have in various tournaments (a new table for each tournament, all the same structure), the second table is full of users and 5 golfers they've picked for each tournament:

Table 1 (MField):

+---------------+------+------+----+------+------+
| MGolf | Rd1P | Rd2P | CP | Rd3P | Rd4P |
| Jason Day | 0 | -1 | 5 | 2 | -1 |
| Jordan Spieth | 6 | -2 | 5 | -1 | -1 |
| Rory McIlroy | 2 | 1 | 5 | -5 | 3 |
+---------------+------+------+----+------+------+


Table 2 (Rosters):

+-------+---------------+
| User | GName |
| User1 | Jason Day |
| User1 | Jordan Spieth |
| User1 | Rory McIlroy |
+-------+---------------+


My ideal output is:

+---------------+------+------+----+------+------+
| User1 | Rd1P | Rd2P | CP | Rd3P | Rd4P |
| Jason Day | 0 | -1 | 5 | 2 | -1 |
| Jordan Spieth | 6 | -2 | 5 | -1 | -1 |
| Rory McIlroy | 2 | 1 | 5 | -5 | 3 |
+---------------+------+------+----+------+------+


What I'm seeing is:

+---------------+-----+-----+-----+-----+-----+
| User1 | Rd1 | Rd2 | Cut | Rd3 | Rd4 |
| Jason Day | 0 | -1 | 5 | 2 | -1 |
| User1 | Rd1 | Rd2 | Cut | Rd3 | Rd4 |
| Jordan Spieth | 6 | -2 | 5 | -1 | -1 |
| User1 | Rd1 | Rd2 | Cut | Rd3 | Rd4 |
| Rory McIlroy | 2 | 1 | 5 | -5 | 3 |
+---------------+-----+-----+-----+-----+-----+


I'm using the following query:

$sql = "
SELECT
User,
GName,
MGolf,
Rd1P,
Rd2P,
CP,
Rd3P,
Rd4P
FROM
Rosters,
MField
WHERE
roster.GName = MGolf
";

$result = $link->query($sql);

echo '<table>';

if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo '<tr><td>' . $row['User'] . '</td><td>Rd1</td><td>Rd2</td><td>Cut</td><td>Rd3</td><td>Rd4</td></tr>';
echo '<tr><td>' . $row['GName'] . '</td><td>'.$row['Rd1P'].'</td><td>'.$row['Rd2P'].'</td><td>'.$row['CP'].'</td><td>'.$row['Rd3P'].'</td><td>'.$row['Rd4P'].'</td></tr>';
}
} else {
echo "0 results";
}
echo '</table>';


The issue that I'm having is that I would like to only show the 'User' Name once, but it's currently alternating every row in the table. I know that's because of how the query is structured but I can't figure out how to show the 'User' once, with the 5 golfers in rows below them, then the golfer info from the 'MField' table. Is there a cleaner way to combine this info?

Answer

In your loop, only print the header row once. Use a variable to track this.

if ($result->num_rows > 0) {
    $header_printed = false;
    // output data of each row
    while($row = $result->fetch_assoc()) {
        if (!$header_printed) {
            echo '<tr><td>' . $row['User'] . '</td><td>Rd1</td><td>Rd2</td><td>Cut</td><td>Rd3</td><td>Rd4</td></tr>';
            $header_printed = true;
        }
        echo '<tr><td>' . $row['GName'] . '</td><td>'.$row['Rd1P'].'</td><td>'.$row['Rd2P'].'</td><td>'.$row['CP'].'</td><td>'.$row['Rd3P'].'</td><td>'.$row['Rd4P'].'</td></tr>'; 
    }
} else {
    echo "0 results"
}
Comments