how to get all parent name and its id in a single table

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

you should use mysql join query to get the desired result...

below query can be used..

USING MYSQL JOINS

select c1.category_id,c1.category_name,c2.category_name as parent_name
from category c1
left join category c2 on (c2.parent_id = c1.category_id)

OR

USING SUB-QUERY

SELECT c1.category_id,c1.category_name,
(select c2.category_name from category c2 where c2.parent_id = c1.category_id limit 1) as parent_name
from category c1

let me know if further help needed.

you can read more about mysql joins here : https://dev.mysql.com/doc/refman/5.0/en/join.html and mysql subqueries here : https://dev.mysql.com/doc/refman/5.0/en/subqueries.html

Answer 2

Try this one.

 SELECT * FROM category t1 JOIN other_table t2 ON t1.parent_id = t2.parent_id

Answer 3

You need to first get base category (who has no parent) and the categories child categories.

UNION their results and we get the desired result.

SELECT category_id, category_name, '' as parent 
FROM `categories` 
WHERE `parent_d` = 0 
UNION 
SELECT c.category_id, c.category_name, P.category_name as parent 
FROM `categories` c 
INNER JOIN categories P ON c.`parent_d` = P.category_id 
WHERE c.`parent_d` != 0

Here is the demo

Answer 4

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



?>