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

i have following table :

now need fetch category 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 ; // , in case mobile has parent electronic product 

i need fetch hierarchy relation 9 category associative array

like 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":[]}]}]

you should use mysql join query desired result...

below query can used..

using mysql joins

select c1.category_id,c1.category_name,c2.category_name parent_name 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 category c2 c2.parent_id = c1.category_id limit 1) parent_name category c1 

let me know if further needed.

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