MySQL Multiple table data selection

Question

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?

What if the `User` column is different for different golfers? — Barmar, Sep 18 '16 at 22:15

score 0 · Accepted Answer · answered Sep 18 '16 at 22:17

0

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"
}

answered Sep 18 '16 at 22:17

Barmar

741,623
53
500
612

Very close, now I can see the initial header for User1, but I am unable to get subsequent users. – Trex Sep 18 '16 at 22:28
See http://stackoverflow.com/questions/27575562/how-can-i-list-has-same-id-data-with-while-loop-in-php/27575685#27575685 – Barmar Sep 18 '16 at 23:00
There's nothing in the question about subsequent users. All your players have the same `User1`. I asked what you wanted to do if the users were different, you didn't answer. – Barmar Sep 18 '16 at 23:01
Sorry, I just saw that. I looked at the post that you linked me to and I was able to get that to work for my issue. Thanks for the help! – Trex Sep 18 '16 at 23:22
I don't understand why smart people have trouble figuring that out by themselves. If you want to print something only when it changes, just remember the old value in a variable, and compare it with the new value. – Barmar Sep 18 '16 at 23:24
Just a littleclarification - I have 0 background in programming at all. I started playing with PHP and MySQL about a month ago and was able to figure it out to this point. This is just a fun project I'm working on in my spare time. What might seem like the most logical thing to you isn't necessarily something that springs to my mind immediately. I'm still trying to figure out what is possible and how to make it work. I appreciate your help. – Trex Sep 18 '16 at 23:28

MySQL Multiple table data selection

1 Answers1