Need some help with a php / sql loop

I am generating a xml file from data in the database, and I need help creating a php script to loop through the database and create a nested xml document from the data in there.

The database is simple:
id,parent_id,name,url,date

The 'parent_id' is the same value of the 'id' of that entry's parent. Basically you have a big tree structure when you associate a particular entry with it's parent. Each child can only have one parent, and there is no limit to the number of children any entry or it's children can have.

What I need to do is loop through all of the entries and build a nested xml file, which needs to be organized by parents and their children. The problem is that I have no idea how many children any parent has. Their could be 15 levels on one branch, and 2 on another. I can easily make a nested script to create the xml but I don't want to manually add each possible level in the php script, I would likely have to account for 40 or 50 levels. Ideally the script would loop through all of the entries, find the children, loop through those, find their children, etc.

The XML will basically look like this: (Simplified)

<xml>
	<parent>
		<child1>
			<child2></child2>
			<child2></child2>
		</child1>
		<child1></child1>
		<child1>
			<child2></child2>
			<child2>
				<child3>
					<child4></child4>
				</child3>
			</child2>	
		</child1>
	</parent>
</xml>

Code (markup):

Anyway, any suggestion or guidance on this would be appreciated. I don't need any help writing the script, more of how to do it without writing each possible loop manually.

 

This can be done quite simply with recursion.

If you have no need for notating "child1", "child2", ... "childn", then I would recommend making them all just "child", as such:

<xml>
	<parent>
		<child>
			<child></child>
			<child></child>
		</child>
		<child></child>
		<child>
			<child></child>
			<child>
				<child>
					<child></child>
				</child>
			</child>	
		</child>
	</parent>
</xml>

Code (markup):

Anyway, pseudocode on doing this with "child1", "child2", ... "childn":

Function GenerateXML()
    Get list of parents from database (WHERE parent_id = 0)

    For each parent in database list:
         <parent>
              GenerateChildXML(parent['id'], 1)
         </parent>

Function GenerateChildXML(parent_id, child_level)
     Get list of children with given parent_id from database

     If no children listed, Then:
           Return

     Otherwise:
          For each child in database list:
          <child{child_level}>
               GenerateChildXML(child['id'], child_level + 1)
          </child{child_level}>

Code (markup):

You would call GenerateXML, which would take care of the parents, and then GenerateChildXML would be called to handle each level of child element.
This would allow you to nest as many levels deep as you needed to (within fairly high limits).

Note: If you did without the <childn>, then you'd be able to remove the "child_level" parameter.

 

Looks like exactly what I need. I originally added the child('1,2,3, etc') just to avoid the confusion of seeing child as every tag, so removing that won't be a problem. Must have been having one of those days as it seems so obvious now.

Thanks