Category Tree?

PHP
1

Hi guys, I’ve attempted to create a category tree for site navigation but doesn’t want to work, not sure what else I can do. Any ideas or pointers would be useful. Please see my code below!

    function categoriesLeft($sql){
        $sql = mysql_query(($sql));
            while($result = mysql_fetch_assoc($sql)):
                echo $result['categories_name']."<br/>";
              
                //This section doesn't work...
                while($result['parent_id']==$results['categories_id']):
                      echo "<li>".$result['categories_name']."</li>";
                endwhile;
                
            endwhile;
    }

2

could we see the query please

3

Well, from an HTML standpoint you can’t have CDATA and a BR as a sibling to a LI…

but yeah, without the query it’s hard to judge… though the use of while/endwhile is outright bizzare since you aren’t wildly opening and closing <?php ?> for nothing like many others do. I’d also not overwrite the query string with itself… and I’m not sure mysql_fetch_assoc “works” that way.

this in particular:

while ($result[‘parent_id’]==$results[‘categories_id’])

Since fetch_assoc is only returning one row – the most that would ever loop is ONCE… and that’s only if the cat_id matches the parent. I almost think you have the logic flow backwards… either that or you need to run a mixed query or more than one query.

Basically your logic flow doesnae make any sense.

4

Hi there, ok I’ve had a play about with my code and come up with the following which is perfect…anybody see anything which could work better done differently, any advice is appreciated.

    function categoriesLeft($table){
        $sql = mysql_query(("SELECT * FROM $table WHERE parent_id=0"));
            while($result = mysql_fetch_assoc($sql)):
                echo $result['categories_name']."<br/>";
            
                //Query to gather all of the sub_categories with a matching parent_id...
                $subs = mysql_query("SELECT * FROM $table WHERE parent_id=$result[categories_id]");
                
                while($row = mysql_fetch_assoc($subs)):
                    //Print out all subcategories of current category...
                    echo "<li>".$row['categories_name']."</li>";
                    
                        //Controller if a category matches the current one, change class...
                        if($row['categories_id']==$_GET['id']):
                            echo "*";
                        endif;
                    
                endwhile;
                
            endwhile;
    }
5

My only concern would be the nonsensical markup.


SomeCategory<br />
<li>SubCategory</li>
*

Invalid HTML. That is gibberish/invalid markup since CDATA and BR again, cannot be siblings to LI. Since this is a category tree, it would actually make more sense for this to be a nested list. (though it’s hard to say if you are omitting the actual output logic)

You also are using double quotes in a number of places where it makes no sense, like on echo – why waste time taking longer when you don’t have to. You also are using string additions in places where that doesn’t make sense either. Single quotes on your echo’s would let you have nicely formatted output that’s easy to debug.

something like:


function categoriesLeft($table){

	$parentResult = mysql_query("
		SELECT * FROM $table
		WHERE parent_id=0
	");
	
	echo '
	<ul class="categoryTree">';
		
	while ($parentRow=mysql_fetch_assoc($sql)) {
	
		echo '
		<li>
			',$parentRow['categories_name'];
	
		$childResult=mysql_query("
			SELECT * FROM $table
			WHERE parent_id=$result[categories_id]
		");
		
		if (mysql_num_rows($childResult)>0) {
			echo '
			<ul>';
		
			while ($childRow=mysql_fetch_assoc($subs)) {
				echo '
				<li>',$row['categories_name'],(
					$row['categories_id']==$_GET['id'] ? '*' : ''
				),'</li>';
			}
			
			echo '
			</ul>';
		}
		echo '
		</li>';
	}
	echo '
	</ul>';
}

Which would output something like:


<ul class="categoryTree">
	<li>
		ParentCategoryWithKids
		<ul>
			<li>ChildCategory</li>
			<li>ChildCategoryMatchesGet*</li>
		</ul>
	</li>
	<li>
		ParentCategoryNoKids
	</li>
</ul>

VALID HTML, it helps… a lot.

6

doing a query inside a loop is really inefficient

you should do a single join query, and adjust the php to loop over the result rows and detect the control breaks in the main categories

SELECT parent.categories_name AS parent_name
     , child.categories_name AS child_name
  FROM $table AS parent
LEFT OUTER
  JOIN $table AS child
    ON child.parent_id = parent.categories_id
 WHERE parent.parent_id = 0
ORDER
    BY parent.categories_name
     , child.categories_name
7

Hi guys, thanks for the help, advice and pointers, always good to learn new things. I was going to run the HTML through the validator, hectic weekend! r937, would you then place the query outside of the while-loop? Kind of makes sense I guess :slight_smile: