TalkPHP - View Single Post

Dracula · 06-04-2008, 12:46 PM

Hi guys!
I’m currently working on a grade tracker and I’ve reached a dead end where I need some help.
Basically, I’ve got a mysql query that returns the right results I need from the database and so far, so good. The problem starts when I try to output the results of the query through a while loop. To be more precise, the query returns the data in the following form:

HTML Code:

Assignment_ID | Assignment Title | Criteria covered | Date submitted | Status
 
1               Staff research              P1          2008-03-12      Pass
1               Staff research              P2          2008-03-12      Pass
1               Staff research              P3          2008-03-12      Pass
1               Staff research              M1          2008-03-12      Pass
1               Staff research              M2          2008-03-12      Pass
1               Staff research              M3          2008-03-12      Pass
1               Staff research              D1          2008-03-12      Pass
1               Staff research              D2          2008-03-12      Pass
1               Staff research              D3          2008-03-12      Pass
2              Games techniques             P4          2007-12-03      Merit
2              Games techniques             P5          2007-12-03      Merit
2              Games techniques             P6          2007-12-03      Merit
2              Games techniques             M4          2007-12-03      Merit
2              Games techniques             M5          2007-12-03      Merit
2              Games techniques             M6          2007-12-03      Merit
2              Games techniques             D4          2007-12-03      Merit
2              Games techniques             D5          2007-12-03      Merit

Well, what I want in the html output is to hide the redundant data and display horizontally just the bits that changes from one iteration of the loop to the next, like this:

HTML Code:

Assignment Title |            Criteria covered           | Date submitted | Status
 
Staff research       P1, P2, P3, M1, M2, M3, D1, D2, D3      2008-03-12      Pass
Games techniques    P4, P5, P6, M4, M5, M6, D4, D5, D6       2007-12-03      Merit

So far I managed to display them vertically and hide away just the repeating Assignment Title by setting a counter as a random variable which I’ve called $lastassigID just before the loop starts and set it to NULL. Later in the while loop I’ve added an IF ELSE statement that check to see if $lastassigID=$assigID from the query. If it does, it echoes an empty table cell, if it doesn't, it echoes the assignment title. Then at the end of the loop I set $lastassigID to be equal to $assigID. Here is the code...

PHP Code:


			
<?php
if (mysql_num_rows($result)==0){
echo "<p> &nbsp</p><span class='heading'>No assignments have been issued for this module!</span>";
}
else{
?>
<table width="100%" border="0" cellspacing="0" cellpadding="5">
<tr valign="top" class="heading"> 
<td width="25%">Assignment Title</td>
<td width="40%">Criteria</td>
<td width="15%">Date Sub </td>
<td width="20%">Status</td>
</tr>
<?php
 
 
$row_count = 1;//the counter for colouring the rows
 
$lastassigID = ''; //the counter for getting the assignment titles
 
mysql_data_seek($result,0) ;
while ($row = mysql_fetch_array($result,MYSQL_ASSOC)){ 
$assigID=$row['Assignment_ID'];
$title=$row['Title'];
$datedue=$row['Date_due'];
$datesubmit=$row['Date_submit'];
$grade=$row['Grade_awarded']; 
$criteria=$row['Grade']; 
$achieved=$row['Achieved']; 
 
if($assigID<>$lastassigID){ 
 
$row_count++;
 
if ($row_count % 2 == 0){
//EVEN
$row_color="#FFFFFF";
}
else{
//ODD
$row_color="#EDEDED";
}
echo '<tr bgcolor="' . $row_color . '">'; 
echo '
<tr bgcolor="' . $row_color . '">
<td valign="top"><a href="module.php?id='.$assigID.'">'.$title.'</a></td>';
$lastassigID=$assigID; //resetting the counter 
} 
else { 
echo '<tr bgcolor="' . $row_color . '">'; 
echo '<td valign="top"></td>'; 
} 
 
echo' <td>'.$criteria.'</td>';// this is the one that outputs my P1, P2, P3, etc…
 
 
 
 
 
$date_due = strtotime($datedue);
$date_submit = strtotime($datesubmit);
$today = strtotime($todays_date);
 
//here is the output for the Date – different colours and messages depending on the time of submission
if (isset($datesubmit) && $date_submit>$date_due){
echo '<TD valign="top"><span class="late">'.$datesubmit.'</span></TD>';
}
elseif (!isset($datesubmit) && $date_due>$today){
echo '<TD valign="top"><span class="unsubmit">Not submitted</span></TD>';
}
elseif (!isset($datesubmit) && $date_due<$today){
echo '<TD valign="top"><span class="overdue">Not submitted</span></TD>';
}
else{
echo '<TD valign="top"><span class="">'.$datesubmit.'</span></TD>';
}
 
 
//here is the output for the STATUS, again different colours and messages
if ($grade=="" && $date_due>$date_submit && isset($datesubmit)){
echo '<TD valign="top"><span class="unmarked">Awaiting marking</span></TD></TR>';
}
elseif ($datesubmit=="" && $date_due<$today){
echo '<TD valign="top"><span class="fail">Overdue</span></TD></TR>';
}
elseif ($grade=="" && $date_due>$date_submit){
echo '<TD valign="top"><span class="active">Active</span></TD></TR>';
}
elseif ($grade==""){
echo '<TD valign="top"><span class="unmarked">Awaiting marking</span></TD></TR>';
}
else{
switch($grade){
case "Pass":
echo '<TD valign="top"><span class="pass">Pass</span></TD></TR>';
break;
case "Merit":
echo '<TD valign="top"><span class="pass">Merit</span></TD></TR>';
break;
case "Distinction":
echo '<TD valign="top"><span class="pass">Distinction</span></TD></TR>';
break;
case "Not yet Achieved":
echo '<TD valign="top"><span class="fail">Not yet Achieved</span></TD></TR>';
break;
}
} 
}
}
?>
</div>
</body>
</html>

… and here’s the output looking like this:

HTML Code:

Assignment Title | Criteria covered | Date submitted | Status
 
Staff research           P1             2008-03-12      Pass
                         P2             2008-03-12      Pass
                         P3             2008-03-12      Pass
                         M1             2008-03-12      Pass
                         M2             2008-03-12      Pass
                         M3             2008-03-12      Pass
                         D1             2008-03-12      Pass
                         D2             2008-03-12      Pass
                         D3             2008-03-12      Pass
Games Techniques         P4             2007-12-03      Merit
                         P5             2007-12-03      Merit
                         P6             2007-12-03      Merit
                         M4             2007-12-03      Merit
                         M5             2007-12-03      Merit
                         M6             2007-12-03      Merit
                         D4             2007-12-03      Merit
                         D5             2007-12-03      Merit
                         D5             2007-12-03      Merit

Here is where I got stuck: how can I display them horizontally like this?

HTML Code:

Assignment Title |            Criteria covered           | Date submitted | Status
 
Staff research       P1, P2, P3, M1, M2, M3, D1, D2, D3      2008-03-12      Pass
Games techniques    P4, P5, P6, M4, M5, M6, D4, D5, D6       2007-12-03      Merit

I was thinking about a nested loop but then how will the inner loop know when to stop and let the outer loop carry on? I was thinking of setting a similar counter like the one for the title, so that as long as the assignment_ID is the same, the inner loop can carry on, but then how can I output the results horizontally on a single row, then the next row displaying the results of a second iteration of both loops? I’d really appreciate any help. Thanks ever so much.

06-04-2008, 12:46 PM	#1 (permalink)
Dracula The Visitor Join Date: Jun 2008 Posts: 2 Thanks: 1	Horizontal nested loop problem Hi guys! I’m currently working on a grade tracker and I’ve reached a dead end where I need some help. Basically, I’ve got a mysql query that returns the right results I need from the database and so far, so good. The problem starts when I try to output the results of the query through a while loop. To be more precise, the query returns the data in the following form: HTML Code: Assignment_ID \| Assignment Title \| Criteria covered \| Date submitted \| Status 1 Staff research P1 2008-03-12 Pass 1 Staff research P2 2008-03-12 Pass 1 Staff research P3 2008-03-12 Pass 1 Staff research M1 2008-03-12 Pass 1 Staff research M2 2008-03-12 Pass 1 Staff research M3 2008-03-12 Pass 1 Staff research D1 2008-03-12 Pass 1 Staff research D2 2008-03-12 Pass 1 Staff research D3 2008-03-12 Pass 2 Games techniques P4 2007-12-03 Merit 2 Games techniques P5 2007-12-03 Merit 2 Games techniques P6 2007-12-03 Merit 2 Games techniques M4 2007-12-03 Merit 2 Games techniques M5 2007-12-03 Merit 2 Games techniques M6 2007-12-03 Merit 2 Games techniques D4 2007-12-03 Merit 2 Games techniques D5 2007-12-03 Merit Well, what I want in the html output is to hide the redundant data and display horizontally just the bits that changes from one iteration of the loop to the next, like this: HTML Code: Assignment Title \| Criteria covered \| Date submitted \| Status Staff research P1, P2, P3, M1, M2, M3, D1, D2, D3 2008-03-12 Pass Games techniques P4, P5, P6, M4, M5, M6, D4, D5, D6 2007-12-03 Merit So far I managed to display them vertically and hide away just the repeating Assignment Title by setting a counter as a random variable which I’ve called `$lastassigID` just before the loop starts and set it to NULL. Later in the while loop I’ve added an IF ELSE statement that check to see if `$lastassigID=$assigID` from the query. If it does, it echoes an empty table cell, if it doesn't, it echoes the assignment title. Then at the end of the loop I set `$lastassigID` to be equal to `$assigID`. Here is the code... PHP Code: <?php if (mysql_num_rows($result)==0){ echo "<p> &nbsp</p><span class='heading'>No assignments have been issued for this module!</span>"; } else{ ?> <table width="100%" border="0" cellspacing="0" cellpadding="5"> <tr valign="top" class="heading"> <td width="25%">Assignment Title</td> <td width="40%">Criteria</td> <td width="15%">Date Sub </td> <td width="20%">Status</td> </tr> <?php $row_count = 1;//the counter for colouring the rows $lastassigID = ''; //the counter for getting the assignment titles mysql_data_seek($result,0) ; while ($row = mysql_fetch_array($result,MYSQL_ASSOC)){ $assigID=$row['Assignment_ID']; $title=$row['Title']; $datedue=$row['Date_due']; $datesubmit=$row['Date_submit']; $grade=$row['Grade_awarded']; $criteria=$row['Grade']; $achieved=$row['Achieved']; if($assigID<>$lastassigID){ $row_count++; if ($row_count % 2 == 0){ //EVEN $row_color="#FFFFFF"; } else{ //ODD $row_color="#EDEDED"; } echo '<tr bgcolor="' . $row_color . '">'; echo ' <tr bgcolor="' . $row_color . '"> <td valign="top"><a href="module.php?id='.$assigID.'">'.$title.'</a></td>'; $lastassigID=$assigID; //resetting the counter } else { echo '<tr bgcolor="' . $row_color . '">'; echo '<td valign="top"></td>'; } echo' <td>'.$criteria.'</td>';// this is the one that outputs my P1, P2, P3, etc… $date_due = strtotime($datedue); $date_submit = strtotime($datesubmit); $today = strtotime($todays_date); //here is the output for the Date – different colours and messages depending on the time of submission if (isset($datesubmit) && $date_submit>$date_due){ echo '<TD valign="top"><span class="late">'.$datesubmit.'</span></TD>'; } elseif (!isset($datesubmit) && $date_due>$today){ echo '<TD valign="top"><span class="unsubmit">Not submitted</span></TD>'; } elseif (!isset($datesubmit) && $date_due<$today){ echo '<TD valign="top"><span class="overdue">Not submitted</span></TD>'; } else{ echo '<TD valign="top"><span class="">'.$datesubmit.'</span></TD>'; } //here is the output for the STATUS, again different colours and messages if ($grade=="" && $date_due>$date_submit && isset($datesubmit)){ echo '<TD valign="top"><span class="unmarked">Awaiting marking</span></TD></TR>'; } elseif ($datesubmit=="" && $date_due<$today){ echo '<TD valign="top"><span class="fail">Overdue</span></TD></TR>'; } elseif ($grade=="" && $date_due>$date_submit){ echo '<TD valign="top"><span class="active">Active</span></TD></TR>'; } elseif ($grade==""){ echo '<TD valign="top"><span class="unmarked">Awaiting marking</span></TD></TR>'; } else{ switch($grade){ case "Pass": echo '<TD valign="top"><span class="pass">Pass</span></TD></TR>'; break; case "Merit": echo '<TD valign="top"><span class="pass">Merit</span></TD></TR>'; break; case "Distinction": echo '<TD valign="top"><span class="pass">Distinction</span></TD></TR>'; break; case "Not yet Achieved": echo '<TD valign="top"><span class="fail">Not yet Achieved</span></TD></TR>'; break; } } } } ?> </div> </body> </html> … and here’s the output looking like this: HTML Code: Assignment Title \| Criteria covered \| Date submitted \| Status Staff research P1 2008-03-12 Pass P2 2008-03-12 Pass P3 2008-03-12 Pass M1 2008-03-12 Pass M2 2008-03-12 Pass M3 2008-03-12 Pass D1 2008-03-12 Pass D2 2008-03-12 Pass D3 2008-03-12 Pass Games Techniques P4 2007-12-03 Merit P5 2007-12-03 Merit P6 2007-12-03 Merit M4 2007-12-03 Merit M5 2007-12-03 Merit M6 2007-12-03 Merit D4 2007-12-03 Merit D5 2007-12-03 Merit D5 2007-12-03 Merit Here is where I got stuck: how can I display them horizontally like this? HTML Code: Assignment Title \| Criteria covered \| Date submitted \| Status Staff research P1, P2, P3, M1, M2, M3, D1, D2, D3 2008-03-12 Pass Games techniques P4, P5, P6, M4, M5, M6, D4, D5, D6 2007-12-03 Merit I was thinking about a nested loop but then how will the inner loop know when to stop and let the outer loop carry on? I was thinking of setting a similar counter like the one for the title, so that as long as the assignment_ID is the same, the inner loop can carry on, but then how can I output the results horizontally on a single row, then the next row displaying the results of a second iteration of both loops? I’d really appreciate any help. Thanks ever so much.