Welcome to WebmasterWorld Guest from 54.167.102.69

Forum Moderators: open

Message Too Old, No Replies

Nested "while" loops excluding record

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

New User

5+ Year Member

joined:Aug 21, 2007
posts: 20
votes: 0


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 "&nbsp;&nbsp;&nbsp;- <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!
4:46 pm on Mar 23, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


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 . . .
5:25 pm on Mar 23, 2011 (gmt 0)

New User

5+ Year Member

joined:Aug 21, 2007
posts: 20
votes: 0


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!
4:07 pm on Mar 24, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


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. :-)