Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

nested loop not working

Posted on 2010-09-20
16
Medium Priority
?
374 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 111

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 111

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 4

Accepted Solution

by:
heppa earned 1000 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 111

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 111

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 111

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 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 1000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
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…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

604 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