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

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

Hybrid View

  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
    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.

  8. #8
    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.

  9. #9
    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)); ?>

  10. #10
    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

  11. #11
    Join Date
    Sep 2010
    Posts
    38
    Hi,
    I do not know how to do that

    PHP Code:
    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 
    I know there is a if statement in there, but how do I check if the value.
    I am a newbie in the world of php and mysql.
    Last edited by catalepticstate; 10-05-2010 at 05:16 AM.

  12. #12
    Join Date
    Sep 2010
    Posts
    38
    so will

    $row['date'] = NULL
    if $row['date'] = $row['date'] {
    Output table
    }
    else if $row['date'] = $row['date'] {
    Output table
    }

    The problem with that is, its saying the same thing.

    So if the date for first block is 2010-01-01 the matches with that date are the same then output table

    but will I need a counter to move the date check up to the next one?

  13. #13
    Join Date
    Jan 2009
    Posts
    3,346
    So you would need to create a new variable. Then during your loop check if that variable is the same as your field value. I'm afraid I won't be much help without doing it for you until to grasp some of the basics. Try looking at some of the following tutorials to see if that helps. If you get rolling and get stuck again post back what you have coded and where you need help and I'm sure someone will be able to point you in the right direction.

    http://www.w3schools.com/php/default.asp
    http://php.net/manual/en/tutorial.php
    http://www.tizag.com/phpT/

  14. #14
    Join Date
    Sep 2010
    Posts
    38
    Could you do it for me? I am not very confidant in doing this or give me a basic code and I could develop on that

  15. #15
    Join Date
    Jan 2009
    Posts
    3,346
    I don't mind helping guide someone towards the answer or assisting on a conceptual level...however, this is my "day job" so I don't generally like to do it for free. Sorry.

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