如何在单个表中获取所有父名称及其ID

Question

I have the following table :

Now i need to fetch all category with its parent category

like for:

category_id 1 -> "category_id=1;category_name=Electronic";<br/>
category_id 2 -> "category_id=2;category_name=Media";<br/>
category_id 3 -> "category_id=3;category_name=Mobile-Charger,parent_name=mobile" parent_id=1;<br/>
category_id 4 -> "category_id=4;category_name=Mobile-Charger,parent_name=Mobile" parent_id=1 ;
// and in that case Mobile also has a parent Electronic Product

I Need To fetch all hierarchy relation for 9 category like associative array

like this type :

[{"category_id":"1","category_name":"Electronic Product","parent_id":"0","child":[{"category_id":"3","category_name":"Mobile","parent_id":"1","child":[{"category_id":"4","category_name":"Mobile-Charger","parent_id":"3","child":[]}]}]},{"category_id":"2","category_name":"Media","parent_id":"0","child":[{"category_id":"5","category_name":"media-child-1","parent_id":"2","child":[]},{"category_id":"6","category_name":"media-child-2","parent_id":"2","child":[{"category_id":"8","category_name":"media-child-6-child-1","parent_id":"6","child":[]},{"category_id":"9","category_name":"media-child-6-child-2","parent_id":"6","child":[]}]},{"category_id":"7","category_name":"media-child-3","parent_id":"2","child":[]}]}]

Answer 1

<?php

include 'config.php';
static $all_data =  array();
$all_data =  buildCategories($all_data, 0);
echo json_encode($all_data);

function buildCategories($all_data2, $parent) 
{

    $q = "Select * from category where parent_id = ".$parent;
    if($rs = getRSQuery($q))
    {
        $all_data2 = $rs;
                if($all_data2!='Not Found')
                {
        foreach($all_data2 as $index => $catDetails)
        {
            $all_data2[$index]['child'] = array();
            $all_data2[$index]['child'] =  buildCategories($all_data2[$index]['child'], $catDetails['category_id']) ;
        }
                }
    }
    else
        return array();
    return $all_data2;

}


function getRSQuery($query)
{

    $query_result = array();
    $result_set=  mysql_query($query);
    if(!$result_set)
    {
        die('Could not get data: ' . mysql_error());
    }
    $num_rows= mysql_num_rows($result_set);
    if($num_rows>0)
    {
      $index = 0;
      while(($row = mysql_fetch_array($result_set, MYSQL_ASSOC)) != FALSE)
        {
            foreach($row as $colName =>$val)
            {
                $query_result[$index][$colName] = $val;             
            }
            $index++;
        }
        return $query_result;       
    }
    else
        return "Not Found";
}



?>