Nested Categories

[afaaro]

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]

Does your category table look like this?

[afaaro]

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]

 

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]

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]

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]

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

Edited February 15, 2014 by Barand

[afaaro]

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]

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

[afaaro]

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]

Is there any better way?