PHP Tree Structure for Categories and Sub Categories Without Looping a Query

How to get categories tree without looping queries?

If it's always just up to 2 levels deep you may be able to use the query below. In php you could check to see if the bg_category on the current row is the same as on the previous row to either show or not show the column, same with child1 since they can have multiple child2s.

I added some additional categories and subcategories, out of order, for illustration.

I used 3 underscores indicating 1 level deep and 6 underscores indicating 2 levels deep, but you can actually put some of the html tags into the sql to have the query generate for you what you would put at each level.

This should work despite the order of the the rows in the database -- determined by the id field -- but like I said it will only work 2 levels deep.

Although it looks pretty bad, you could set up some views to simplify the query you would actually run. Of course, this still assumes you're only going 2 levels deep. If you have subcategories upon subcategories of subcategories, sql might not be the way to go.

Fiddle:
http://sqlfiddle.com/#!2/630c6/18/0

select w.bg_category, w.child, x.child as child2

from(

select x.bg_category, y.bg_category as child

from(

select id, bg_category, parent
from tbl
where parent = 0
union all
select id, concat('___',bg_category), parent
from tbl
where id in (select parent id from tbl)
and parent <> 0
union all
select id, concat('______',bg_category), parent
from tbl
where id not in (select parent from tbl)
and parent not in (select id from tbl where parent = 0)
union all
select id, concat('___',bg_category), parent
from tbl
where id not in (select parent from tbl)
and parent in (select id from tbl where parent = 0)

  ) x

join

(

  select id, bg_category, parent
from tbl
where parent = 0
union all
select id, concat('___',bg_category), parent
from tbl
where id in (select parent id from tbl)
and parent <> 0
union all
select id, concat('______',bg_category), parent
from tbl
where id not in (select parent from tbl)
and parent not in (select id from tbl where parent = 0)
union all
select id, concat('___',bg_category), parent
from tbl
where id not in (select parent from tbl)
and parent in (select id from tbl where parent = 0)

  ) y on x.id = y.parent

  ) w

left join

(

select x.bg_category, y.bg_category as child

from(

select id, bg_category, parent
from tbl
where parent = 0
union all
select id, concat('___',bg_category), parent
from tbl
where id in (select parent id from tbl)
and parent <> 0
union all
select id, concat('______',bg_category), parent
from tbl
where id not in (select parent from tbl)
and parent not in (select id from tbl where parent = 0)
union all
select id, concat('___',bg_category), parent
from tbl
where id not in (select parent from tbl)
and parent in (select id from tbl where parent = 0)

  ) x

join

(

  select id, bg_category, parent
from tbl
where parent = 0
union all
select id, concat('___',bg_category), parent
from tbl
where id in (select parent id from tbl)
and parent <> 0
union all
select id, concat('______',bg_category), parent
from tbl
where id not in (select parent from tbl)
and parent not in (select id from tbl where parent = 0)
union all
select id, concat('___',bg_category), parent
from tbl
where id not in (select parent from tbl)
and parent in (select id from tbl where parent = 0)

  ) y on x.id = y.parent

  ) x on w.child = x.bg_category

where substr(w.bg_category,1,1) <> '_'

php category, sub category tree

$qry = $conn->prepare('
  SELECT   a.pid, a.parent_name, b.category_name
  FROM     parent_categories a
      JOIN child_categories  b ON a.pid = b.lpid
  ORDER BY a.pid
');

if ($qry->execute()) {
  echo '<ul>';

  $row = $qry->fetch();
  while ($row) {
    $current_pid = $row['pid'];

    echo '<li>', htmlentities($row['parent_name']), '<ul>';
    do {
      echo '<li>', htmlentities($row['category_name']), '</li>';
    } while ($row = $qry->fetch() and $row['pid'] == $current_pid);
    echo '</ul></li>';
  }

  echo '</ul>';
}

Showing sub-categories and number of items under the main category

Your children relation will return a collection and not a single model, because of this you have to loop through the children aswell:

@foreach($allCategories as $cats)
    <li><a href="#">{{ $cats->title }}</a>
        <ul class="sub-category">
            @if($cats->children)
                @foreach($cats->children as $child)
                    <li><a href="#"> {{ $child->title }} </a></li>
                @endforeach
            @endif
        </ul>
    </li>
@endforeach

PHP Recursive Function to Retrieve List of Sub Categories

You seem to want a simple one-dimensional array (eg. a list) with the sub-category-ids, what you're currently building is a tree (nested arrays). This should to the trick:

public function getList($parentId)
{
    $retVal = array($parentId);

    $query = "SELECT * FROm `bs_category` WHERE `parent_id` = ".$parentId;
    $result = $this->db->query($query);

    foreach($result->rows as $row)
    {
        $retVal = array_merge($retVal, $this->getList($row["category_id"]));
    }

    return $retVal;
}

---

Related Topics