Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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>
share|improve this question

1 Answer 1

First off, you have a syntax error that can be viewed by looking at Stackoverflow's syntax highlighting. Your third line needs to be

include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';

Upon further review, the below line is also incorrect:

include $_SERVER['DOCUMENT_ROOT'] . /includes/error.html.php';

It should be

include $_SERVER['DOCUMENT_ROOT'] . '/includes/error.html.php';

You need to encapsulate all strings within quotes or apostrophes.

I would recommend using a code editor that includes syntax highlighting for your language such as KomodoEdit or Netbeans; you will save a lot of headaches.

If this does not help, please post the specific errors/issues you are encountering.

share|improve this answer
    
Many thanks for looking at it Dave. The main issue I'm having is getting the result of the query to display each scorer for each manager. At the moment, this code gives the same list of names for each manager, rather than only those players who have scored and who belong only to the individual managers. So I don't know if it's my select query that is incorrect (this bit: concat(weeks2012.name,' ',weeks2012.goals) as Scorers), if it's the end of the query (I've tried GROUP BY and ORDER BY), or if it's the foreach loop in the html file - or a combination of all three. Does that make any sense? –  AK31 Mar 11 '13 at 2:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.