I've searched and searched and I'm still pretty lost. What I struggle with is some basics, but I'm usually pretty good at figure things out from examples.
What I need help with: I can't seem to populate a Golf Scorecard HTML table based on my MySQL query. For whatever reason, I wish I knew, the last golfer in my table gets all the values. I can't figure our a way for the data to loop correctly. At hole 9, the loop should stop and move to the next row for the next golfer.
I've been trying Whiles and Foreach loops to figure out how exactly they work which is why I have both. I plan on combining the two queries into one, once I figure out the individual outputs.
I really appreciate any guides. I've been searching for a solution for a few weeks now, and I'm realizing I'm quite stuck.
//Get Hole Info
$holeSQL = $auth_user->runQuery("SELECT HoleNum, FrontBack FROM `tblHole`
WHERE CourseID=:course_id AND FrontBack=:front_back ORDER BY
`tblHole`.`HoleNum` ASC");
$holeSQL ->
execute(array(":course_id"=>$courseID,":front_back"=>$frontback));
//Get Par Info
$parSQL = $auth_user->runQuery("SELECT Par FROM `tblHole` WHERE
CourseID=:course_id AND FrontBack=:front_back ORDER BY `tblHole`.`HoleNum`
ASC");
$parSQL ->
execute(array(":course_id"=>$courseID,":front_back"=>$frontback));
//Get Round Scores
$scoresSQL = $auth_user->runQuery("SELECT tblScore.PlayerID, tblScore.RoundID, tblScore.HoleNum, tblScore.NumStrokes, tblScore.NumPutts, tblScore.FIR, tblScore.GIR FROM tblScore WHERE RoundID=53 ORDER BY tblScore.PlayerID ASC");
$scoresSQL -> execute(array(":round_id"=>$roundID));
$scores = $scoresSQL -> fetchALL(PDO::FETCH_ASSOC);
Array
(
[0] => Array
(
[PlayerID] => 2
[RoundID] => 53
[HoleNum] => 1
[NumStrokes] => 5
[NumPutts] => 2
[FIR] => 0
[GIR] => 0
)
[1] => Array
(
[PlayerID] => 2
[RoundID] => 53
[HoleNum] => 2
[NumStrokes] => 6
[NumPutts] => 2
[FIR] => 0
[GIR] => 0
)
[2] => Array
(
[PlayerID] => 2
[RoundID] => 53
[HoleNum] => 3
[NumStrokes] => 4
[NumPutts] => 2
[FIR] => 0
[GIR] => 0
)
[3] => Array
(
[PlayerID] => 2
[RoundID] => 53
[HoleNum] => 4
[NumStrokes] => 5
[NumPutts] => 2
[FIR] => 0
[GIR] => 0
)
[4] => Array
(
[PlayerID] => 2
[RoundID] => 53
[HoleNum] => 5
[NumStrokes] => 3
[NumPutts] => 1
[FIR] => 0
[GIR] => 0
)
[5] => Array
(
[PlayerID] => 2
[RoundID] => 53
[HoleNum] => 6
[NumStrokes] => 6
[NumPutts] => 2
[FIR] => 0
[GIR] => 0
)
[6] => Array
(
[PlayerID] => 2
[RoundID] => 53
[HoleNum] => 7
[NumStrokes] => 7
[NumPutts] => 3
[FIR] => 0
[GIR] => 0
)
[7] => Array
(
[PlayerID] => 2
[RoundID] => 53
[HoleNum] => 9
[NumStrokes] => 6
[NumPutts] => 3
[FIR] => 0
[GIR] => 0
)
[8] => Array
(
[PlayerID] => 2
[RoundID] => 53
[HoleNum] => 8
[NumStrokes] => 2
[NumPutts] => 1
[FIR] => 0
[GIR] => 1
)
[9] => Array
(
[PlayerID] => 11
[RoundID] => 53
[HoleNum] => 2
[NumStrokes] => 5
[NumPutts] => 2
[FIR] => 1
[GIR] => 0
)
[10] => Array
(
[PlayerID] => 11
[RoundID] => 53
[HoleNum] => 1
[NumStrokes] => 5
[NumPutts] => 3
[FIR] => 1
[GIR] => 0
)
[11] => Array
(
[PlayerID] => 11
[RoundID] => 53
[HoleNum] => 8
[NumStrokes] => 4
[NumPutts] => 2
[FIR] => 0
[GIR] => 0
)
[12] => Array
(
[PlayerID] => 11
[RoundID] => 53
[HoleNum] => 7
[NumStrokes] => 6
[NumPutts] => 2
[FIR] => 0
[GIR] => 0
)
[13] => Array
(
[PlayerID] => 11
[RoundID] => 53
[HoleNum] => 6
[NumStrokes] => 6
[NumPutts] => 2
[FIR] => 0
[GIR] => 0
)
<div class="table-responsive">
<!--<form class="tr" method="post" action="roundupload.php">-->
<table class="table table-bordered m-b-0" id="roundupload">
<thead>
<tr>
<th colspan="1">Hole</th>
<?php
while ($HoleNum = $holeSQL->fetch(PDO::FETCH_ASSOC)){
echo'<th>'.$HoleNum['HoleNum'].'</th>'
;}?>
</tr>
<tr>
<th colspan="1">Par</th>
<?php
while ($parInfo = $parSQL->fetch(PDO::FETCH_ASSOC)){
echo'<th><span class="label label-success">'.$parInfo['Par'].'</span></th>'
;}?>
<th class="text-muted">Total</th>
</tr>
</thead>
<tbody>
<!-- Pull in Shooter Names For Upload Table -->
<?php
foreach($arr as $userInfo){
?>
<tr class="tableRow">
<td class="text-muted" rowspan="1">
<?php echo
$userInfo['user_first'].' '.$userInfo['user_last']?>
</td>
<?php
;}
?>
<?php
foreach ($scores as $holescore){
echo'<td>'.$holescore['NumStrokes'].'</td>'
;}?>
<td id="hole4:h4" class="inner strokes" contenteditable="true"></td>
<td id="stroketotal:s1" class="inner-total"></td>
</tr>
</tbody>
</table>
<!--</form>-->
</div>
Get Round Scores Query. The query below allows me to get pretty much everything I needed in 1 query.
//Get Round Scores
$scoresSQL = $auth_user->runQuery("SELECT tblUsers.user_first, tblUsers.user_last, tblScore.PlayerID, tblScore.RoundID, tblScore.HoleNum, tblScore.NumStrokes, tblScore.NumPutts, tblScore.FIR, tblScore.GIR FROM tblScore INNER JOIN tblUsers ON tblScore.PlayerID=tblUsers.user_id WHERE RoundID=:round_id ORDER BY tblScore.PlayerID, tblScore.HoleNum ASC");
$scoresSQL -> execute(array(":round_id"=>$roundID));
$scores = $scoresSQL -> fetchALL(PDO::FETCH_ASSOC);
Then I had some help from a guy at work to help me understand how to create my own arrays to help with the data.
<table class="table table-bordered m-b-0" id="roundupload">
<thead>
<tr>
<th colspan="1">Hole</th>
<?php
while ($HoleNum = $holeSQL->fetch(PDO::FETCH_ASSOC)){
echo'<th>'.$HoleNum['HoleNum'].'</th>'
;}
?>
</tr>
<tr>
<th colspan="1">Par</th>
<?php
while ($parInfo = $parSQL->fetch(PDO::FETCH_ASSOC)){
echo'<th><span class="label label-success">'.$parInfo['Par'].'</span></th>'
;}
?>
<th class="text-muted">Total</th>
</tr>
</thead>
<tbody>
<!-- Pull in Shooter Names For Upload Table -->
<?php
$players = array();
echo '<tr>';
foreach ($scores as $result){
if (!$players[$result['PlayerID']]['totalStrokes'] ) {
$players[$result['PlayerID']]['totalStrokes'] = null;
}
if (!$players[$result['PlayerID']]['totalPutts'] ) {
$players[$result['PlayerID']]['totalPutts'] = null;
}
$players[ $result[ 'PlayerID' ] ][ 'Name' ] = array('first' => $result[ 'user_first' ],'last' => $result[ 'user_last' ]);
$players[ $result[ 'PlayerID' ] ][ 'Hole' ][ ] = array('Hole' => $result[ 'HoleNum' ],'Strokes' => $result[ 'NumStrokes' ],'Putts' => $result[ 'NumPutts' ]);
$players[ $result[ 'PlayerID' ] ][ 'totalStrokes' ] += ( $result[ 'NumStrokes' ] );
$players[ $result[ 'PlayerID' ] ][ 'totalPutts' ] += ( $result[ 'NumPutts' ] );
}
foreach($players as $player){
echo '<th class="text-muted" rowspan="1">'.$player['Name']['first'].' '.$player['Name']['last'].'</th>';
echo '<td>'.$player['Hole']['0']['Strokes'].' | '.$player['Hole']['0']['Putts'].'</td>';
echo '<td>'.$player['Hole']['1']['Strokes'].' | '.$player['Hole']['1']['Putts'].'</td>';
echo '<td>'.$player['Hole']['2']['Strokes'].' | '.$player['Hole']['2']['Putts'].'</td>';
echo '<td>'.$player['Hole']['3']['Strokes'].' | '.$player['Hole']['3']['Putts'].'</td>';
echo '<td>'.$player['Hole']['4']['Strokes'].' | '.$player['Hole']['4']['Putts'].'</td>';
echo '<td>'.$player['Hole']['5']['Strokes'].' | '.$player['Hole']['5']['Putts'].'</td>';
echo '<td>'.$player['Hole']['6']['Strokes'].' | '.$player['Hole']['6']['Putts'].'</td>';
echo '<td>'.$player['Hole']['7']['Strokes'].' | '.$player['Hole']['7']['Putts'].'</td>';
echo '<td>'.$player['Hole']['8']['Strokes'].' | '.$player['Hole']['8']['Putts'].'</td>';
echo '<td>'.$player['totalStrokes'].' | '.$player['totalPutts'].'</td></tr>';
}
?>
</tbody>
</table>
Definitely not the best way to get the job done, but it did get the job done.