[SOLVED] Group XML output as groups from MySQL

[sonnyb]

Hi.

I'm building a system for keeping track of video clips, and need to output the information from the MySQL database an XML file.

 

The structure I wanted they wanted for the XML file is something like:

 

<?xml version="1.0" encoding="UTF-8" ?>
<clips>
  <client value="Client1">
  	<clip id="394">
	<title>Title 1</title>
	<categories>Cat1,Cat2</categories>
	<video>videourl/foo1.flv</video>
	<thumbnail>thumburl/thumb1.jpg</thumbnail>
	<date>2008-03-09</date>
	<author>AuthorName</author>
</clip>
  	<clip id="395">
	<title>Title 2</title>
	<categories>Cat1,Cat2</categories>
	<video>videourl/foo2.flv</video>
	<thumbnail>thumburl/thumb2.jpg</thumbnail>
	<date>2008-03-09</date>
	<author>AuthorName</author>
</clip>
  </client>
  <client value="Client2">
  	<clip id="396">
	<title>Title 3</title>
	<categories>Cat1,Cat2</categories>
	<video>videourl/foo3.flv</video>
	<thumbnail>thumburl/thumb3.jpg</thumbnail>
	<date>2008-03-09</date>
	<author>AuthorName</author>
</clip>
  	<clip id="397">
	<title>Title 4</title>
	<categories>Cat1,Cat2</categories>
	<video>videourl/foo4.flv</video>
	<thumbnail>thumburl/thumb4.jpg</thumbnail>
	<date>2008-03-09</date>
	<author>AuthorName</author>
</clip>
  </client>
</clips>

 

Where 'client', title, video, thumbnail, date, author etc are columns in the DB. So what I need to group all the entries with the matching 'client' fields, then output the related videos as the children of that client... Does that make sense?

 

I hope someone can help me get on the right path here. I imagine it's something to do with setting up the right loop with GROUP_CONCAT or GROUP BY, but I'm a little lost as it is.

 

Thank you!

[JonnoTheDev]

You do not want to group them as that will only give you a count of the number of clips per client.

You either want to order the records by client or search for a specific client i.e WHERE client_id='x'

[sonnyb]

Thanks for the reply - I can see that I'd need to order by client in order to output each one in that order, however what I'm looking to do is group all the related videos under one parent node for each unique client like so:

 

<clips>
  <client value="Client1">
     <clip>
     [clipinfo]
    </clip>
     <clip>
     [clipinfo]
   </clip>
  </client>
  <client value="Client2">
     <clip>
      [clipinfo]
   </clip>
  </client>
</clips>

 

So you can see how the first Client has 2 clips, the second only has one etc.

 

I think it would need to sort through all the 'client' fields, create a parent node for each unique one, then loop through the data again and where 'client' matches the parent node, add the clip info as a child of the relevant one.

 

Thanks.

[JonnoTheDev]

Could do or just order all clips by client ID. In a loop when the client ID changes then close the node and create a new node.

 

So your database results returned may look like

 

clipId clientId             author Title

1 1 joe xxxxxxxx

2 1 joe xxxxxxxx

3 1 joe xxxxxxxx

4 2 phil xxxxxxxx

5 2 simon xxxxxxxx

[sonnyb]

Okay, I think I follow... I'll see what I can knock up. Thanks for the advice!

[sonnyb]

Got it all sorted thanks, if anyone else needs it:

 

<?php
$clients_query = mysql_query("SELECT client FROM clips GROUP BY client");
while($row=mysql_fetch_assoc($clients_query))
{
	$clientarray[]=$row['client'];
}
$client_num = count($clientarray);
for ($i = 0; $i <= $client_num-1; $i++)
{
	$data_query = mysql_query("SELECT * FROM clips WHERE client='".$clientarray[$i]."'");
	print "\t<client name=\"$clientarray[$i]\">\n";
	while($data=mysql_fetch_assoc($data_query))
	{
		$items .= "\t \t<clip id=\"{$data['id']}\"> \n";
		$items .= "\t \t \t<title>{$data['title']}</title> \n";
		$items .= "\t \t \t<categories>{$data['title']}</categories> \n";
		$items .= "\t \t</clip>\n";
	}
	print "\t</client>\n";
}
?>

 

And then I have it writing to an XML file from there.

 

Many thanks for the help!