www.webdeveloper.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 25

Thread: nested loop not working (need sort by date)

  1. #1
    Join Date
    Sep 2010
    Posts
    38

    nested loop not working (need sort by date)

    I am trying to create a loop that will show the matches for the dates in the database and sort the matches into blocks based on the date.

    I cannt seem to get this working, what am I doing wrong?

    PHP Code:
    mysql_select_db($database_db, $db);
    $query_match_fixturesD1 = "select m.match_id, date_format(m.date, '%W %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, v.venue_name, r.fname, r.sname  
    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) 
    LEFT JOIN referee r ON r.ref_id = m.referee_id LEFT JOIN venue v ON v.venue_id = m.venue_id
    where s1.team <> s2.team AND t1.division ='D1' AND t2.division = 'D1'
    group by match_id
    order by m.match_id LIMIT 5";
    $match_fixturesD1 = mysql_query($query_match_fixturesD1, $db) or die(mysql_error());
    $row_match_fixturesD1 = mysql_fetch_assoc($match_fixturesD1);
    $totalRows_match_fixturesD1 = mysql_num_rows($match_fixturesD1);

    mysql_select_db($database_db, $db);
    $query_match_dateD1 = "select matches.match_id, date_format(date, '%W %D %M %Y') as dDate 
    from matches LEFT JOIN matchscores ON matches.match_id = matchscores.match_id LEFT JOIN team ON matchscores.team = team.team_id
    where date = '$row_match_fixturesD1[date]' AND team.division = 'D1'
    order by matches.match_id";
    $match_dateD1 = mysql_query($query_match_dateD1, $db) or die(mysql_error());
    $row_match_dateD1 = mysql_fetch_assoc($match_dateD1);
    $totalRows_match_dateD1 = mysql_num_rows($match_dateD1);


    <?php do { ?>
            <div class="tableHeading">
            <h2><?php echo $row_match_fixturesD1['mDate']; ?></h2>
            </div>
            <table width="100%" border="0" cellspacing="0" cellpadding="0">
              <tr>
                <td><table width="100%" border="0" cellspacing="0" cellpadding="0">
                  <tr>
                    
                    <td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Type</div></td>
                    <td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Home</div></td>
                    <td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Score</div></td>
                    <td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Away</div></td>
                    <td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Kick-Off</div></td>
                    <td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Venue</div></td>
                    <td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Referee</div></td>
                    <td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Report</div></td>
                    
                  </tr>
                  <?php do { ?>
                    <tr bgcolor="#cccccc">
                      
                      <td class="td"><div align="center"><?php echo $row_match_fixturesD1['division']; ?></div></td>
                      <td class="td"><div align="center"><?php echo $row_match_fixturesD1['team1_name']; ?></div></td>
                      <td class="td"><div align="center"><?php echo $row_match_fixturesD1['score1']; ?> v <?php echo $row_match_fixturesD1['score2']; ?></div></td>
                      <td class="td"><div align="center"><?php echo $row_match_fixturesD1['team2_name']; ?></div></td>
                      <td class="td"><div align="center"><?php echo $row_match_fixturesD1['time']; ?></div></td>
                      <td class="td"><div align="center"><?php echo $row_match_fixturesD1['venue_name']; ?></div></td>
                      <td class="td"><div align="center"><?php echo $row_match_fixturesD1['fname']; ?> <?php echo $row_match_fixturesD1['sname']; ?></div></td>
                      <td class="td"><div align="center">
                      <?php if ($row_match_fixturesD1['match_id'] != "") {?>
                      <a href="match-report.php?id=<?php echo $row_match_fixturesD1['match_id']; ?>"><img src="images/copy.gif" alt="Match Report" border="0" title="Match Report" /></a>
                      <?php } else { ?> <?php };?>
                      </div></td>
                      
                    </tr>
                    <?php } while ($row_match_fixturesD1 mysql_fetch_assoc($match_fixturesD1)); ?>
                </table></td>
              </tr>
            </table>
            <p> </p>
    <?php } while ($row_match_dateD1 mysql_fetch_assoc($match_dateD1)); ?>

  2. #2
    Join Date
    Sep 2010
    Posts
    38
    any ideas on how I can get this working?

  3. #3
    Join Date
    Jan 2009
    Posts
    3,346
    Maybe have a look at a couple tutorials for mysql and php. Generally people use a format something like:
    PHP Code:
    <?php
    $query 
    "SELECT * FROM example"
         
    $result mysql_query($query) or die(mysql_error());


    while(
    $row mysql_fetch_array($result)){
        echo 
    $row['name']. " - "$row['age'];
        echo 
    "<br />";
    }
    ?>
    It looks like your query is a little over complicated...are you trying to pull a couple of fields from the same rows by using 2 queries/result sets?

  4. #4
    Join Date
    Sep 2010
    Posts
    38
    No just from 1 giant query, but I have two sets of output i need.
    I have tried from examples online but its not working, can you please take a look at the code for me.

  5. #5
    Join Date
    Jan 2009
    Posts
    3,346
    Your code is currently executing 2 queries which produce 2 different result sets. Both are being retrieved from the same table which is why I asked if your queries needed combining so you are only working with a single result set. What exactly are you trying to retrieve from the database and what does your schema look like?

  6. #6
    Join Date
    Sep 2010
    Posts
    38
    I need to display a set of tables with the Date of the matches
    Then under that a table with all the matches for that date.

    The there can only be matches played on that date

    I need a text and previous buttons to move to the next date if thats possible if not then all the matches for the dates in the database must be on one page.

    I have been tried for days and weeks now to get this done.

    I have changed the site today but the GREEN heading should show the date and the matches should be under it/
    This is the site currently: the page

  7. #7
    Join Date
    Jan 2009
    Posts
    3,346
    It sounds like a "group by" might be in order. What is your db table structure?

  8. #8
    Join Date
    Sep 2010
    Posts
    38
    CREATE TABLE IF NOT EXISTS `matches` (
    `match_id` int(8) NOT NULL auto_increment,
    `date` date default NULL,
    `time` varchar(5) default NULL,
    `report` longtext,
    `referee_id` int(8) NOT NULL,
    `season_id` int(8) NOT NULL,
    `venue_id` int(8) NOT NULL,
    PRIMARY KEY (`match_id`),
    KEY `referee_id` (`referee_id`),
    KEY `venue_id` (`venue_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3812 ;

    CREATE TABLE IF NOT EXISTS `matchscores` (
    `matchscores_id` int(8) NOT NULL auto_increment,
    `match_id` int(8) NOT NULL,
    `team` int(8) NOT NULL,
    `score` int(8) default NULL,
    PRIMARY KEY (`matchscores_id`),
    KEY `match_id` (`match_id`,`team`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7623 ;

    CREATE TABLE IF NOT EXISTS `match_player` (
    `match_player` int(8) NOT NULL auto_increment,
    `player_id` int(8) NOT NULL,
    `Goals` int(8) default '0',
    `YC` int(8) default '0',
    `RC` int(8) default '0',
    `MOM` mediumint(8) default '0',
    `match_id` int(8) NOT NULL,
    PRIMARY KEY (`match_player`),
    KEY `player_id` (`player_id`,`match_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=547 ;


    -- Table structure for table `player`
    --

    CREATE TABLE IF NOT EXISTS `player` (
    `player_id` int(8) NOT NULL auto_increment,
    `fname` varchar(100) NOT NULL,
    `sname` varchar(100) NOT NULL,
    `gender` varchar(10) default 'Male',
    `nationality` varchar(50) default NULL,
    `email` varchar(255) default NULL,
    `dob` date default NULL,
    `place_birth` varchar(255) default NULL,
    `height` varchar(20) default NULL,
    `weight` varchar(10) default NULL,
    `photo` varchar(255) default NULL,
    `comments` text,
    `position` varchar(255) default NULL,
    `team_id` int(8) NOT NULL,
    PRIMARY KEY (`player_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=578 ;

    CREATE TABLE IF NOT EXISTS `team` (
    `team_id` int(8) NOT NULL auto_increment,
    `team_name` varchar(255) NOT NULL,
    `team_address` text,
    `team_phone` varchar(25) default NULL,
    `team_email` varchar(255) default NULL,
    `team_web` varchar(255) default NULL,
    `colours_home` varchar(50) default NULL,
    `colours_away` varchar(50) default NULL,
    `manager` varchar(255) default NULL,
    `assistant` varchar(255) default NULL,
    `club_rep` varchar(255) default NULL,
    `division` varchar(255) default NULL,
    `team_pic` varchar(255) default NULL,
    `team_logo` varchar(255) default NULL,
    `profile` mediumtext,
    `honours` mediumtext,
    `password` varchar(10) NOT NULL,
    PRIMARY KEY (`team_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=25 ;

  9. #9
    Join Date
    Jan 2009
    Posts
    3,346
    So you are wanting something like:

    10/02/10:
    Match 1 ... match1 details
    Match 2 ... match2 details
    etc

    10/03/10:
    Match 1 ... match1 details
    Match 2 ... match2 details
    etc


    ?

  10. #10
    Join Date
    Sep 2010
    Posts
    38
    Yes thats it! :-)

  11. #11
    Join Date
    Jan 2009
    Posts
    3,346
    Then you would build your query with "SELECT fields FROM tables & joins WHERE any clause GROUP BY date field ORDER BY date field". Does that make sense?

  12. #12
    Join Date
    Sep 2010
    Posts
    38
    I have that, but thats not the problem.

    When I do a while loop it does not create a loop within a loop. I am using dreamweaver.

    It does not allow me to create the table for next date and set of matches.

  13. #13
    Join Date
    Jan 2009
    Posts
    3,346
    It doesn't matter what editor you are using.

    Basically you need to check with each loop whether the date field is the same as the previous loop. If not, you start a new table. There is no need for multiple loops or result sets like you have in the code you've posted.

  14. #14
    Join Date
    Sep 2010
    Posts
    38
    So is this bit fine:

    PHP Code:
    <?php do { ?>
            <div class="tableHeading">
            <h2><?php echo $row_match_fixturesD1['mDate']; ?></h2>
            </div>
            <table width="100%" border="0" cellspacing="0" cellpadding="0">
              <tr>
                <td><table width="100%" border="0" cellspacing="0" cellpadding="0">
                  <tr>
                    
                    <td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Type</div></td>
                    <td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Home</div></td>
                    <td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Score</div></td>
                    <td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Away</div></td>
                    <td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Kick-Off</div></td>
                    <td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Venue</div></td>
                    <td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Referee</div></td>
                    <td height="25" bgcolor="#000000" class="tableTextHeading"><div align="center">Report</div></td>
                    
                  </tr>
                  <?php do { ?>
                    <tr bgcolor="#cccccc">
                      
                      <td class="td"><div align="center"><?php echo $row_match_fixturesD1['division']; ?></div></td>
                      <td class="td"><div align="center"><?php echo $row_match_fixturesD1['team1_name']; ?></div></td>
                      <td class="td"><div align="center"><?php echo $row_match_fixturesD1['score1']; ?> v <?php echo $row_match_fixturesD1['score2']; ?></div></td>
                      <td class="td"><div align="center"><?php echo $row_match_fixturesD1['team2_name']; ?></div></td>
                      <td class="td"><div align="center"><?php echo $row_match_fixturesD1['time']; ?></div></td>
                      <td class="td"><div align="center"><?php echo $row_match_fixturesD1['venue_name']; ?></div></td>
                      <td class="td"><div align="center"><?php echo $row_match_fixturesD1['fname']; ?> <?php echo $row_match_fixturesD1['sname']; ?></div></td>
                      <td class="td"><div align="center">
                      <?php if ($row_match_fixturesD1['match_id'] != "") {?>
                      <a href="match-report.php?id=<?php echo $row_match_fixturesD1['match_id']; ?>"><img src="images/copy.gif" alt="Match Report" border="0" title="Match Report" /></a>
                      <?php } else { ?> <?php };?>
                      </div></td>
                      
                    </tr>
                    <?php } while ($row_match_fixturesD1 mysql_fetch_assoc($match_fixturesD1)); ?>
                </table></td>
              </tr>
            </table>
            <p> </p>
    <?php } while ($row_match_dateD1 mysql_fetch_assoc($match_dateD1)); ?>

  15. #15
    Join Date
    Jan 2009
    Posts
    3,346
    You are still executing 2 separate queries for no reason and have a second loop which is not needed...I don't know to explain any clearer without coding it for you.

    Psuedocode:
    Query with 1 result here (using order by or group by as needed)
    set your check date variable as null
    loop here
    is check date the same?
    yes, continue existing table
    no, start a new table
    set check date variable to current field value
    end loop

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