Solved

nested loop not working

Posted on 2010-09-20
16
357 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 108

Expert Comment

by:Ray Paseur
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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
 
LVL 4

Accepted Solution

by:
heppa earned 250 total points
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
"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
Comment Utility
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
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 4

Author Comment

by:cataleptic_state
Comment Utility
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
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 250 total points
Comment Utility
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
Comment Utility
I will have to.

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

Author Closing Comment

by:cataleptic_state
Comment Utility
Decided to hire someone.
0
 
LVL 4

Author Comment

by:cataleptic_state
Comment Utility
Still not working
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article discusses how to create an extensible mechanism for linked drop downs.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now