Adjecency List Category Count

<?php
/*
CREATE DATABASE IF NOT EXISTS test;
USE test;

DROP TABLE IF EXISTS items;
CREATE TABLE items(
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama VARCHAR(50),
    id_category INT
);

INSERT INTO items(nama,id_category)
VALUES
('innova',5),
('honda beat',3),
('lancer evo',4),
('honda vario',3),
('nmax',3),
('truck elf',6),
('truck gandeng',6);

SELECT * FROM items;
+----+---------------+-------------+
| id | nama          | id_category |
+----+---------------+-------------+
|  1 | innova        |           5 |
|  2 | honda beat    |           3 |
|  3 | lancer evo    |           4 |
|  4 | honda vario   |           3 |
|  5 | nmax          |           3 |
|  6 | truck elf     |           6 |
|  7 | truck gandeng |           6 |
+----+---------------+-------------+
7 rows in set (0.00 sec)

DROP TABLE IF EXISTS category;
CREATE TABLE category(
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama VARCHAR(50),
    parent INT
);

INSERT INTO category(nama,parent)
VALUES
('kendaraan',null),
('mobil',1),
('motor',1),
('sedan',2),
('suv',2),
('truck',2);

SELECT * FROM category;
+----+-----------+--------+
| id | nama      | parent |
+----+-----------+--------+
|  1 | kendaraan |   NULL |
|  2 | mobil     |      1 |
|  3 | motor     |      1 |
|  4 | sedan     |      2 |
|  5 | suv       |      2 |
|  6 | truck     |      2 |
+----+-----------+--------+
6 rows in set (0.00 sec)
*/
$dbhost='localhost';
$dbuser='root';
$dbpass='';
$dbname='test';
$db=new mysqli($dbhost,$dbuser,$dbpass,$dbname);
$sql="
    SELECT
        a.*,SUM(IF(b.id IS NULL,0,1)) AS jml
    FROM
        category a
        LEFT JOIN items b ON a.id=b.id_category
    GROUP BY a.id
    ORDER BY a.id";
$result=$db->query($sql);
$data=array();
$index = array();
while($row=$result->fetch_object()){
    $id = $row->id;
    $parent_id = $row->parent===null ? null : $row->parent;
    $data[$id] = $row;
    $index[$parent_id][] = $id;
}
function bottom_up($parent_id)
{
    global $data, $index;
    $parent_id = $parent_id===null? null : $parent_id;
    if (isset($index[$parent_id])) {
        foreach ($index[$parent_id] as $id) {
            bottom_up($id);
            if($parent_id!==null) $data[$parent_id]->jml+=$data[$id]->jml;
        }
    }
}
bottom_up(NULL);
?>
<!doctype html>
<html>
<head>
<title>Latihan 2017-02-09</title>
</head>
<body>
<table border='1'>
<tr><th>Nama</th><th>Jumlah</th></tr>
<?php
foreach($data as $d)
    echo "<tr><td>{$d->nama}</td><td>{$d->jml}</td></tr>";
?>
</table>
</body>
</html>