Left join not displaying correctly on repeat region

MaDmiX

10-13-2012, 03:00 PM

Hi All,

I hope I am posting in the right forum. I honestly don't know for sure. Here goes...

I created the following query with a left join to pull records from a related table:
SELECT *
FROM tblonairactivity LEFT JOIN tblAffectedProg ON tblonairactivity.tblOnAirActivityID=tblaffectedprog.tblOnAirActivityID
WHERE (Network_INI = -1) AND (Airdate > varStartDate OR varStartDate IS NULL) AND (Airdate < varEndDate OR varEndDate IS NULL)
ORDER BY Airdate, DiscrepType

Now I have the following repeat region which is not displaying correctly:
</TABLE>
<?php
$oldAirdate = "";
$oldDiscrepType = "";
do {
$newAirdate = $row_rstINIactivity['Airdate'];
$newDiscrepType = $row_rstINIactivity['DiscrepType'];
?>
<table align="left" style="clear:right" >
<tr>
<TD width="15%" ALIGN=RIGHT BGCOLOR=#FFFFFF ><?php if ($oldAirdate <> $newAirdate) {echo $row_rstINIactivity['Airdate'];} ?></td>
<td width="10%"> </td>
<td width="75%"> </td>
</tr>
<tr>
<td> </td>
<td> </td>
<TD ALIGN=LEFT BGCOLOR=#FFFFFF ><?php if ($oldDiscrepType <> $newDiscrepType) {echo $row_rstINIactivity['DiscrepType'] . "'s";} ?></TD>
</tr>
<tr>
<td> </td>
<TD ALIGN=left BGCOLOR=#FFFFFF ><?php echo $row_rstINIactivity['DiscrepDetail']; ?>  </TD>
<TD ALIGN=LEFT BGCOLOR=#FFFFFF ><?php echo $row_rstINIactivity['Description']; ?></TD>
</tr>
<tr>
<td> </td>
<td> </td>
<TD ALIGN=LEFT BGCOLOR=#FFFFFF ><?php echo $row_rstINIactivity['Resolution']; ?></TD>
</tr>
<tr>
<td> </td>
<td> </td>
<TD ALIGN=LEFT BGCOLOR=#FFFFFF ><?php echo $row_rstINIactivity['Author']; ?></TD>
</tr>
</table>

<table id="vari" align="right" width="75%" border="0" <?php if ($row_rstINIactivity['OnAirVariance'] == -1) { echo "style='display: block'"; }else{ echo "style='display: none'";}?>>
<caption>
Details of affected content:
</caption>
<tr>
<th align="left" width="15%" scope="col">Start Time:</th>
<th align="left" width="15%" scope="col">End Time:</th>
<th align="left" width="15%" scope="col">Material ID:</th>
<th align="left" width="40%" scope="col">Title:</th>
<th align="left" width="15%" scope="col">Length:</th>
</tr>
<tr>
<td><?php echo $row_rstINIactivity['StartTime']; ?></td>
<td><?php echo $row_rstINIactivity['EndTime']; ?></td>
<td><?php echo $row_rstINIactivity['MaterialID']; ?></td>
<td><?php echo $row_rstINIactivity['Title']; ?></td>
<td></td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
</table>
 
<?php
$oldAirdate = $row_rstINIactivity['Airdate'];
$oldDiscrepType = $row_rstINIactivity['DiscrepType'];
}
while ($row_rstINIactivity = mysql_fetch_assoc($rstINIactivity)); ?>

What I was going for is that the "Details of affected content" would be listed all together under one entry, but instead I have three duplicate entries each with one of the three "Details..." entries. Here is the source code from my web browser so you can see what I mean. Thanks in advance for any help.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<script src="../javascripts/functions.js" type="text/javascript"></script>

<title>On Air Activity Report</title>
<script src="../javascripts/functions.js" type="text/javascript"></script>
<style type="text/css">
.print {
width: 6.5in;
display: block;/* float: left; */
font-family: Verdana, Geneva, sans-serif;
height: 9px;
margin-top: 0px;
margin-right: auto;
margin-bottom: 0px;
margin-left: auto;
}
.print fieldset legend {
font-weight: bold;
font-size: 14px;
}
#segmentInfo {
border-bottom-width: medium;
border-bottom-style: dotted;
}
.print fieldset {
padding: 15px;
border-top-color: #000;
border-right-color: #000;
border-bottom-color: #000;
border-left-color: #000;
}
h1 {
font-family: "Lucida Sans Unicode", "Lucida Grande", sans-serif;
font-size: x-large;
margin: 0px;
padding: 0px;
line-height: 20px;
text-align: right;
}
#mcommLogo {
float: left;
}
</style>
<script type="text/javascript">
function outageLength() {
var rows=document.getElementById("vari").getElementsByTagName("tr");
var result=0;

for (var x = 1; x < rows.length-1; x++) { //start at 1 to skip the header cells
var start=rows[x].cells[0].innerHTML;
var end=rows[x].cells[1].innerHTML;
var rowTotal=calculateTime(start,end);
rows[x].cells[4].innerHTML=rowTotal;

var rowSecs=TimeToSecs(rowTotal);
totalSecs=parseFloat(rowSecs);

if (!isNaN(totalSecs)) result += rowSecs;
}

var grandTotalElem = window.document.getElementById("grandTotal");
grandTotalElem.innerHTML=SecsToTime(result);
}

window.onload = function() {
outageLength();
};

</script>
</head>
<body>
<div class="print">
<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=0 >
<TR HEIGHT=34 >
<TD ALIGN=LEFT BGCOLOR=#FFFFFF >On Air Activity for 2012-10-12 to 2012-10-14</TD>
</TR>
</TABLE>
 
<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=0 >
<TR HEIGHT=24 >
<TD WIDTH="15%" ALIGN=LEFT BGCOLOR=#FFFFFF >Network:</TD>
<TD WIDTH="10%" ALIGN=LEFT BGCOLOR=#FFFFFF > </TD>
<TD WIDTH="75%" ALIGN=LEFT BGCOLOR=#FFFFFF >Event Type:</TD>
</TR>
</TABLE>
<HR>
<HR>
<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=0 >
<TR HEIGHT=21 >
<TD ALIGN=LEFT BGCOLOR=#FFFFFF >INI</TD>
</TR>
</TABLE>
<table align="left" style="clear:right" >
<tr>
<TD width="15%" ALIGN=RIGHT BGCOLOR=#FFFFFF >2012-10-13</td>
<td width="10%"> </td>
<td width="75%"> </td>
</tr>
<tr>
<td> </td>
<td> </td>
<TD ALIGN=LEFT BGCOLOR=#FFFFFF >Log issue's</TD>
</tr>
<tr>
<td> </td>
<TD ALIGN=left BGCOLOR=#FFFFFF >Log/Playlist mismatch  </TD>
<TD ALIGN=LEFT BGCOLOR=#FFFFFF >test</TD>
</tr>
<tr>
<td> </td>
<td> </td>
<TD ALIGN=LEFT BGCOLOR=#FFFFFF >test</TD>
</tr>
<tr>
<td> </td>
<td> </td>
<TD ALIGN=LEFT BGCOLOR=#FFFFFF >kberger@insp.com</TD>
</tr>
</table>

<table id="vari" align="right" width="75%" border="0" style='display: block'>
<caption>
Details of affected content:
</caption>
<tr>
<th align="left" width="15%" scope="col">Start Time:</th>
<th align="left" width="15%" scope="col">End Time:</th>
<th align="left" width="15%" scope="col">Material ID:</th>
<th align="left" width="40%" scope="col">Title:</th>
<th align="left" width="15%" scope="col">Length:</th>
</tr>
<tr>
<td>01:00:00</td>
<td>02:00:00</td>
<td>test1</td>
<td>test1</td>
<td></td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
</table>
 
<table align="left" style="clear:right" >
<tr>
<TD width="15%" ALIGN=RIGHT BGCOLOR=#FFFFFF ></td>
<td width="10%"> </td>
<td width="75%"> </td>
</tr>
<tr>
<td> </td>
<td> </td>
<TD ALIGN=LEFT BGCOLOR=#FFFFFF ></TD>
</tr>
<tr>
<td> </td>
<TD ALIGN=left BGCOLOR=#FFFFFF >Log/Playlist mismatch  </TD>
<TD ALIGN=LEFT BGCOLOR=#FFFFFF >test</TD>
</tr>
<tr>
<td> </td>
<td> </td>
<TD ALIGN=LEFT BGCOLOR=#FFFFFF >test</TD>
</tr>
<tr>
<td> </td>
<td> </td>
<TD ALIGN=LEFT BGCOLOR=#FFFFFF >kberger@insp.com</TD>
</tr>
</table>

<table id="vari" align="right" width="75%" border="0" style='display: block'>
<caption>
Details of affected content:
</caption>
<tr>
<th align="left" width="15%" scope="col">Start Time:</th>
<th align="left" width="15%" scope="col">End Time:</th>
<th align="left" width="15%" scope="col">Material ID:</th>
<th align="left" width="40%" scope="col">Title:</th>
<th align="left" width="15%" scope="col">Length:</th>
</tr>
<tr>
<td>02:00:00</td>
<td>03:00:00</td>
<td>test2</td>
<td>test2</td>
<td></td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
</table>
 
<table align="left" style="clear:right" >
<tr>
<TD width="15%" ALIGN=RIGHT BGCOLOR=#FFFFFF ></td>
<td width="10%"> </td>
<td width="75%"> </td>
</tr>
<tr>
<td> </td>
<td> </td>
<TD ALIGN=LEFT BGCOLOR=#FFFFFF ></TD>
</tr>
<tr>
<td> </td>
<TD ALIGN=left BGCOLOR=#FFFFFF >Log/Playlist mismatch  </TD>
<TD ALIGN=LEFT BGCOLOR=#FFFFFF >test</TD>
</tr>
<tr>
<td> </td>
<td> </td>
<TD ALIGN=LEFT BGCOLOR=#FFFFFF >test</TD>
</tr>
<tr>
<td> </td>
<td> </td>
<TD ALIGN=LEFT BGCOLOR=#FFFFFF >kberger@insp.com</TD>
</tr>
</table>

<table id="vari" align="right" width="75%" border="0" style='display: block'>
<caption>
Details of affected content:
</caption>
<tr>
<th align="left" width="15%" scope="col">Start Time:</th>
<th align="left" width="15%" scope="col">End Time:</th>
<th align="left" width="15%" scope="col">Material ID:</th>
<th align="left" width="40%" scope="col">Title:</th>
<th align="left" width="15%" scope="col">Length:</th>
</tr>
<tr>
<td>03:00:00</td>
<td>04:00:00</td>
<td>test3</td>
<td>test3</td>
<td></td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
</table>
 
 
 
</div>
</body>
</html>

Kind regards,

Ken

tangoforce

10-13-2012, 05:36 PM

while ($row_rstINIactivity = mysql_fetch_assoc($rstINIactivity)); ?>

I'm not entirely sure what you are trying to do with all your code / description but the above line stuck out imediately.

Why is it at the end and where is the rest of the loop? :confused:

MaDmiX

10-13-2012, 06:18 PM

Hi tangoforce,

I think I need to have a loop within a loop. The first loop, loops through the parent table records and then I need another loop to loop through the child records. I tried this, however but it didn't work though I might not have done it correctly.

What I am trying to do is have each of the parent records display with all of it's child records displaying under it.

Thanks,

Ken

Fou-Lu

10-13-2012, 07:05 PM

Hey Tango, think you missed the do at the top.
I will suggest that do/while should never be used with a resource. do/while indicates you are planning to iterate at least one time (guaranteed), so even with controls using a count from the rows isn't the best idea logically. Think more along the lines of other resources such as sockets and files. Using a controlled do/while with resources is a hassle if you want to avoid the possibility of any error. I would suggest that a do/while would work for iterating items within a resource, but not for the resource itself (ie: while <- row, do/while <- fields in row).

Using LEFT and RIGHT joins often create duplicate records. They aren't actually duplicates (as the entire record is usually not unique), but the primary table will repeat its region data for each record available in the joined table. This is where your code comes into play.
You won't need multiple loops, simply controls for them. I don't want to go through this HTML to figure out what you need to do, so instead just do a simple:

mysql_data_seek($rstINIactivity, 0);
while ($row = mysql_fetch_assoc($rstINIactivity))
{
var_export($row);
}

And post that result for the recordsets (from the HTML source, not the browser). Then write a simple ascii representation of what you want it to look like and post that.

tangoforce

10-13-2012, 09:48 PM

Hey Tango, think you missed the do at the top.

Ooops so I did! :o

MaDmiX

10-15-2012, 09:04 PM

Hi Fou-Lu,

Not really sure I understand everything you said there (most of it went over my head). I researched the mysql_data_seek and var_export functions to see if I could get some insight but no luck.

:confused:

Fou-Lu

10-15-2012, 10:36 PM

mysql_data_seek moves the pointer back to the specified record. If you try calling it after the initial iteration you have, then it will return no results since it's already reached the end of the resultset. I just move it back to the first to run through another loop.
The var_export creates an output that can be interpreted with PHP directly. Objects don't seem to work well unless you have overridden the __set_state, but in arrays they'll dump the text in a way I can just copy and recreate the arrays again.

MaDmiX

10-25-2012, 02:10 PM

Hi All,

I got this figured out. I did wind up using nested loops but I abandoned the LEFT JOIN in favor of two separate queries. The placement of the second query had to be within the first loop to make it work. Here is the final working code:

<?php
$oldAirdate = "";
$oldDiscrepType = "";
do {
$newAirdate = $row_rstINIactivity['Airdate'];
$newDiscrepType = $row_rstINIactivity['DiscrepType'];
?>
<table width="100%" align="left" style="clear:right" >
<tr>
<TD width="15%" ALIGN=LEFT BGCOLOR=#FFFFFF ><?php if ($oldAirdate <> $newAirdate) {echo $row_rstINIactivity['Airdate'];} ?></td>
<td width="10%"> </td>
<td width="75%"> </td>
</tr>
<tr>
<td> </td>
<td> </td>
<TD ALIGN=LEFT BGCOLOR=#FFFFFF ><?php if ($oldDiscrepType <> $newDiscrepType or $oldAirdate <> $newAirdate) {echo $row_rstINIactivity['DiscrepType'] . "'s";} ?></TD>
</tr>
<tr>
<td></td>
<TD ALIGN=left BGCOLOR=#FFFFFF ><?php echo $row_rstINIactivity['DiscrepDetail']; ?>  </TD>
<TD ALIGN=LEFT BGCOLOR=#FFFFFF ><?php echo $row_rstINIactivity['Description']; ?></TD>
</tr>
<tr>
<td> </td>
<td> </td>
<TD ALIGN=LEFT BGCOLOR=#FFFFFF ><?php echo $row_rstINIactivity['Resolution']; ?></TD>
</tr>
<tr>
<td> </td>
<td> </td>
<td>Networks affected: 
<?php if ($row_rstINIactivity['Network_INI']== -1) {echo ("INI");} ?>
<?php if ($row_rstINIactivity['Network_INI'] + $row_rstINIactivity['Network_INSP'] + $row_rstINIactivity['Network_Halogen'] < -1) {echo (", ");} ?>
<?php if ($row_rstINIactivity['Network_INSP']== -1) {echo ("INSP");} ?>
<?php if ($row_rstINIactivity['Network_INI'] + $row_rstINIactivity['Network_INSP'] + $row_rstINIactivity['Network_Halogen'] < -1) {echo (", ");} ?>
<?php if ($row_rstINIactivity['Network_Halogen']== -1) {echo ("Halogen");} ?>
<?php if ($row_rstINIactivity['Network_INI'] + $row_rstINIactivity['Network_INSP'] + $row_rstINIactivity['Network_Halogen'] > -1) {echo ("None");} ?>
</td>
</tr>
<tr>
<td> </td>
<td> </td>
<TD ALIGN=LEFT BGCOLOR=#FFFFFF >Entered by <?php echo $row_rstINIactivity['Author']; ?></TD>
</tr>
</table>
<?php
mysql_select_db($database_connAirtrackII, $connAirtrackII);
$query_rstINIaffected = "SELECT * FROM tblaffectedprog WHERE tblOnAirActivityID = ".$row_rstINIactivity['tblOnAirActivityID']." ORDER BY tblAffectedProgID ASC";
$rstINIaffected = mysql_query($query_rstINIaffected, $connAirtrackII) or die(mysql_error());
$row_rstINIaffected = mysql_fetch_assoc($rstINIaffected);
$totalRows_rstINIaffected = mysql_num_rows($rstINIaffected);
?>
<table id="vari<?php echo $row_rstINIactivity['tblOnAirActivityID']; ?>" align="right" width="70%" border="0" cellspacing="0" <?php if ($row_rstINIactivity['OnAirVariance'] == -1) { echo "style='display: block'"; }else{ echo "style='display: none'";}?>>
<caption align="left">
Details of affected content:
</caption>
<tr>
<th align="left" width="15%" scope="col">Start Time:</th>
<th align="left" width="15%" scope="col">End Time:</th>
<th align="left" width="15%" scope="col">Material ID:</th>
<th align="left" width="40%" scope="col">Title:</th>
<th align="left" width="15%" scope="col">Duration:</th>
</tr>
<?php do { ?>
<tr id="variData" FONT style=FONT-SIZE:8pt FACE="Calibri" COLOR=#000000>
<td><?php echo $row_rstINIaffected['StartTime']; ?></td>
<td><?php echo $row_rstINIaffected['EndTime']; ?></td>
<td><?php echo $row_rstINIaffected['MaterialID']; ?></td>
<td><?php echo $row_rstINIaffected['Title']; ?></td>
<td></td>
</tr>
<?php } while ($row_rstINIaffected = mysql_fetch_assoc($rstINIaffected)); ?>
<tr id="footer" FONT style=FONT-SIZE:8pt FACE="Calibri" COLOR=#000000>
<td></td>
<td></td>
<td></td>
<td align="right">Outage Duration:  </td>
<td id="grandTotal<?php echo $row_rstINIactivity['tblOnAirActivityID']; ?>"></td>
</tr>
</table>
<script type="text/javascript">
(
function() {
var rows=document.getElementById("vari<?php echo $row_rstINIactivity['tblOnAirActivityID']; ?>").getElementsByTagName("tr");
var result=0;

for (var x = 1; x < rows.length-1; x++) { //start at 1 to skip the header cells
var start=rows[x].cells[0].innerHTML;
var end=rows[x].cells[1].innerHTML;
var rowTotal=calculateTime(start,end);
rows[x].cells[4].innerHTML=rowTotal;

var rowSecs=TimeToSecs(rowTotal);
totalSecs=parseFloat(rowSecs);

if (!isNaN(totalSecs)) result += rowSecs;
}

var grandTotalElem = window.document.getElementById("grandTotal<?php echo $row_rstINIactivity['tblOnAirActivityID']; ?>");
grandTotalElem.innerHTML=SecsToTime(result);
}
) ();
</script>
 
<?php
$oldAirdate = $row_rstINIactivity['Airdate'];
$oldDiscrepType = $row_rstINIactivity['DiscrepType'];
}
while ($row_rstINIactivity = mysql_fetch_assoc($rstINIactivity)); ?>

Thanks for all of you help everyone!

Ken