Welcome to WebmasterWorld Guest from 54.224.57.95

Forum Moderators: open

Message Too Old, No Replies

Nested "while" loops excluding record

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

5+ 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 "&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)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 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 . . .
5:25 pm on Mar 23, 2011 (gmt 0)

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

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 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. :-)
 

Featured Threads

Hot Threads This Week

Hot Threads This Month