it would be amazing if someone could help me with this please, I really don't know how to solve it and I've been wracking my brains (and books and internet) for some time without really understanding what I need to do. I'm not a programmer by any means, purely self taught, so please accept my apologies if it seems easy. I've created a database for a fantasy league football site and having got the correct queries for the results and table, I just want to add a query that shows the weekly results by manager, and from this, which players have scored for those managers (no manager's team has the same player), an example such as:
(Table headers: Week > Manager > For > Against > Scorers) 2 Manager1 3-0 (Player a 1, player b 2) 2 Manager2 1-2 (player f 1) 2 Manager3 4-1 (player g 2, player x 2)
I'm using lots of joins to get my results, and have been experimenting with nested foreach loops thinking that I need to do a separate foreach loop to iterate through any player from each team that has scored (ie pull from the list of players who has goals greater than 0), but have had mixed results - none that I need!
Here is my code, from the index controller to the html displaying the foreach loops. If anyone can help or has any ideas, I really would be grateful.
if (isset($_GET['action']) and $_GET['action'] == 'scorers')
{
include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';
$sql = "SELECT Wk.week as ptsweeks, managers.managerid as ID, managers.manager as Manager, CASE when sum(Wk.goals)>GA.goals then '3' when sum(Wk.goals)=GA.goals then '1' when sum(Wk.goals)<GA.goals then '0' END as PTS, sum(Wk.goals) as F, GA.goals as A, case when Wk.goals>'0' then concat(Wk.name,' ',Wk.goals) end as goalscs
from weeks2012 as Wk
inner join goalsAgainst2012 as GA
inner join strikers2012 as ST
inner join defences2012 as DEF
inner join managers
on
Wk.week = GA.week and
DEF.managerid = ST.managerid and
ST.playerid = Wk.playerid and
DEF.defenceid = GA.defenceid and
managers.managerid = ST.managerid
where Wk.week = '$weekWK'
group by managers.managerid";
$result = mysqli_query($link, $sql);
if (!$result)
{
$error = 'Error updating submitted article.';
include $_SERVER['DOCUMENT_ROOT'] . '/includes/error.html.php';
exit();
}
while ($row = mysqli_fetch_array($result))
{
$weeks[] = array(
'table' => array('ptsweeks' => $row['ptsweeks'], 'ID' => $row['ID'], 'Manager' => $row ['Manager'], 'F' => $row['F'], 'A' => $row['A'], 'PTS' => $row['PTS']),
'gs' => array('goalscs' => $row['goalscs'])
);
}
include 'scorers_html.php';
}
** from scorers_html.php :
<table>
<?php foreach($weeks as $key => $gls): ?>
<tr>
<td> <?php echo $gls['table']['ptsweeks']; ?> </td>
<td> <?php echo $gls['table']['Manager']; ?> </td>
<td> <?php echo $gls['table']['F']; ?> </td>
<td> <?php echo $gls['table']['A']; ?> </td>
<td> <?php echo $gls['table']['PTS']; ?> </td>
</tr>
<?php foreach($weeks as $key => $gls): ?>
<tr>
<td> <?php echo $gls['gs']['goalscs']; ?> </td>
</tr>
<?php endforeach; ?>
<?php endforeach; ?>
</table>
