Hi every one

I need to retrieve categories and subcategories by category type this my data

category_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
category_parent INT(11) UNSIGNED NOT NULL DEFAULT '0',
category_name VARCHAR(255) NOT NULL DEFAULT '',
category_type VARCHAR(30) NOT NULL DEFAULT 'news',



like

News++++++
    category1
    category2
        subcategory1

Video++++++
    category3
    category4
    category5

something like that

thank you

Recommended Answers

All 8 Replies

Member Avatar for diafol

Show your code / SQL so far

Use an order by query to sort your query result. Example: select * from your_table order by category_type,category_parent,category_id
Then using a variable to store the data for comparism:

$results = array();
foreach($your_result as $row){
    $results[$row['category_type']][$row['category_parent']][] = $row['category_id'];
}

echo"<pre>";
foreach($results as $category_type => $parents){
    echo "\n".$category_type;
    if(is_array($parents) && !empty($parents)){
        foreach($parents as $parent => $children){
            echo "\n\t".$parent;
            if(is_array($children) && !empty($children)){
                foreach($children as $child){
                    echo "\n\t\t".$child;
                }
            }
        }
    }
}
echo"</pre>";

Ips thank you it worked great but the sub categories moved under all categories.

the one am using is this

function load_categories(){
    $categories = array();
    $result2 = dbquery("SELECT * FROM ".DB_PREFIX."content_categories ORDER BY type");
    while($data = dbarray($result2)){
        $categories[] = $data;
    }
    return $categories;
    @mysql_free_result($result);
}

$categories = load_categories();
$return = array();
foreach($categories as $id => $data){
    $class = bgclass();
    if($data['parent'] == $catID){
        $add  = "<a href='".FUSION_SELF.$aidlink."&page=categories&subpage=add&amp;action=edit&id=".$data['cid']."'>Edit</a> | ";
        $edit = "<a href='".FUSION_SELF.$aidlink."&page=categories&subpage=add&amp;action=delete&id=".$data['cid']."' onclick=\"return confirm('Delete');\">Delete</a>";
        $return["x".$data["cid"]] = "<tr class='$class'><td>".$indent." ".$data["name"]."<span style='float:right;'>$add $edit</span></td></tr>";
        $return = array_merge($return, admin_catlist($data["cid"], $indent."&#8212;"));
    }
}
return $return;

Did you mean the data do not having category_id? If so, just need to filter it out

foreach($your_result as $row){
    if(isset($row['category_id']) && !empty($row['category_id'])){
        $results[$row['category_type']][$row['category_parent']][] = $row['category_id'];
    }elseif(isset($row['category_parent']) && !empty($row['category_parent'])){
        $results[$row['category_type']][$row['category_parent']][] = "";
    }
}

my one works like this

------------------------------------------------
    category1               - news type
    category2               - news type
        subcategory1        - news type
    category3               - other type
    category4               - other type
        subcategor2         - other type
            subsubcategory1 - other type
    category5               - news type

but what i want is like this
------------------------------------------------
News type
    category1
    category2
        subcategory1
    category5
other type
    category3
    category4
        subcategor2
            subsubcategory1

First of all, to the one that rate down my post previously: Can you please pointed out which part my codes having issue?

@afaaro, as you seen your code for $categories[] = $data; this only store the $data into $categories where the key of array $categories will still be 0,1,2,3,4..., and the result will be on how the database is order. Then you use foreach($categories as $id => $data), this is wrong as the $id here is nothing but the indexing of 0,1,2,3,4....

But if you see my 2nd post, I collecting the ids with specific keys. And using the looping in my 1st post, I can get total exact result.

Example code

<?php
$your_result = array(
    array('category_type' => "news",'category_parent' => "category 1",'category_id' => "category id 1"),
    array('category_type' => "news",'category_parent' => "category 2"),
    array('category_type' => "news",'category_parent' => "category 3",'category_id' => "category id 3"),
    array('category_type' => "news",'category_parent' => "category 1",'category_id' => "category id 4"),
    array('category_type' => "events",'category_parent' => "category 1",'category_id' => "category id 1"),
    array('category_type' => "events",'category_parent' => "category 2"),
    array('category_type' => "events",'category_parent' => "category 3",'category_id' => "category id 3")
);
$results = array();
foreach($your_result as $row){
    if(isset($row['category_id']) && !empty($row['category_id'])){
        $results[$row['category_type']][$row['category_parent']][] = $row['category_id'];
    }elseif(isset($row['category_parent']) && !empty($row['category_parent'])){
        $results[$row['category_type']][$row['category_parent']][] = "";
    }
}
echo"<pre>";
foreach($results as $category_type => $parents){
    echo "\n".$category_type;
    if(is_array($parents) && !empty($parents)){
        foreach($parents as $parent => $children){
            echo "\n\t".$parent;
            if(is_array($children) && !empty($children)){
                foreach($children as $child){
                    if(trim($child) != ""){
                        echo "\n\t\t".$child;
                    }
                }
            }
        }
    }
}
echo"</pre>";
?>

Results will be as I attached. result.jpg

its nice but when i do this way from database its only giving me ids

// $your_result = array(
//     array('category_type' => "news",'category_parent' => "category 1",'category_id' => "category id 1"),
//     array('category_type' => "news",'category_parent' => "category 2"),
//     array('category_type' => "events",'category_parent' => "category 1",'category_id' => "category id 1"),
//     array('category_type' => "news",'category_parent' => "category 3",'category_id' => "category id 3"),
//     array('category_type' => "news",'category_parent' => "category 1",'category_id' => "category id 4"),
//     array('category_type' => "events",'category_parent' => "category 2"),
//     array('category_type' => "events",'category_parent' => "category 3",'category_id' => "category id 3")
// );
$your_result = array();
$result = dbquery("SELECT * FROM ".CONTENT_CATEGORIES);
while($data = dbarray($result)){
    $your_result[] = $data;
}
print_p($your_result);
$results = array();
foreach($your_result as $row){
    if(isset($row['cid']) && !empty($row['cid'])){
        $results[$row['type']][$row['parent']][] = $row['cid'];
    }elseif(isset($row['parent']) && !empty($row['parent'])){
        $results[$row['type']][$row['parent']][] = "";
    }
}
echo"<pre>";
foreach($results as $category_type => $parents){
    echo "\n".$category_type;
    if(is_array($parents) && !empty($parents)){
        foreach($parents as $parent => $children){
            echo "\n\t".$parent;
            if(is_array($children) && !empty($children)){
                foreach($children as $child){
                    if(trim($child) != ""){
                        echo "\n\t\t".$child;
                    }
                }
            }
        }
    }
}
echo"</pre>";

Hello Ips again, I really appreciate your answer

Now its working fine with this

function test_categories(){
    $array = array();
    $result = dbquery("SELECT * FROM ".CONTENT_CATEGORIES);
    while($data = dbarray($result)){
        $array[$data['type']]['parent'][] = $data;
    }
    return $array;
}

function admin_list($level = FALSE){
    global $class;
    echo "<table cellspacing='0' cellpadding='0' class='table table-responsive tbl-border center'>";
    foreach(test_categories() as $category_type => $parents){
        echo "<tr class='$class'><th>".$category_type."</th></tr>";
        if(is_array($parents) && !empty($parents)){
            foreach($parents as $parent => $children){
                if(is_array($children) && !empty($children)){
                    foreach($children as $child){
                        $indent = str_repeat("—", $level);
                        $class = bgclass();                     
                        echo "<tr class='$class'><td>".$indent="—".$child['name']."</td></tr>";
                    }
                }
            }
        }
    }
    echo "</table>";
}
admin_list();

but i need to show subcategories like this and how

Categories
— subcategories
—— subcategories

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.