Solved

nested loop not working

Posted on 2010-09-20
16
369 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 3
16 Comments
 
LVL 110

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 110

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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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 110

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 110

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 110

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 110

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

Webinar: Choosing a MySQL HA Solution

Join Percona’s Principal Technical Services Engineer, Marcos Albe as he presents Choosing a MySQL High Availability Solution on Thursday, June 29, 2017 at 10:00 am PDT / 2:00 pm EDT (UTC-7).

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

724 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