Packet and N-SubPacket Msql query

[shashib]

I am making packet type system when in my **Packet Table** , parent packet ( `id` which is primary key ) and its N -Sub Packet is under (`parent_id`) is stored , below is my table structure :**Packet_table**

    id | packet_name | parent_id |        1  |   01        |  0    2  |   02        |  0    3  |   03        |  1    4  |   04        |  1    5  |   05        |  1    6  |   06        |  4    7  |   07        |  4    8  |   08        |  3    9  |   09        |  5    10 |   010       |  2    ........................so on and on with N packets in same table

Below is what i have tried *but its not getting* `id` N sub packet detail properly: SELECT p.`packet_name` AS MAIN, s.`packet_name` AS SUB FROM packet_table s LEFT JOIN packet_table p ON s.`parent_id` = p.`id`thats as per above table : **id** ( which is primary / auto increment )id = 1 -> main packet (01) , its sub and N sub packets are :01 - > 03,04,0504 -> 06,0703 -> 0805 -> 09

**in short**    01 -> 03 -> 08          04 -> 06 , 07          05 -> 09

its not necessary above design format mysql code .. just simple N sub packet query will doabove is just few but in my case there will be N number of sub packet for each (id).So how it be can achieve .Note : it can be same as Category and N sub Category type

[shashib]

any one here ?

[niche]

This is more of a sql question. What error messages are you getting?

Edited September 5, 2014 by niche

[justsomeguy]

What is the query returning if it's not returning the data you want?

[shashib]

Its Just returning simple join query of main and sub packet s and i want :

 

1 } just need is when i search with id=1 then result will give me it all sub packet and it N sub sub packets,2 } when i search with any sub packet id then it result should give me its sub packet and its n sub packet and so on and on.. also this sub packet is of which main packet and vise versa.**UPDATE :** please check below format for query i need some what N Packet Table Format 1 : thats Main Packet +------------+------------+----------+ | Main Pkt | Sub Packet | COUNT(*) | +------------+------------+----------+ | 01 | 03 | 1 | | 01 | 04 | 1 | | 01 | 05 | 1 | --------------------------------------Second : Table Format 2 : thats Sub and Its N sub Packet +------------+------------+-----------------+ | Main Pkt | Sub Packet | N Sub Packet | +------------+------------+-----------------+ | 01 | 03 | 08 | | 01 | 04 | 06 | | 01 | 04 | 07 | | 01 | 05 | 09 | ---------------------------------------------Third : Table Format 2 : thats Sub and Its N sub Packet +------------+------------+-----------------+ | Main Pkt | Sub Packet | N Sub Packet | +------------+------------+-----------------+ | 01 | 03 | 08 | | 01 | 04 | 06 | | 01 | 04 | 07 | | 03 | 011 | 014 | -- ***** --------------------------------------------- above ***** : here 03 is actually sub packet of 01 hecene it query will also help meSo that 01 - 03 - 011 - 014

[justsomeguy]

With that structure I believe you'll need to use a recursive function to get the children of each parent, I don't think you can use a single query for that.

[shashib]

So can you please help me with that code?

[justsomeguy]

You'll need a recursive function, which is a function that calls itself. The function should be passed an ID, and it will return an array of all children of that ID. So, it should look up all of the children of that ID and start adding them to the array, and for each one it finds it needs to call itself to get the children of that node also. The end result will be an array that is kind of like a tree structure like you describe.

[shashib]

@ above : Your right , but can you help me with its MYSQL QUERY ? (PHP/Mysql ) code .. please

[justsomeguy]

You don't know how to write a query to get all children with a certain parent ID? I'm not going to do your work for you, you need to make an effort.