Nested Categories

afaaro · February 15, 2014

Hello Guys

I need to retrieve posts from category and it's parents and parent's child how can i do it. i tried this one but no luck

$query = dbquery("SELECT * FROM ".CATEGORY);
while ($row = dbarray($query) ){        
	$ref[$row['category_id']] = array('category_name' => $row['category_name'],'category_parent' => $row['category_parent']);
}

function generate_child($parent){        
	$has_childs = false;        

	global $ref;        

	foreach($ref as $key => $value){                
		if ($value['category_parent'] == $parent){ 
			if ($has_childs === false){                            
				$has_childs = true;                    
			}                        
			generate_child($key);                        
		}        
	}        
	if ($has_childs === true);
}

$cdata = generate_child(0);
if(dbrows($result = dbquery("SELECT p.*, c.* FROM ".POST." p LEFT JOIN ".CATEGORY." c ON p.post_category=c.category_id WHERE c.category_id='".$cdata['category_id']."' ORDER BY p.post_created DESC LIMIT 10"))){
    echo "<ul>";
    while ($data = dbarray($result)) {
        if($config['seourl'] == 1){ $post = $data['category_url']."/".$data['post_url']; } else { $post = INFUSIONS."media/media.php?cid={$data['category_url']}&id={$data['post_url']}"; }
        
        if($data['post_embed']){
            $thumbnail = image_provider($data['post_embed']);
        }elseif($data['post_image']){
            $thumbnail = INFUSIONS."media/uploads/posts/".$data['post_image'];
        }else{
            $thumbnail = INFUSIONS."media/images/nophoto235.png";
        }

        echo "<li class='".$data['post_format']."'>";
        echo "<a href='$post'>";
        echo "<img src='$thumbnail' width='70' height='50'>";
        echo "<h3>".trimlink($data['post_name'], 70)."</h3>";
        echo "</li></a>";
    }
    echo "</ul>";
}

Edited February 15, 2014 by afaaro

jazzman1 · February 15, 2014

Does your category table look like this?

afaaro · February 15, 2014

mine is category_id, category_parent, category_name.. If i use this it only returns parents of the category but it doesn't return any

child's parents -> childs->childs

echo posts_by_CATID(0);
function posts_by_CATID($id){
    if($result = dbquery("SELECT DISTINCT p.*, cat.category_id, cat.category_parent, cat.category_url, parent.category_name
                          FROM ".CATEGORY." cat 
                          LEFT JOIN ".CATEGORY." parent ON cat.category_parent=parent.category_id
                          INNER JOIN ".POST." p ON cat.category_id=p.post_category 
                          WHERE (cat.category_id=$id OR parent.category_id=$id) ORDER BY p.post_created DESC")){
                                
        while($data = dbarray($result)){
            echo "<a href='media.php?id={$data['post_url']}'>".$data['post_name']."</a><br />";
        }
    }
}

Edited February 15, 2014 by afaaro

afaaro · February 15, 2014

mine is category_id, category_parent, category_name.. If i use this it only returns parents of the category but it doesn't return any

child's parents -> childs->childs

echo posts_by_CATID(0);
function posts_by_CATID($id){
    if($result = dbquery("SELECT DISTINCT p.*, cat.category_id, cat.category_parent, cat.category_url, parent.category_name
                          FROM ".CATEGORY." cat 
                          LEFT JOIN ".CATEGORY." parent ON cat.category_parent=parent.category_id
                          INNER JOIN ".POST." p ON cat.category_id=p.post_category 
                          WHERE (cat.category_id=$id OR parent.category_id=$id) ORDER BY p.post_created DESC")){
                                
        while($data = dbarray($result)){
            echo "<a href='media.php?id={$data['post_url']}'>".$data['post_name']."</a><br />";
        }
    }
}

I achieved this creating child->child->child->child->child->child like this.

function posts_by_CATID($id){
    if($result = dbquery("SELECT DISTINCT p.*, c.category_id,c.category_parent,c.category_name,c1.category_name,c2.category_name,c3.category_name,c4.category_name,c5.category_name,c6.category_name,c7.category_name
                          FROM ".CATEGORY." c 
                          LEFT JOIN ".CATEGORY." c1 ON  c.category_parent=c1.category_id
                          LEFT JOIN ".CATEGORY." c2 ON c1.category_parent=c2.category_id
                          LEFT JOIN ".CATEGORY." c3 ON c2.category_parent=c3.category_id
                          LEFT JOIN ".CATEGORY." c4 ON c3.category_parent=c4.category_id
                          LEFT JOIN ".CATEGORY." c5 ON c4.category_parent=c5.category_id
                          LEFT JOIN ".CATEGORY." c6 ON c5.category_parent=c6.category_id
                          LEFT JOIN ".CATEGORY." c7 ON c6.category_parent=c7.category_id
                          INNER JOIN ".POST." p ON c.category_id=p.post_category
                          WHERE (c.category_id=$id OR c1.category_parent=$id OR c2.category_parent=$id OR c3.category_parent=$id OR c4.category_parent=$id OR c5.category_parent=$id OR c6.category_parent=$id OR c7.category_parent=$id) ORDER BY p.post_created DESC")){
                                
        while($data = dbarray($result)){
            echo "<a href='media.php?id={$data['post_url']}'>".$data['post_name']."</a><br />";
        }
    }
}

is there better way?

Barand · February 15, 2014

It looks to me like you have your LEFT JOINS the wrong way round. You need to join the id of the category to the parent_id of the child category

afaaro · February 15, 2014

Hello Barand,

you mean this way

function posts_by_CATID($id, $style='', $count='10'){
    if($result = dbquery("SELECT DISTINCT p.*, c.category_id,c.category_parent,c.category_name,c1.category_name,c2.category_name,c3.category_name,c4.category_name,c5.category_name,c6.category_name,c7.category_name,c8.category_name,c9.category_name
                          FROM ".CATEGORY." c 
                          LEFT JOIN ".CATEGORY." c1 ON c1.category_id=c.category_parent
                          LEFT JOIN ".CATEGORY." c2 ON c2.category_id=c1.category_parent
                          LEFT JOIN ".CATEGORY." c3 ON c3.category_id=c2.category_parent
                          LEFT JOIN ".CATEGORY." c4 ON c4.category_id=c3.category_parent
                          LEFT JOIN ".CATEGORY." c5 ON c5.category_id=c4.category_parent
                          LEFT JOIN ".CATEGORY." c6 ON c6.category_id=c5.category_parent
                          LEFT JOIN ".CATEGORY." c7 ON c7.category_id=c6.category_parent
                          LEFT JOIN ".CATEGORY." c8 ON c8.category_id=c7.category_parent
                          LEFT JOIN ".CATEGORY." c9 ON c9.category_id=c8.category_parent
                          INNER JOIN ".POST." p ON c.category_id=p.post_category
                          WHERE (c.category_parent=0 OR c.category_parent=$id OR c1.category_parent=$id OR c2.category_parent=$id OR c3.category_parent=$id OR c4.category_parent=$id OR c5.category_parent=$id OR c6.category_parent=$id OR c7.category_parent=$id OR c8.category_parent=$id OR c9.category_parent=$id) 
                          ORDER BY p.post_created DESC LIMIT ".$count)){
        echo "<ul id='$style-$id'>";         
        while($data = dbarray($result)){
            if($data['post_embed']){ $thumbnail = image_provider($data['post_embed']); }elseif($data['post_image']){ $thumbnail = INFUSIONS."media/uploads/posts/".$data['post_image']; }else{ $thumbnail = INFUSIONS."media/images/nophoto100.png"; }
            echo "<li class='".$data['post_id']."'><a href='media.php?cid=".$data['category_id']."&id=".$data['post_url']."'>";
            echo "<img src='$thumbnail' width='70' height='50'>";
            echo "<h3>".trimlink($data['post_name'], 70)."</h3>";
            echo "</a></li>";
        }
        echo "</ul>";
    }
}

Barand · February 15, 2014

Disregard previous comment. I'm still trying to get my mind around what you want

Edited February 15, 2014 by Barand

afaaro · February 15, 2014

Sorting posts by category and i have 100 categories includes category->parent->child->child so on

now this one is working echoing like [listing all posts from the all categories and it's children]

 echo posts_by_CATID(0, "grid", "10");

but when i echo by child category doesn't display anything like this

 echo posts_by_CATID(21, "grid", "10");

doddsey_65 · February 15, 2014

If you have categories that have multiple siblings you should have a look at modified preorder tree traversals

afaaro · February 15, 2014

This is my table

CREATE TABLE IF NOT EXISTS `fusionhka19_mediacategory` (
  `category_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `category_name` varchar(255) NOT NULL,
  `category_parent` int(11) NOT NULL DEFAULT '0',
  `category_url` varchar(100) NOT NULL,
  `category_image` varchar(255) DEFAULT NULL,
  `category_body` text NOT NULL,
  `category_theme` text NOT NULL,
  `category_views` int(11) NOT NULL DEFAULT '0',
  `category_enabled` int(1) NOT NULL DEFAULT '1',
  `category_access` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`category_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=85 ;

afaaro · May 8, 2014

Is there any better way?

Sign In

Nested Categories

Recommended Posts

afaaro

Link to comment

Share on other sites

jazzman1

Link to comment

Share on other sites

afaaro

Link to comment

Share on other sites

afaaro

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

afaaro

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

afaaro

Link to comment

Share on other sites

doddsey_65

Link to comment

Share on other sites

afaaro

Link to comment

Share on other sites

afaaro

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information