While Loop Not Showing All Records

[PCHomepage]

In a relatively simple WHILE loop that should show a half dozen or so records, only one is showing and it is appearing twice so there is clearly a typo somewhere but I cannot spot it. Is there an eagle-eye who can help? Thank you!

function ListRecordings($DBconn)  {
	$Query = "SELECT r.ID AS RecordingID, r.Format AS FormatID, r.CatalogNo, r.Description, r.Year, r.ArchiveCopy, 
		r.Bootleg, p.Phonic, fc.Format AS FormatText, fc.FormatOrder AS FormatOrder, c.CountryName AS CountryText, 
		l.LabelName AS LabelText, fc.Format AS FormatCaptionText, ca.ImageID1 AS ImageID, rt.Title AS Title, 
		r.PressingHistory 
		FROM ((((((`conn`.recordings r 
		LEFT JOIN `conn`.phonic p ON p.ID = r.MonoStereo) 
		LEFT JOIN `geoip`.countries c ON c.ID = r.Country) 
		LEFT JOIN `conn`.labels l ON l.LabelID = r.Label) 
		LEFT JOIN `conn`.formats fc ON fc.ID = r.Format) 
		LEFT JOIN `conn`.coverart_assignments ca ON ca.RecordingUsed = r.ID) 
		LEFT JOIN `conn`.recordtitles rt ON rt.ID = r.Title) ";

	$Where = " WHERE r.EntryVerified = 1"; // Initializes $Where variable
	$Where .= (CCGetParam("OriginalPress","")) ? " AND r.PressingHistory = ".CCGetParam("OriginalPress",""): "";
	$Where .= (CCGetParam("Bootleg","")) ? " AND r.Bootleg = ".CCGetParam("Bootleg",""): "";
	$Where .=  (CCGetParam("Country","")) ? " AND r.Country = ".CCGetParam("Country",""): "";
	$Where .=  (CCGetParam("Format","")) ? " AND r.Format = ".CCGetParam("Format",""): "";

	$Order = (CCGetParam("OriginalPress","") == 2) ? 
				" ORDER BY fc.FormatOrder, Year": 
				" ORDER BY fc.FormatOrder, rt.Title, Year";
						
	$Query = $Query . $Where . $Order;

	$DBconn->query($Query);

	[COLOR=red]while ($DBconn->next_record()) :
		$RecordID = $DBconn->f("RecordingID");
		$ImageID = $DBconn->f("ImageID");
		$FormatID = $DBconn->f("FormatID");
		$FormatText = $DBconn->f("FormatText");
		$Title = $DBconn->f("Title");
		$CatalogNo = $DBconn->f("CatalogNo");
		$Description = $DBconn->f("Description");

		$Category = (CCGetParam("s_keyword",""))  ? DLookup("CategoryName", "lookup_categories", "ID=15", $DBconn): "";

		$RecordList = "";			
		
		global $last_type; // Show format type caption only once until it changes to the next type
		if ($FormatID != $last_type) :
			//$RecordList .= "<div class=\"AlbumFormat\">". $FormatText ."s</div>";
			$RecordList .= $FormatText . "s<br>\n";
			$last_type = $FormatID;
		else :
			$FormatID = "";
		endif;

		$RecordList .= "<p>$Title<br>\n";

		$Quirk = time().randchr(10);
		$Thumbnail = DLookup("Thumbnail", "images_coverart", "ID='" . $ImageID . "'", $DBconn);

		if ($ImageID) :
			$ImageID = $ImageID;
		elseif ($ImageID && !$Thumbnail) :
			$ImageID = "104"; // shows Image Coming Soon if there is no thumbnail
		else :
			$ImageID = "360"; // shows No Image if there is no cover art assignment entry
		endif;

		$RecordList .=  (GetUserID() && GetGroupID() == 4)  ?
					"<a href=\"/administration/coverart_admin.php?ID=" . $ImageID  . "\">":
					"<a href=\"/recording_details.php?ID=" . $RecordID  . "\">";

		$RecordList .= "<img border=\"0\" src=\"/show_image.php?ID=$ImageID&Quirk=$Quirk&Type=2\" align=\"left\" width=\"50\" class=\"ImageLeft\"></a>";
		$RecordList .= ($CatalogNo && $CatalogNo != "NA")  ? "<font size=\"-1\">catalog no: $CatalogNo</font><br>": "";
		$RecordList .= (strlen($Description) > 300) ?
                                strip_tags(substr_replace($Description, ' . . .', 300)):
				$RecordList .= strip_tags($Description);

		endif;
	endwhile;[/color]
 
	return $RecordList;
}

 

[PCHomepage]

I found the reason for the duplicate (an extra $RecordList .= ) but not yet for why there is only one entry showing when there should be a half dozen!

 

[jpadie]

because you are resetting the $RecordList variable to "" each iteration of the while loop. move the variable instantiation outside of the loop.

$RecordList = "";	
while ($DBconn->next_record()) :
...
endwhile;
return $RecordList;

 

[PCHomepage]

I had tried it both inside and outside the WHILE loop, which is what led me to believe there must be a typo somewhere. Currently it is outside the loop and is being used to begin a div tag that will surround the listing. Even with it outside, it still returns only a single result.

 

[jpadie]

echo out the complete query and run it in a mysql client to see whether it returns more than one row.

if there were a typo then the code would not execute at all; and a syntax error would be thrown.

you could also footprint your code to tell you how many rows it is iterating over etc etc.

but a sure-fire way to have only one result is to leave the $RecordList = ''; in the middle of the while loop.

 

[PCHomepage]

I did that, of course, to verify that the dynamic query is functioning and it is indeed. At its most basic and the choice I am using for testing the function, it returns exactly ten rows.

I am thinking of a typo such as a missing dot or something similar that is resetting the RecordList variable that would not actually throw a syntax error. I've looked through the code until I'm nearly blue in the face and I see nothing to account for only a single result being fetched!

I'm not sure how to "footprint" the code.

 

[PCHomepage]

I found the problem although it's not clear why it did what it did, which was the DLookup() function call about midway through the WHILE loop. I've used this function many times inside loops so it's something of a mystery as to why it caused the odd failure this time. Removing it and editing the condition that followed it fixed it and it is now giving the proper number of results!

$Thumbnail = DLookup("Thumbnail", "images_coverart", "ID='" . $ImageID . "'", $DBconn);

 

[jpadie]

no idea what DLookup is. i guess it is one of your user-functions.

anyway, well done for finding the problem.

footprinting code is as simple as echo'ing output at particular points in your code where you want to test against known intended outputs.