homepage Welcome to WebmasterWorld Guest from 174.129.80.166
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Nested "while" loops excluding record
beaudettee




msg:4285862
 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




msg:4286359
 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




msg:4286384
 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




msg:4286898
 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.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved