xml from mysql

[g5604]

Hi,

I am trying to generate this xml schema from a mysql database:

<MENU>		
	<FOLDER NAME="Web" link="web.swf">
		<FILE NAME="Printbook" LINK="printbook.jpg"/>
		<FILE NAME="site" LINK="site.jpg"/>
	</FOLDER>
	<FOLDER NAME="Print link="print.swf"">
		<FILE NAME="Submenu Link 1" LINK="sublink31.html" />
	</FOLDER>
</MENU>

heres my php so far:

<?php
$db_name = "portfolio"; 
$link = mysql_connect("localhost", "root", "****") or die("Could not connect to server!");
$table_name = 'folder';
$select_db = mysql_select_db($db_name, $link); 
$query = "SELECT * FROM " . $table_name;
$result = mysql_query($query, $link) or die("Could not complete database query"); 
$num = mysql_num_rows($result); 
if ($num != 0) {
 $file= fopen("results.xml" , "w"); 
  $_xml ="<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\r\n";
  $_xml .="<menu>\r\n";
  while ($row = mysql_fetch_array($result)) { 
    $_xml .="   <folder name='" . $row[name] . "'  link= '" . $row[link] . "'>\r\n";

 $_xml .=" </folder>\r\n";
	}

  $_xml .="</menu>";
fwrite($file, $_xml);
fclose($file); 
  echo $_xml;
} else {
  echo "No Records found";
}

?>

This generates this:

<?xml version="1.0" encoding="UTF-8" ?>
<menu>
   <folder name='web'  link= 'web.swf'>
 </folder>
   <folder name='print'  link= ''print.swf'>
 </folder>
</menu>

but i cant work out how to add in the files to each folder. Any ideas?

many thanks,
Gareth

[nnichols]

You have not shown how you are storing the files in the database. Assuming that files are stored in a table called files you can use the following query -

SELECT *
FROM folders
LEFT JOIN files
	ON folders.id = files.folder_id
ORDER BY folers.name ASC, files.name ASC

Then when iterating through the results simply check to see if each file is in the same folder as the previous file, if not close the previous folder node and open a new one.

Hope this helps you get started 😉

[g5604]

many thanks for your reply 🙂

i have two tables folder and files.

heres what i have so far:

<?php
$db_name = "portfolio";
$link = mysql_connect("localhost", "root", "***") or die("Could not connect to server!");
$table_name = 'folder';
$select_db = mysql_select_db($db_name, $link);

$query = "SELECT * FROM " . $table_name;
$result = mysql_query($query, $link) or die("Could not complete database query");
$num = mysql_num_rows($result);

if ($num != 0)
{
    $file= fopen("results.xml" , "w");
    $_xml ="<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\r\n";
    $_xml .="<menu>\r\n";
    while ($row = mysql_fetch_array($result))
    {
        $_xml .="   <folder name='" . $row[name] . "'  link= '" . $row[link] . "'>\r\n";


    $query = 'SELECT * FROM files WHERE folder="'.$row[name].'";';
    $files = mysql_query($query, $link) or die("Could not complete database query");
    $numOfFiles = mysql_num_rows($result);

    while ($file = mysql_fetch_array($result))
    {
        $_xml .= '\t\t<FILE NAME="'.$file[name].'" LINK="'.$row[link].'"/>\r\n';
    }


    $_xml .=" </folder>\r\n";
}
$_xml .="</menu>";
fwrite($file, $_xml);
fclose($file);
echo $_xml;
}
else
{
    echo "No Records found";
}

?> 

but i am getting these errors:

Warning: fwrite(): supplied argument is not a valid stream resource in C:\public_html\MakeXML\make2.php on line 36

Warning: fclose(): supplied argument is not a valid stream resource in C:\public_html\MakeXML\make2.php on line 37
\t\t\r\n\t\t\r\n

any ideas what i am doing wrong?

[nnichols]

You should really be doing it as a single query, as I suggested above, intead of running a series of nested queries. The error is being caused by you overwriting the variable that is holding the file handle.

$file= fopen("results.xml" , "w");

and then

while ($file = mysql_fetch_array($result))

So when you try to

fwrite($file, $_xml);

$file is an array not the file handle resource.

[g5604]

Hi,

many thanks for your help 🙂

i am very new to php so its very hard at the moment for me to work this out. I will try and do it the way you suggested.

gareth.

[nnichols]

Remember to mark this thread as RESOLVED, if it is.