while is only showing one record

[bravo14]

Hi guys

 

I am using the code below to show distinct months using one query then for each month I am trying to show a list of fixtures for the month, the problem I have is only one month is showing.

 

where am i going wrong?

 

 

<?php
//find distinct months
$sql="SELECT DISTINCT MONTH( match_date ) AS
MONTH FROM tbl_fixtures
WHERE season_id =$current_season
UNION SELECT DISTINCT MONTH( match_date ) AS
MONTH FROM tbl_gp
WHERE season_id =$current_season";
$result=mysqli_query($dbConn, $sql);
if(mysqli_num_rows($result)>0){
    while($row=mysqli_fetch_assoc($result)){
        $month=$row['MONTH'];
        echo '<div class="fixtureMonth">
              <h2>'.$months[$month].'</h2>
              </div>
              <div class="fixtures">';
              //find fixtures for month
        $sql="SELECT Opposition, match_date, competition, tbl_clubs.club_name AS club, image, HomeAway, team, opposition_points, points
        FROM tbl_fixtures
        INNER JOIN tbl_clubs ON tbl_fixtures.club = tbl_clubs.club_id
        WHERE season_id =$current_season and MONTH(match_date)=$month
        UNION SELECT Opposition, match_date, \"SGP\" AS competition, \"SGP\" AS club, \"sgp.png\" AS image,  \"\" as HomeAway, \"\" as team, \"\" as opposition_points, points
        FROM tbl_gp
        WHERE season_id =$current_season and MONTH(match_date)=$month
        ORDER BY match_date";
        $result=mysqli_query($dbConn,$sql)or die(mysqli_error($dbConn));
        if(mysqli_num_rows($result)==0){
            echo no_fixtures_found;
        }
        else{
            echo '<table class="fixturesTable">';
            while($row=mysqli_fetch_assoc($result)){
                echo'<tr>
    <td>'.date("d-M-Y",strtotime($row['match_date'])).'</td>
          <td><img src="'.$shopConfig['url'].'images/'.$row['image'].'"/></td>
          <td>'.$row['Opposition'].'</td>
          <td>'.$row['HomeAway'].'</td>
          <td><b>'.$row['team'].'</b>-'.$row['opposition_points'].'</td>
          <td>'.$row['team'].'</td>
          </tr>';
            }
            echo '</table>';
        }
        echo '</div>';
    }
    
}
?>

[Psycho]

Do NOT run queries in loops. You can run ONE query to get all the data you need.

 

Anyway, your problem is that you are reusing variables $results & $row in processing the queries and they are being overwritten.

if(mysqli_num_rows($result)>0)
{
    while($row=mysqli_fetch_assoc($result))
    {
            $result=mysqli_query($dbConn,$sql)or die(mysqli_error($dbConn));
 
            echo '<table class="fixturesTable">';
            while($row=mysqli_fetch_assoc($result))
            {
 
            }
}

Edited December 3, 2014 by Psycho

[Psycho]

This should do what you need in a much more efficient manner

 

 

$sql = "SELECT Opposition, DATE_FORMAT('%d-%b-%Y', match_date) as match_date,
               MONTH(match_date) as month, competition, tbl_clubs.club_name AS club,
               image, HomeAway, team, opposition_points, points
        FROM tbl_fixtures
        INNER JOIN tbl_clubs ON tbl_fixtures.club = tbl_clubs.club_id
        WHERE season_id = $current_season and MONTH(match_date) = $month
        UNION
        SELECT Opposition, DATE_FORMAT('%d-%b-%Y', match_date) as match_date,
               MONTH(match_date) as month, 'SGP' AS competition, 'SGP' AS club,
               'sgp.png' AS image, '' as HomeAway, '' as team, '' as  opposition_points, points
        FROM tbl_gp
        WHERE season_id = $current_season and MONTH(match_date) = $month
        ORDER BY match_date";
$result = mysqli_query($dbConn,$sql)or die(mysqli_error($dbConn));
 
if(mysqli_num_rows($result))
{
    $currentMonth = false;
    while($row = mysqli_fetch_assoc($result))
    {
        //Check if month has changed
        if($currentMonth != $row['month'])
        {
            //Close previous fixture table if not first record
            if($currentMonth)
            {
                echo "    </table>\n";
                echo "</div>\n";
            }
            //Open new fixture table
            echo "<div class='fixtureMonth'>\n";
            echo "    <h2>{$months[$row['month']]}</h2>\n";
            echo "</div>\n";
            echo "<div class='fixtures'>\n";
            echo "    <table class='fixturesTable'>";
            //Set current month to new value
            $currentMonth = $row['month'];
        }
        //Display fixture record
        echo "<tr>\n";
        echo "<td>{date("d-M-Y",strtotime($row['match_date'])}</td>\n";
        echo "<td><img src='{$shopConfig['url']}images/{$row['image']}'></td>\n";
        echo "<td>{$row['Opposition']}</td>\n";
        echo "<td>{$row['HomeAway']}</td>\n";
        echo "<td><b>{$row['team']}</b>-{$row['opposition_points']}</td>\n";
        echo "<td>{$row['team']}</td>\n";
        echo "</tr>\n";
    }
    //Close last fixture table
    echo "    </table>\n";
    echo "</div>\n";
}