geeves geeves - 2 months ago 7
MySQL Question

How to flatten MySQL results

There are two tables. One table contains player information, like name and rank. The other table contains game substitution information.

the Output should look like this:

Rank| Player Name | Period 1 position | Period 2 Position| Period 3 Position |


Player Table Sample

|ID| Name | Rank |
|1 | Fred | 10 |
|2 | Dan | 12 |
|3 | Mike | 15 |


Position Table sample

|ID| Game_ID | Player_ID | Period | Position |
|1 | 12 | 1 | 1 | Striker Right|
|2 | 12 | 1 | 2 | Mid Center |
|3 | 12 | 2 | 1 | Striker Left |


Below is the code to get it out of MySQL

I've tried various ways transform the returned data set.

$players_sql = "SELECT position.id as position_id,
player.rank,
player.first_name,
position.period,
position.position
FROM player
INNER JOIN position
on player.id = position.player_id
ORDER BY player.rank, position.period;";

if ($result = $mysqli->query($players_sql)) {
// printf("Select returned %d rows.\n", mysqli_num_rows($result));
/* fetch associative array */
while ($row = $result->fetch_assoc()) {
$subs[$row["position_id"]] = [
"rank" => $row["rank"],
"first_name" => $row["first_name"],
"period" => $row["period"],
"position" => $row["position"]
];
}
mysqli_free_result($result);
}

// collapse players This works
foreach ($subs as $sub) {
$players_by_rank[$sub["rank"]] = $sub["first_name"];
}


Later on the data is data is displayed in a form a user can submit. The code below does NOT match the code above. The code below used static defined arrays with pre-filled data that did not come from the database. It provides a way to visualize what the output needs to be. In the code example below there are a total of 4 periods that go into 4 columns and a summary column at the end. There must be an easy way to flatten this data!

<?php
for ($sub = 1; $sub < $sub_info . length; $sub++) {
?>
<tr>
<td><?php echo $sub_info[sub] ?></td>
<td><input type = "text"
name = "p<?php echo $player; ?>"
value = "<?php echo $names[$player - 1]; ?>"
id = "p<?php echo $player; ?>" ></td>
<?php
for ($period = 1; $period < $periods + 1; $period++) {
?>
<td>
<div class="form-group">
<select onchange="calTotals(<?php echo $player . ',' . $period; ?>)"
id="<?php echo "p{$player}p{$period}"; ?>"
name="<?php echo "p{$player}p{$period}"; ?>">
<?php
$output = "";
for ($i = 0; $i < $players_on_field + 1; $i++) {
$output = "<option";
if ($i == 0) {
$output .= ' selected';
}
$output .= ">$i</option>";
echo $output;
}
?>
</select>
</div>
</td>
<?php } ?> <!-- Periods -->
<td><!-- total periods in game using JavaScript -->
<input type="text"
name="<?php echo "periods{$player}"; ?>"
id="<?php echo "periods{$player}"; ?>">
</td>
</tr>
<?php } ?> <!-- players -->

Answer

You can use join to connect the tables, one instance of the players table, and three of the positions (one per period)

SELECT pl.rank, pl.name, 
  po1.position as period_1_position, 
  po2.position as period_2_position, 
  po3.position as period_3_position from 
  player as pl 
  inner join position as po1 on pl.id = po1.player_id and po1.period=1  
  inner join position as po2 on pl.id = po2.player_id and po2.period=2  
  inner join position as po3 on pl.id = po3.player_id and po3.period=3