Solved

nested loop not working

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

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

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 108

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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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 108

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 108

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

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.

Question has a verified solution.

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

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
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.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

863 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

23 Experts available now in Live!

Get 1:1 Help Now