SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: Category Tree?

  1. #1
    SitePoint Evangelist
    Join Date
    Aug 2010
    Posts
    503
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Category Tree?

    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!

    Code PHP:
        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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    could we see the query please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Non-Member bronze trophy
    Join Date
    Nov 2009
    Location
    Keene, NH
    Posts
    3,760
    Mentioned
    23 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SitePoint Evangelist
    Join Date
    Aug 2010
    Posts
    503
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    PHP Code:
        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. #5
    Non-Member bronze trophy
    Join Date
    Nov 2009
    Location
    Keene, NH
    Posts
    3,760
    Mentioned
    23 Post(s)
    Tagged
    0 Thread(s)
    My only concern would be the nonsensical markup.

    Code:
    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:

    Code:
    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:

    Code:
    <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. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by coxdabd View Post
    ...anybody see anything which could work better done differently, any advice is appreciated.
    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
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist
    Join Date
    Aug 2010
    Posts
    503
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •