FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


xml from mysql





wangbin218200
Hi,

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


HTML Code:

<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 Code:
<?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:


HTML Code:
<?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
MrBaseball34
Can you post some test data or schema from your database?
I can't see where you are returning files, just menus and links.
hexkid
wangbin218200 wrote:
[...]
but i cant work out how to add in the files to each folder. Any ideas?


If I understood you correctly, basically you have two options.

a) make a database query for folders and, in a loop, make a subquery for the specific folder
b) make a global query and put the data in an array, then process the array

I much prefer option b)

Code:
// a)
################
## NOT TESTED ##
################
$outer_query = 'select * from folder';
// ...
while ($outer_row = mysql_fetch_array($outer_result)) {
  $_xml .=" <folder name='" . $outer_row['name'] . "' link= '" . $outer_row['link'] . "'>\r\n";

  $innersql = "select * from files where foldername='" . $outer_row['name'] . "'";
  // ...
  while ($inner_row = mysql_fetch_array($inner_result)) {
    $_xml .= '<FILE NAME="' . $inner_row['name'] . '" LINK="' . $inner_row['link'] . '"/>' . "\r\n";
  }
  mysql_free_result($inner_result);

  $_xml .="</folder>\r\n";
}
mysql_free_result($outer_result);
mysql_close();


Code:
// b)
################
## NOT TESTED ##
################
define(EOL, "\r\n"); // use "\n" for Linux (or to save a byte per line)

$query = 'select d.name as dname, d.link as dlink, f.name as fname, f.link as flink from folder d, file f where d.name=f.foldername';
// ...
$dlink = $dfiles = array();
while ($row = mysql_fetch_array($result)) {
  if (!isset($dlink[$row['dname']])) $dlink[$row['dname']] = $row['dlink'];
  $dfiles[$row['dname']][] = array($row['fname'], $row['flink']);
}
// all SQL done
mysql_free_result($result);
mysql_close();

// writing XML
$_xml = '<?xml version="1.0" encoding="UTF-8" ?>' . EOL;
$_xml .= '<MENU>' . EOL;
foreach ($dlink as $k=>$v) {
  $_xml .= '<FOLDER NAME="' . $k . '" LINK="' . $v . '">' . EOL;
  foreach ($dfiles[$k] as $f) {
    $_xml .= '<FILE NAME="' . $f[0] . '" LINK="' . $f[1] . '"/>' . EOL;
  }
  $_xml .= '</FOLDER>' . EOL;
}
$_xml .= '</MENU>' . EOL;
Related topics
Help with EVERYTHING
Looking for dedicated scripters/programmers
Tutor site
[man]Server de RO Con mysql
Flash, PHP and MySQL
XML parsing
php and xml
PHP and MySQL FAQ - Read Before posting!
[tutorial]Crear RSS en nuestra web dinamico con php+mysql
MySQL Database Backup Using PHP
CMS without MYSQL
mysql connection question
php admin and mysql admin console
Mysql And PHP HELP PLZ
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.