Retrieve Child id's Mysql

[sugan]

HiI want to retrieve all the child id's of top most parent in the parent child hierarchy.

id   name	  parentid1	PHP		   02	Javascript	 03	PHP Script	14	Java Script code  25	Ad management  36	Random Rotator  5

In the above table I want to select all the child id's of parentid 1. i.e, I want resultant set of id's as (3,5,6).Please help me to find set of child id's without using table JOIN in the query. Pls help!Regards,Sugan

[Nim199]

I do not understand, do you want a way to look up all the records where the parent/child ID = 1?

[justsomeguy]

You can't really do that with one query. You need to have a function that gets the children of a certain ID, and the function needs to call itself to get the children of those children. So the first time you would get records where the parent is 1, and for each record you find you would get records where the parent is the ID of the record. It's called a recursive function. In PHP it would look something like this:

function get_children($id){  $result = mysql_query("SELECT * FROM table WHERE parent=$id");  while ($row = mysql_fetch_assoc($result))  {	//display the current record	echo $row['name'];	get_children($row['id']);  }}

[sugan]

Hi,I mean the id should be equal to parent id. The PHP is the parent and the PHP script is the child and the Ad management is the child for PHP script. Like this. Pls help!Regards,Sugan

[Reg Edit]

[...] In the above table I want to select all the child id's of parentid 1. i.e, I want resultant set of id's as (3,5,6) [...]

Well, that would just be:

SELECT id FROM mytablename WHERE parentid = 1

But from your other comments it seems you want other data too? Please show complete retrieved data you want to see in that case.

[justsomeguy]

Hi,I mean the id should be equal to parent id. The PHP is the parent and the PHP script is the child and the Ad management is the child for PHP script. Like this. Pls help!Regards,Sugan

That's exactly the situation where you would use a function like the one I wrote.

[SpOrTsDuDe.Reese]

The code justsomeguy provided works. I just tried it out on my friends sql database server website thing.