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>";
} 

[diafol]

Not sure how you're storing this info...

This is a possible schema...

categories: cat_id | cat_name

subcategories: subcat_id | subcat_name | cat_id

article: art_id | title | description | ... | subcat_id

SELECT a.* FROM articles AS a INNER JOIN subcategories AS s ON a.subcat_id = s.subcat_id INNER JOIN categories AS c ON s.cat_id = c.cat_id ORDER BY c.cat_name, s.subcat_name, a.title 

That would group all articles by category, subcategory and listed alphabetically by title.

You could also list by date posted etc.

You could also include the c.cat_name and s.subcat_name if you wanted to create headings for the grouped articles.

SELECT a.*, c.cat_name, s.subcat_name, s.subcat_id FROM articles AS a INNER JOIN subcategories AS s ON a.subcat_id = s.subcat_id INNER JOIN categories AS c ON s.cat_id = c.cat_id ORDER BY c.cat_name, s.subcat_name, a.title

So, then when the subcat_id changes in the loop - echo out a <h3> title cat-subcat or similar.

[afaaro]

my category table has

category_id
category_name
category_parent

and post table has

post_id
post_category
post_name

[diafol]

Oh no, not the old self-referencing categories again. Sorry can't help you. Anybody else?

[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>";
            }
        }
    }

[jkon]

I have seen a number of questions of that type and I am thinking of writing a tutorial here about the Indexed List Model in OOP PHP with Assigners and Data Workers (but to be honest I don't see much interest in the OOP approach of this issue) . For now I will explain you how it works in basis without all the conveniences an OOP Model would give us.

Also even in this small example I can't find any reason why the id of the category table is named category_id , for now on in this example will be simple ID. Also from the partial schema you gave I understood that any branch can have also subcategories and articles at the same time, normally this is isn't the way but lets suppose that in you case it is.

<?php
session_start();
// Change these with your own database,user and password
$database = "test_db";
$dbUser = "testdbuser";
$dbPassword = "testdbpassword";
$db = new PDO("mysql:dbname=".$database.";host=localhost",$dbUser,$dbPassword);

//There is no reason to get the categories tree each time, so we will use visit scope caching through session
if(isset($_SESSION["CATS_ARRAY"]) && isset($_SESSION["CATS_CHILDRENS_ARRAY"]))
{
  $catsArray = $_SESSION["CATS_ARRAY"];
  $catsChildrensArray= $_SESSION["CATS_CHILDRENS_ARRAY"];
}
else
{
  $catsArray = array();
  $catsChildrensArray = array(); 

  // Lets assume that we have a table named categories that has ID,PARENT_ID,TITLE
  // and that the top categories have PARENT_ID 0 
  $statement = $db->prepare("SELECT * FROM categories");
  $statement->execute(array());
  $r = $statement->fetchAll(PDO::FETCH_ASSOC);  

  // now $r will have all the categories rows in a multidimentional array
  foreach($r as $row)
  {
      $catsArray[ $row["ID"] ] = $row; 
      $catsChildrensArray[ $row["PARENT_ID"] ][] = $row["ID"];
  }
  // Now if a category has childrens the id each one will be in the $catsChildrensArray 
  // with index of it. 
  $_SESSION["CATS_ARRAY"] = $catsArray;
  $_SESSION["CATS_CHILDRENS_ARRAY"] = $catsChildrensArray;
}

// Let's suppose that we have a table named posts with ID,CATEGORY_ID and TITLE 
// what we want to do is to get all the tree down of categories and find the articles or childrens 
// of each category. We will use different css class if it is an post or a subcategory 

$re = "<ul>".recursiveCategoryContent(0,$db,$catsArray,$catsChildrensArray)."</ul>";
echo $re; 

function recursiveCategoryContent($categoryId,PDO $db,$catsArray,$catsChildrensArray)
{
  // $re will be the returned string 
  $re = "";
  $statement = $db->prepare("SELECT * FROM posts WHERE CATEGORY_ID = ?");
  $statement->execute(array($categoryId));
  $r = $statement->fetchAll(PDO::FETCH_ASSOC);   
  // Now $r will have all posts of this category (if any) in a multidimentional array

  if( count($r) > 0 )
  {
    foreach($r as $row)
    {
      $re .= "<li class='post'>".$row["TITLE"]."</li>";
    }
  }

  if(isset($catsChildrensArray[$categoryId]))
  {
    foreach($catsChildrensArray[$categoryId] as $catId)
    {
      $re .= "<li class='subcategory'>".$catsArray[$catId]["TITLE"]."</li>";
      $re .= "<ul>".recursiveCategoryContent($catId,$db,$catsArray,$catsChildrensArray)."</ul>"; 
    }
  }

  return $re; 
}

?>

[afaaro]

Hi jkon thank you so much it worked but one thing instead list by category how can i accomplish that.

this is the code i analysed

function get_contents($categoryId,$catsArray,$catsChildrensArray){
    if($result = GetList("SELECT * FROM ".DB_PREFIX."mediaCategory")){
        $catsArray = array();
        $catsChildrensArray = array();
        foreach($result as $row){
            $catsArray[ $row["category_id"] ] = $row; 
            $catsChildrensArray[ $row["category_parent"] ][] = $row["category_id"];     
        }
    }        
    $html = "";
    if($result = GetList("SELECT p.*,c.* FROM ".DB_PREFIX."mediaCategory c LEFT JOIN ".DB_PREFIX."mediaPost p ON c.category_id=p.post_category WHERE c.category_id='".$categoryId."' ORDER BY p.post_created DESC")){
        foreach($result as $row){
            $html .= "<li class='post'>".$row["post_name"]."</li>";
        }

        if(isset($catsChildrensArray[$categoryId])){
            foreach($catsChildrensArray[$categoryId] as $catId){
                $html .= "<li class='subcategory'><h1>".$catsArray[$catId]["category_name"]."</h1>";
                $html .= "<ul>".get_contents($catId,$catsArray,$catsChildrensArray)."</ul></li>"; 
            }
        }
        return $html;
    }
}

echo "<ul>".get_contents(39,$catsArray,$catsChildrensArray)."</ul>";

[jkon]

If the only thing different that you want to do is to display the tree down with subcategories and articles NOT from the top parent category but from one other (lets say the category that has ID 39) the only different that you should have in the code I posted is changing line 42 from:
$re = "<ul>".recursiveCategoryContent(0,$db,$catsArray,$catsChildrensArray)."</ul>";
to:
$re = "<ul>".recursiveCategoryContent(39,$db,$catsArray,$catsChildrensArray)."</ul>";

If you are not trying to do that , explain further what exactly are you trying to achieve.

[afaaro]

Hi jkon

I would like to achieve list articles by date with recursive category

    function get_contents($categoryId){
        if($result = GetList("SELECT * FROM ".DB_PREFIX."mediaCategory")){
            $catsArray = array();
            $catsChildrensArray = array();
            $html = "";
            foreach($result as $row){
                $catsArray[$row["category_id"]] = $row; 
                $catsChildrensArray[$row["category_parent"]][] = $row["category_id"];       
            }
            if(isset($catsChildrensArray[$categoryId])){
                foreach($catsChildrensArray[$categoryId] as $catId){
                    $html .= "<li class='subcategory'><h1>".$catsArray[$catId]["category_name"]."</h1>";
                    $html .= "<ul>".get_contents($catId)."</ul></li>";
                    if($result = GetList("SELECT p.*,c.* FROM ".DB_PREFIX."mediaCategory c LEFT JOIN ".DB_PREFIX."mediaPost p ON c.category_id=p.post_category WHERE c.category_id='".$catId."' ORDER BY p.post_created DESC LIMIT 10")){
                        foreach($result as $row){
                            $html .= "<li class='post'>".$row["post_name"]."</li>";
                        }                   
                    }
                }
            }
            return $html;
        }        

    }
    echo "<ul>".get_contents(39)."</ul>";

[jkon]

If you want to list all articles of a category and its subcategories recursively order by date just add an order by clause in line 49 (of the code I gave you)
$statement = $db->prepare("SELECT * FROM posts WHERE CATEGORY_ID = ?");

And as we said you can have the articles of the categories recursively not only from the top parent category down but from what ever category you like.

The main point here is not to get a code and apply it without understanding what is doing. The core is first to understand and then choose how you are going to apply it.

[afaaro]

you are really amazing jkon but if i select 1 single category displays order by date, if i select category and it's subcategory it selects 

Category Name1
    post 1
    post 2
Category Name2
    post 1
    post 2    
which i don't want that way, i want this way:

post 1 - category 1 if posted by date
post 2 - subcategory 2 if posted by date
post 3 - category 2 if posted by date

something like that if there's any to modify

function recursiveCategoryContent($categoryId,$catsArray,$catsChildrensArray){
    if($result = GetList("SELECT * FROM ".DB_PREFIX."mediaCategory")){
        $catsArray = array();
        $catsChildrensArray = array();
        foreach($result as $row){
            $catsArray[ $row["category_id"] ] = $row; 
            $catsChildrensArray[ $row["category_parent"] ][] = $row["category_id"];     
        }
    }
    // $html will be the returned string 
    $html = "";
    $data = GetList("SELECT * FROM ".DB_PREFIX."mediaPost WHERE post_category=$categoryId ORDER BY post_created DESC LIMIT 10");
    // Now $data will have all posts of this category (if any) in a multidimentional array
    if( count($data) > 0 ){
        foreach($data as $row){
            $html .= "<li class='post'>".$row["post_name"]."-".showdate("shortdate", $row['post_created'])."</li>";
        }
    }
    if(isset($catsChildrensArray[$categoryId])){
        foreach($catsChildrensArray[$categoryId] as $catId){
            $html .= "<li class='subcategory'><h1>".$catsArray[$catId]["category_name"]."</h1></li>";
            $html .= "<ul>".recursiveCategoryContent($catId,$catsArray,$catsChildrensArray)."</ul>"; 
        }
    }
    return $html; 
}
echo "<ul>".recursiveCategoryContent(1,$catsArray,$catsChildrensArray)."</ul>";

[jkon]

Final code (It would be easier for you as well if you explained from the first post what you are trying to do). The non OOP version of the Indexed List Model that my first code had , can do this as well. All you need is to use it to generate the IN clause. Here is the code how to do it (Once more I will point that you need to understand it before using it)

<?php
session_start();
// Change these with your own database,user and password
$database = "test_db";
$dbUser = "testdbuser";
$dbPassword = "testdbpassword";
$db = new PDO("mysql:dbname=".$database.";host=localhost",$dbUser,$dbPassword);

//There is no reason to get the categories tree each time, so we will use visit scope caching through session
if(isset($_SESSION["CATS_ARRAY"]) && isset($_SESSION["CATS_CHILDRENS_ARRAY"]))
{
  $catsArray = $_SESSION["CATS_ARRAY"];
  $catsChildrensArray= $_SESSION["CATS_CHILDRENS_ARRAY"];
}
else
{
  $catsArray = array();
  $catsChildrensArray = array();

  // Lets assume that we have a table named categories that has ID,PARENT_ID,TITLE
  // and that the top categories have PARENT_ID 0
  $statement = $db->prepare("SELECT * FROM categories");
  $statement->execute(array());
  $r = $statement->fetchAll(PDO::FETCH_ASSOC);

  // now $r will have all the categories rows in a multidimentional array
  foreach($r as $row)
  {
    $catsArray[ $row["ID"] ] = $row;
    $catsChildrensArray[ $row["PARENT_ID"] ][] = $row["ID"];
  }
  // Now if a category has childrens the id each one will be in the $catsChildrensArray
  // with index of it.
  $_SESSION["CATS_ARRAY"] = $catsArray;
  $_SESSION["CATS_CHILDRENS_ARRAY"] = $catsChildrensArray;
}

// Let's suppose that we have a table named posts with ID,CATEGORY_ID , TITLE and CREATION_TIMESTAMP
// what we want to do is to get this time is to get all the posts of a gatecory and its subcategories
// ordered by CREATION_TIMESTAMP DESC

// Here we get the category id we want and its subcategories ids in a comma seperated String
// in this example with 0 we get all the tree down BUT YOU CAN ENTER WHAT EVER category id you like
$catString = getSubcategories(0,$catsArray,$catsChildrensArray);

if($catString != "")
{
  $statement = $db->prepare("SELECT * FROM posts WHERE CATEGORY_ID IN (".$catString.") ORDER BY CREATION_TIMESTAMP DESC");
  $statement->execute(array());
  $r = $statement->fetchAll(PDO::FETCH_ASSOC);
  if(count($r) > 0)
  {
    $re = "";
    $re .= "<table>";
    foreach($r as $row)
    {
      $re .= "<tr><td>".$row["TITLE"]."</td>";
      $re .= "<td>".categoryPath($row["CATEGORY_ID"], $catsArray)."</td></tr>";
    }
    $re .= "</table>";
    echo $re; 
  }
}

// Returns a String containing all the categories ids from the one that is given
// to tree down seperated by comma
function getSubcategories($id,$catsArray,$catsChildrensArray)
{
  $re =  isset($catsArray[$id]) ? $id : "";
  if(isset($catsChildrensArray[$id]))
  {
    foreach($catsChildrensArray[$id] as $catId)
    {
      $re .= $re == "" ? "" : ",";
      $re .= getSubcategories($catId, $catsArray, $catsChildrensArray);
    }
  }
  return $re;
}

// Returns the category path in a readable way
function categoryPath($id,$catsArray)
{
  $re = $catsArray[$id]["TITLE"];
  if($catsArray[$id]["PARENT_ID"] != 0)
  {
    $re = categoryPath($catsArray[$id]["PARENT_ID"], $catsArray).">".$re;
  }
  return $re;
}

?>

[afaaro]

WooooW jkon I can't believe it, every thing is working fine as expected. thank you so much

I was looking this for long time, really thank you