Solved

nested loop not working

Posted on 2010-09-20
16
365 Views
Last Modified: 2012-05-10
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?
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)); ?>

Open in new window

0
Comment
Question by:cataleptic_state
  • 7
  • 6
  • 3
16 Comments
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 33720891
Wow, you may have a mess on your hands here.  I almost do not know where to start to try to help you.  You have a query that may get up to 5 rows ("LIMIT 5" on line 7) but you only retrieve the first row (line 9).  Have you considered hiring a developer to help with this part of the project?  It might be faster than trying to work it out with a Q-n-A dialog over EE.  

Sorting information in the data base is done with ORDER BY

Matching information in the data base is done with WHERE

Array indexes need to be placed in quotes, like this:
$row_match_fixturesD1"[date"]

"date" is a reserved word in MySQL.

A developer would be able to help you get past these and many other issues -- very quickly!
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 33720899
Apologies, ...

Array indexes need to be placed in quotes, like this:
$row_match_fixturesD1["date"]

If you don't quote the keys, you are at risk that PHP will use a defined constant instead of the index key.
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33723132
Is there not an easy way to sort the table by date for the matches in asending order?

e.g

2nd october
match 1
match 2
match 3
match 4
match 5

4th october
match 1
match 2
match 3
match 4
match 5

and so on
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 4

Accepted Solution

by:
heppa earned 250 total points
ID: 33724343
1st idea (easy approach, but watch out for performance issues if you have a lot of different dates):

build several queries. first select all different dates and order it ascending
<?php
$result = mysql_query("select distinct date_format( MATCHDATE, '%Y-%m-%d') matchdatefrom matchesorder BY MATCHDATE asc");
?>

put these dates in an array and with each single result select all the matches that have the same match date.

<?phpwhile($actDateRow = $db->mysql_fetch_object($result)){
echo $actDateRow->matchdate;
// now fetch every match with the date
// change the * to everything you want to select from matches and also add joins.
$innerResult = mysql_query("select * from matches where date_format(MATCHDATE,'%Y-%m-%d')=".$actDateRow->matchdate.");}
// now in your result you have all matches with the same date $actDateRow->matchdate
while($actMatch = mysql_fetch_object($innerResult)){
print_r($actMatch);
}
}?>

I didn't check the code for errors, see it as pseudo code that should lead lead directions, but I am very sure, it is not much to do to make it work.

And one hint just to make sure, you will think about it one day: Try a template engine like smarty or something. Correctly used, it cleans up your code AND simplifies work ;)

Good luck!
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 33724516
"sort the table by date for the matches in asending order?"

Yes, you would use ORDER BY and maybe GROUP BY.  For this to work correctly you need to be using the ISO8601 standard for date/time representation.
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33724982
The problem is I do know what which dates the matches will be played, most likely only saturday and friday sunday of every week

I have tried to use smarty, I get confused with it.

I will play around with your suggestions and let you know how I get on.

Thank you
0
 
LVL 4

Expert Comment

by:heppa
ID: 33725210
smarty is easy, if you know how php works and read the manual! ;)

you should maybe read something about MVC ("Model View Control" Design Pattern), which will help a lot.
If you need further directions, please ask...

Regards.Alex
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 33725302
Consider investing a few dollars and a month of your time in this introductory book.  It will not make you a pro, but it will give you a head-start.
http://www.sitepoint.com/books/phpmysql4/

Best of luck with it, ~Ray
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33742226
I think this will be my last ever job I take on with php/mysql go back to design
0
 
LVL 4

Expert Comment

by:heppa
ID: 33742248
no ma', giving up is not the solution! ;)

keep trying... good thing is: when you do design afterwards, you will think about the poor developer that will have to implement your ideas!

honestly: did you think: "it can't be a big problem"? it will take time to get the first results, and it will take even more time to be able to transfer those results to your own problems...

what's the problem now?
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 33742946
I understand your frustration.  You're working in a highly technical area with lots of trip-wires and gotchas.  Programming is performance art, and a depth of experience is the only thing that can get you a firm foundation.  I have eleven shelf-feet of books on PHP and MySQL and I have read every one of them.  If you're not prepared to make that kind of commitment to the craft, I understand, believe me.  It is time-consuming, concentration-filled work.

Read this.  It will make you feel better.  No reason to be frustrated -- just understand our human nature and our limitations.
http://norvig.com/21-days.html
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33743876
Hi,
I have got a book on PHP and Mysql with dreamweaver I bought back when I was at uni.

But it does not show me how I can create many forms on one page, that update half the information into a table and insert into another table.

I have been googling till im going crosseyed.

I think this will seriously be my last programming intensive website
I should just stay on the arty farty side of things.

Maybe I can hire someone to do the coding?
0
 
LVL 109

Assisted Solution

by:Ray Paseur
Ray Paseur earned 250 total points
ID: 33747002
You SHOULD hire someone to do the coding.  It's a professional specialty that requires knowledge and skills.  It is almost always cheaper and wiser to hire a pro rather than try to do it yourself.  Look online for freelance programmers.  You will find lots.  Choose one you trust and who is geographically close to you so you can meet in person.  Best of luck with it, ~Ray
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33776849
I will have to.

Thank you. How shall I award these points?
0
 
LVL 4

Author Closing Comment

by:cataleptic_state
ID: 33806327
Decided to hire someone.
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33886643
Still not working
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question