www.webdeveloper.com
Results 1 to 5 of 5

Thread: two records from the same table in for loop

Hybrid View

  1. #1
    Join Date
    Sep 2010
    Posts
    38

    two records from the same table in for loop

    I have a table that holds teams, a table that holds scores and a table that holds match data.

    I am finding it dificult to get the player data for the second team.

    Currently It is showing player information for one team.

    It does echo out the second team name, but not their player info.

    Can someone have a look at my code and make it work.
    I took off the code I had for the second team becuase it was ging me Error 1065

    PHP Code:
    [B]Main Query:[/B]
    mysql_select_db($database_db, $db);
    $query_match_fixtures = "select m.match_id, date_format(m.date, '%d/%m/%Y') as mDate, m.time, t1.division, m.report, t1.team_name as team1_name, s1.score as score1, t2.team_name as team2_name, s2.score as score2
    from matches m left join (matchscores s1 left join team t1 on t1.team_id = s1.team) on (s1.match_id = m.match_id) left join (matchscores s2 left join team t2 on t2.team_id = s2.team) on (s2.match_id = m.match_id)
    where s1.team <> s2.team AND m.match_id = $matchID
    group by match_id
    order by m.match_id";
    $match_fixtures = mysql_query($query_match_fixtures, $db) or die(mysql_error());
    //$row_match_fixtures = mysql_fetch_assoc($match_fixtures);
    $totalRows_match_fixtures = mysql_num_rows($match_fixtures);
    [B]
    Player extraction:[/B]
    mysql_select_db($database_db, $db);

    $row_match_player = array();

    for ($i = 0; $i < $totalRows_history; $i++)
    {
        $row_history[$i] = mysql_fetch_assoc($history);
    }

    for ($i = 0; $i < $totalRows_match_fixtures; $i++)
    {
        $row_match_fixtures[$i] = mysql_fetch_assoc($match_fixtures);

        $match_player_query = "SELECT *
                               FROM match_player
                                LEFT JOIN player on player.player_id = match_player.player_id
                                LEFT JOIN team ON player.team_id = team.team_id
                               WHERE match_id = ".$matchID."
                                AND team.team_name = '".$row_match_fixtures[$i]['team1_name']."'";


        $result_match_player = mysql_query($match_player_query, $db) or die("large error ".mysql_errno());

        $totalRows_match_player = mysql_num_rows($result_match_player);


        for ($r; $r < $totalRows_match_player; $r++)
        {
            $temp_row_match_player[$r] = mysql_fetch_assoc($result_match_player);
        }
        array_push($row_match_player, $temp_row_match_player);
    }

    [B]The display table:[/B]
            <div class="tableHeading">
            <h2><?php echo $row_history['mDate']; ?></h2>
            </div>
            <table width="100%" border="0" cellspacing="0" cellpadding="0">
      <tr>
        <td height="25" bgcolor="#000000"><div align="left" style="color:#FFFFFF"><strong>Type</strong></div></td>
        <td height="25" bgcolor="#000000"><div align="left" style="color:#FFFFFF"><strong>Home</strong></div></td>
        <td height="25" bgcolor="#000000"><div align="center" style="color:#FFFFFF"><strong>Score</strong></div></td>
        <td height="25" bgcolor="#000000"><div align="center" style="color:#FFFFFF"><strong>Away</strong></div></td>
        <td height="25" bgcolor="#000000"><div align="center" style="color:#FFFFFF"><strong>Kick-Off</strong></div></td>
        <td height="25" bgcolor="#000000"><div align="center" style="color:#FFFFFF"><strong>Venue</strong></div></td>
        <td height="25" bgcolor="#000000"><div align="center" style="color:#FFFFFF"><strong>Referee</strong></div></td>
      </tr>
      <tr>
        <?php foreach ($row_match_fixtures as $show_match_fixtures)
        { 
    ?>
            <td height="25" bgcolor="#dfdfdf">
            <div align="left"><?php echo $show_match_fixtures['division']; ?></div>
            </td>
            <td height="25" bgcolor="#dfdfdf">
            <div align="left"><?php echo $show_match_fixtures['team1_name']; ?></div>
            </td>
            <td height="25" bgcolor="#dfdfdf">
            <div align="center"><?php echo $show_match_fixtures['score1']; ?> - <?php echo $show_match_fixtures['score2']; ?></div>
            </td>
            <td height="25" bgcolor="#dfdfdf">
            <div align="center"><?php echo $show_match_fixtures['team2_name']; ?></div>
            </td>
            <td height="25" bgcolor="#dfdfdf">

            <!-- You do not want a nested loop inside the header so these results will have to go inside the table-->

            <div align="center"><?php echo $row_history['time']; ?></div>
            </td>
            <td height="25" bgcolor="#dfdfdf">
            <div align="center"><?php echo $row_history['venue_name']; ?></div>
            </td>
            <td height="25" bgcolor="#dfdfdf">
            <div align="center"><?php echo $row_history['fname']; ?> <?php echo $row_history['sname']; ?></div>
            </td>
        </tr>
          <tr>
            
            
            <?php foreach ($row_match_player as $player_array)
            {
                foreach (
    $player_array as $player_display)
                {
            
    ?>
            <td valign="top">Goalscorers</td>    
            <td>
        
      
            <?php echo $player_display['fname']." ".$player_display['sname']." (".$player_display['Goals']; ?>)<br />
            

            </td>
            <td>&nbsp;</td>

            <td>
        
      
            <?php echo $player_display['fname']." ".$player_display['sname']." (".$player_display['Goals']; ?>)<br />
            

            </td>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
          </tr>
          <tr>
            <td valign="top">Yellow Cards</td>
            <td><?php echo $player_display['YC']; ?></td>
            <td>&nbsp;</td>
            <td><?php echo $player_display['YC']; ?></td>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
          </tr>
          <tr>
            <td valign="top">Red Cards</td>
            <td><?php echo $player_display['RC']; ?></td>
            <td>&nbsp;</td>
            <td><?php echo $player_display['RC']; ?></td>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
          </tr>
          <tr>
            <td valign="top">Man of the Match</td>
            <td><?php echo $player_display['fname']; ?> <?php echo $player_display['sname']; ?></td>
            <td>&nbsp;</td>
            <td><?php echo $player_display['fname']; ?> <?php echo $player_display['sname']; ?></td>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
          </tr>
                <?php
                    
    }// close and unset $player_display
                    
    unset($player_display);
                } 
    //close and unset $player_array
            
    unset($player_array);
         } 
    //close and unset $show_match_fixtures
        
    unset($show_match_fixtures);?>
    </table>
    http://forums.adobe.com/servlet/Jive...Untitled-1.jpg

    As you can see from the picture above the table is displayed with the name of the "goal scorer" for that team under (home) or Home team

    When I try to create the same thing for away team I get a error, either a syntax error or 1065.

    I tried having a foreach do two queries but that did not work.

    Any help would be appriecated

  2. #2
    Join Date
    Sep 2010
    Posts
    38
    Someone suggested the for loop was wrong

  3. #3
    Join Date
    Feb 2005
    Posts
    42
    Why not just join the match_player table in the first query to return all the players for the match instead of doing it in a loop?

  4. #4
    Join Date
    Sep 2010
    Posts
    38
    I will give that a try

  5. #5
    Join Date
    Sep 2010
    Posts
    38
    I've messed it up nothing works :'(

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles