homepage Welcome to WebmasterWorld Guest from 54.161.191.154
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Nested "while" loops excluding record
beaudettee

5+ Year Member



 
Msg#: 4285860 posted 6:14 pm on Mar 22, 2011 (gmt 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!

 

rocknbil

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



 
Msg#: 4285860 posted 4:46 pm on Mar 23, 2011 (gmt 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 . . .

beaudettee

5+ Year Member



 
Msg#: 4285860 posted 5:25 pm on Mar 23, 2011 (gmt 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!

rocknbil

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



 
Msg#: 4285860 posted 4:07 pm on Mar 24, 2011 (gmt 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. :-)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved