Nested "while" loops excluding record - Databases forum at WebmasterWorld - WebmasterWorld

Forum Moderators: open

Message Too Old, No Replies

Nested "while" loops excluding record

beaudettee

6:14 pm on Mar 22, 2011 (gmt 0)

10+ Year Member

Hi All:

I have 2 very simple tables: 1 named categories and one named files. My goal is to loop through the categories table and display the catname, then loop through the files table and display all the files with a fCatname the same as the catname.

Her is my code:
<?php
// Make a MySQL Connection
$query = "SELECT * FROM category WHERE cDisplay = 'Yes' ORDER BY cLabel";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result))
{
echo "<strong>". $row['cLabel'] . "</strong><br />";
$Category = $row['cLabel'];
$result2 = mysql_query("SELECT * FROM files WHERE file_display='Yes' AND file_catname='$Category' ORDER BY file_label ASC") or die(mysql_error());
while($row2 = mysql_fetch_array($result2))
{
$fileLabel = $row2['file_label'];
$fileName = $row2['file_name'];
$fileLoc = "secure/" . $fileName;
echo "   - <a href='". $fileLoc ."'>" . $fileLabel. "</a><br>";
} echo "<br><br>";
}
?>
Now, this all seems to work fine with one exception: For some reason 1 category is left out EVERY time. If I run the category query separate from the files query it displays all the categories. Strangely, the same issue arises when I am populating a select box option in html.. 1 record always seems to be left out.

I am sure I am overlooking something very silly here so hopefully someone else can catch it. Thanks in advance!

rocknbil

4:46 pm on Mar 23, 2011 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Is it always the same category? I'd check this:

$query = "SELECT * FROM category WHERE cDisplay = 'Yes' ORDER BY cLabel";

because 'Yes', 'yes', ' yes', ' Yes' . . . are all distinctly different values. This would be much more efficient as a boolean or tinyint field (and eliminate this possibility as a culprit,) then you just do . . .

$YesNos = Array('No','Yes');

echo "Display? " . $YesNos[$row['cDisplay']];

The same concept - both of them - applies here, and in addition that you should be storing ID for category in the files table instead of the category name. Numeric lookups will **always** be faster than text matches.

$cat_id = $row['id'];
$result2 = mysql_query("SELECT * FROM files WHERE file_display='Yes' AND file_catname='$cat_id' ORDER BY file_label ASC");

I don't see any other reason with what you posted . . .

beaudettee

5:25 pm on Mar 23, 2011 (gmt 0)

10+ Year Member

Thanks for the reply, Ricknbil.

Yes, I thought about cat_id and will probably change that to make things a bit more efficient but am controlling the text input with dropdowns so format is OK (for now).

I actually did find my issue. I changed this:

$query = "SELECT * FROM category WHERE cDisplay = 'Yes' ORDER BY cLabel";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result))

to this:

$result = mysql_query("SELECT * FROM category WHERE cDisplay = 'Yes' ORDER BY cLabel") or die(mysql_error());
while($row = mysql_fetch_array( $result ))

and all results show. When you are staring at this stuff all day it sometimes blurs, which is the case here.

Thanks again!

rocknbil

4:07 pm on Mar 24, 2011 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

That makes absolutely no sense at all, it doesn't matter if you store the select in a variable or dump it in the command parameter (and is a better way of working IMO . . . ) something else had to have changed. :-)