need help: list articles by multi categories and subcategories

[afaaro]

Hello every one

 

I need help help with tihs code, it works but is there an easy way to generate articles by categories and subcategories

if($result = GetList("SELECT p.*,f.*,c1.*
    FROM ".DB_PREFIX."mediaCategory c1 
    LEFT JOIN ".DB_PREFIX."mediaCategory c2 ON c2.category_id=c1.category_parent
    LEFT JOIN ".DB_PREFIX."mediaCategory c3 ON c3.category_id=c2.category_parent
    LEFT JOIN ".DB_PREFIX."mediaCategory c4 ON c4.category_id=c3.category_parent
    LEFT JOIN ".DB_PREFIX."mediaCategory c5 ON c5.category_id=c4.category_parent
    LEFT JOIN ".DB_PREFIX."mediaCategory c6 ON c6.category_id=c5.category_parent
    LEFT JOIN ".DB_PREFIX."mediaPost p ON c1.category_id=p.post_category
    WHERE (c1.category_id=1 OR c1.category_parent=1 OR c2.category_parent=1 OR c3.category_parent=1 OR c4.category_parent=1 OR c5.category_parent=1 OR c6.category_parent=1)
    ORDER BY p.post_created LIMIT 10")){
    echo "<ul class='grid'>"; 
    foreach ($result as $row) {
        echo "<li class='ID-".$row['post_id']."'><span><h3>".$row['post_name'])."</h3></span></li>";
    }
    echo "</ul>";
} 

[Jacques1]

What's your question? Do you want the tree of all categories and articles?

[afaaro]

Yes

[afaaro]

I have tried this one but its only displaying the top category not subcategory and child category

 

        function get_subcategories($catid=0){

            $result = GetList("SELECT * FROM ".DB_PREFIX."mediaCategory WHERE category_parent=$catid");

            $children = array();        

            foreach($result as $subcat){

             $current_id = $subcat['category_id'];

             $has_sub = GetRow("SELECT * FROM ".DB_PREFIX."mediaCategory WHERE category_parent=$current_id");

                //echo $subcat['category_name'].'-'.has_subcategories($subcat['category_id'])."<br>";                        

                if($has_sub > 0) {

                    $children[] = get_categories($catid);

                } else {

                    $children[] = $subcat['category_id'];

                    //$children[$current_id] = array_merge($children, get_categories($current_id));

                }

            }


            return $children;

        }


        //echo print_p(get_subcategories(0));

        $cat = get_subcategories(1);

        print_p($cat);

        foreach($cat as $row){

     if($result = GetList("SELECT p.*,c.* FROM ".DB_PREFIX."mediaPost p JOIN ".DB_PREFIX."mediaCategory c ON p.post_category=c.category_id WHERE c.category_id='".$row."' ORDER BY p.post_created DESC")){

     foreach($result as $data){

     echo $data['post_name']." - ".$data['category_id']."<BR>";

     }

     }

        }

[afaaro]

Anyone?