AHH GOING CRAZY! WHILE LOOP INSIDE OF WHILE LOOP

[innopar]

Good Evening -  I am in the process of trying to call back a list of categories and sub categories using a WHILE LOOP inside of a WHILE LOOP. It works on a different part of the site within the admin panel but not here. Here it only calls one sub category and moves on to the next parent category instead of finishing the loop and pulling all sub categories out...

 

// CATEGORIES

			$query  = "SELECT * FROM cat";
			$result = mysql_query($query);	



					while($row = mysql_fetch_array($result, MYSQL_ASSOC))
			{

				$catid = $row['id'];
				$catname = $row['name'];

					$output .= "<li class=\"level0 nav-2 parent\" onmouseover=\"toggleMenu(this,1)\" onmouseout=\"toggleMenu(this,0)\">
						<a href=\"product.php?cat=$catid\">
							<span>$catname</span>
						</a>\n";


					$querynav = "SELECT * FROM subcat WHERE pid = '$catid'";
					$resultnav = mysql_query($querynav);	

					while($array = mysql_fetch_array($resultnav, MYSQL_ASSOC))
					{

						$subcatid = $row['id'];
						$subcatname = $row['name'];

							$output .= "<ul class=\"level0\">
								<li class=\"level1 nav-2-1 first\">
									<a href=\"product.php?cat=$catid&subid=$subcatid\">
										<span>$subcatname</span>
									</a>
								</li>
							</ul>
						</li>";
					}

	}

 

[Psycho]

I don't see anything abvious. But, you only need one while loop and, more importantly, you only need one query

 

$query = "SELECT c.name as cat_name, c.id as cat_id,
                 sc.name as subcat_name
          FROM cat
          JOIN subcat as sc ON c.id = sc.pid";
$result = mysql_query($query);

$currentCatID = false;
while($row = mysql_fetch_assoc($result))
{
    if($currentCatID != $row['cat_id'])
    {
        $currentCatID = $row['cat_id'];
        $output .= "<li class=\"level0 nav-2 parent\" onmouseover=\"toggleMenu(this,1)\" onmouseout=\"toggleMenu(this,0)\">
        <a href=\"product.php?cat={$row['cat_id']}\">
        <span>{$row['cat_name']}</span>
        </a>\n";
    }
    $output .= "<ul class=\"level0\">
                <li class=\"level1 nav-2-1 first\">
                <a href=\"product.php?cat=$catid&subid={$row['subcat_id']}\">
                <span>{$row['subcat_name']}</span>
                </a>
                </li>
                </ul>
                </li>";
}

[innopar]

Interesting, I've never seen code in php like that before  I don't know how to manipulate it work for my needs. I just tried to copy and past and its a no go 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

 

[innopar]

ah ...

$query = "SELECT cat.id as cat_id, cat.name as cat_name, subcat.id as subcat_id, subcat.pid as subcat_pid, subcat.name as subcat_name FROM cat LEFT JOIN subcat ON subcat.pid = cat.id";


$result = mysql_query($query);

$currentCatID = false;
while($row = mysql_fetch_assoc($result))
{
    if($currentCatID != $row['cat_id'])
    {
        $currentCatID = $row['cat_id'];
        $output .= "<li class=\"level0 nav-2 parent\" onmouseover=\"toggleMenu(this,1)\" onmouseout=\"toggleMenu(this,0)\">
        <a href=\"product.php?cat={$row['cat_id']}\">
        <span>{$row['name']}</span>
        </a>\n";
    }
    $output .= "<ul class=\"level0\">
                <li class=\"level1 nav-2-1 first\">
                <a href=\"product.php?cat=$catid&subid={$row['subcat_id']}\">
                <span>{$row['subcat_name']}</span>
                </a>
                </li>
                </ul>
                </li>";
}

 

 

Now it's doing the parent category in the top output and the bottom but no error?

[innopar]

Alright update... again

 

$query = "SELECT * FROM cat AS c LEFT JOIN subcat as cl ON cl.pid = c.id";


$result = mysql_query($query);

$currentCatID = false;
while($row = mysql_fetch_assoc($result))
{
    if($currentCatID != $row['id'])
    {
        $currentCatID = $row['pid'];
        $output .= "<li class=\"level0 nav-2 parent\" onmouseover=\"toggleMenu(this,1)\" onmouseout=\"toggleMenu(this,0)\">
        <a href=\"product.php?cat={$row['cat_id']}\">
        <span>{$row['name']}</span>
        </a>\n";
    }
    $output .= "<ul class=\"level0\">
                <li class=\"level1 nav-2-1 first\">
                <a href=\"product.php?cat=$cat_id&subid={$row['id']}\">
                <span>{$row['title']}</span>
                </a>
                </li>
                </ul>
                </li>";
}

 

I found out that using the same variable in two tables made things ugly. So I changed the name in the 'subcat' table from 'name' to 'title' to be different from the 'cat' table. Now the code runs and it does each individual list and sublist for each sub category. I don't want that. I want one parent cat and then the sub cats to fall directly under it for a nice menu. What am I doing wrong??

 

[Psycho]

The loop has an IF condition to test if the parent record is different from the last one. I used the flag $currentCatID. If the value of the current record's parent ID is different from the last one, then show the parent record details and set $currentCatID to the parent's ID. Then on the next iteration of the loop, if the parentID of the record is the same it doesn't show the parent data.

 

However, in your modificaion of the database field names you used one value to do the test and another when setting the value!

    if($currentCatID != $row['id'])
    {
        $currentCatID = $row['pid'];

 

A couple other things.

 

There are some problems with the HTML content you are creating that will generate orphaned HTML tags and invalid markup. I *think* I know what you are trying to achieve. It seems the parent record should be part of a list since it starts with an LI tag. But, there is no ending LI tag. It looks like you are trying to put that closing LI tag for the parent code at the end of the code for the sub category tag. But, that won't work because you would generate multiple closing LI tags for each sub category. In this situation, where you need some closing code after all the child records have been displayed, I prefer to take a different approach. I will create a function to display the parent and all the child records at once. Then in the loop I will create an array to hold all the values. Once a parent and all the child records have been added I run the function. I also think this method makes it much easier to visually "see" how the code is produced

 

Also, you are using SELECT * in your function. Unless you really need all of the fields, it is more efficient to only query for the data you need.

 

Lastly, since you have fields in the two tables that are "similar", instead of calling one "name" and the other "title", just name them something like "cat_name" and "subcat_name". (Also, unless you changed it you also have a problem where the ID field for both tables is named the same). It may seem redundant to reference a field such as "cat.cat_name", but it is very helpful when you have foreign IDs to be able to tell what table the ID is linked to. So, if you used cat_id in the cat table and the subcat table, you could JOIN the two tables using the USING statement, such as

SELECT *
FROM cat
JOIN subcat USING cat_id

 

Anyway, here is some revised code. Note: I put the comment "//DB NAME" after each line where the code references a field from the query. If you need to modify the query make sure you modify these lines as necessary. Also note that this is not tested since I don't have your DB to run against and I wasn't going to create a db table and mock data just to test it. So, there might be some syntax errors, but the logic should be good.

//Function to generate HTML output for category and its subcategories
function categoryHTML($subcatList)
{
    //Start parent category output (get name/id from first record)
    $categoryName = $subcatList[0]['cat_name']; //DB NAME
    $categoryID   = $subcatList[0]['cat_id'];   //DB NAME
    $htmlOutput  = "<li class=\"level0 nav-2 parent\" onmouseover=\"toggleMenu(this,1)\" onmouseout=\"toggleMenu(this,0)\">\n";
    $htmlOutput .= "  <a href=\"product.php?cat={$categoryID}\"><span>{$categoryName}</span></a>\n";
    $htmlOutput .= "  <ul class=\"level0\">\n";
    //Create subcategory ouput
    foreach($subcatList as $subcat)
    {
        $subCatName = $subcat['subcat_name']; //DB NAME
        $subCatID   = $subcat['subcat_id'];   //DB NAME
        $htmlOutput .= "    <li class=\"level1 nav-2-1 first\">\n";
        $htmlOutput .= "      <a href=\"product.php?cat={$categoryID}&subid={$subCatID}\"><span>{$subCatName}</span></a>\n";
        $htmlOutput .= "    </li>\n";
    }
    //Close parent category output
    $htmlOutput .= "  </ul>\n";
    $htmlOutput = "</li>\n";
}

//Create and run query
$query = "SELECT cat.id as cat_id, cat.name as cat_name,
                 subcat.title as subcat_name, subcat.id as subcat_id
          FROM cat
          LEFT JOIN subcat ON subcat.pid = cat.id";
$result = mysql_query($query);

//Process the output
$categoryID = false; //Flag to determine change in category
while($row = mysql_fetch_assoc($result))
{
    //Check if new category from last record
    if($categoryID != $row['cat_id']) //DB NAME
    {
        //New category - set flag
        $categoryID = $row['cat_id']; //DB NAME
        if(isset($subcategoryList))
        {
            //Get content for previous category/subcategory data
            $output .= categoryHTML($subcategoryList);
        }
        //Reset list
        $subcategoryList = array();
    }
    $subcategoryList[] = $row;
}
//Get content for last category/subcategory data
$output .= categoryHTML($subcategoryList);

[spaceman12]

Either you echo it out and you will see all the output or you will only get the value that was at last.