Jump to content

Nested Categories


afaaro

Recommended Posts

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 by afaaro
Link to comment
Share on other sites

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 by afaaro
Link to comment
Share on other sites

 

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?

Link to comment
Share on other sites

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>";
    }
}
Link to comment
Share on other sites

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");
Link to comment
Share on other sites

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 ;
Link to comment
Share on other sites

  • 2 months later...
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.