MySQL to XML

[TheRealRaziel]

Hi,

i'm building a small cms for a flash movie but i need to put the MySQL data into an xml file due to security reasons.

right now i have the first part of a parser that checks the time entries in the table to see if their recent. if the entry is it get's parsed into the xml.

but how do i get the sql data parsed into an xml file?

right now i made a sample xml file by hand and it looks like this:


<?xml version="1.0\" ?>
<myImages>
<src file="banner.jpg" freq="4" url="click.php?id=1" />
<src file="banner.swf" freq="3" url="click.php?id=3"/>
<src file="banner2.jpg" freq="2" url="click.php?id=2" />
<src file="outofservice.swf" freq="1" url="click.php?id=4" />
</myImages>

[andehlu]

umm do u mean you want a file that feeds the data to xml? If so you can use a php script to output the data inot the structure you need. If oyu mean you want to dump your database to a .xml file.... if you have phpmyadmin installed (which i strongly recommend) there is an export feature XML being one of the options.

[TheRealRaziel]

i want it to be a php script yes, would be easier to integrate into a CMS don'tyou think ? ;)

[andehlu]

well this is a sample of how i do it...it wont make the structure you need but you can modify it...


<?php

$username="user";
$password="pass";
$database="dbname";
$host="localhost";
$tablename="portfolio";


$db = mysql_connect($host, $username, $password);
mysql_select_db($database);

//query db data
$sql = "SELECT * FROM $tablename";
$result = mysql_query($sql) or die ("ERROR");

print "<root>\n\n";

while ($row = mysql_fetch_array($result)){

//print name and external url
print "<project pname=\"";
printf ("%s", $row["pname"]);
print "\" purl=\"";
printf ("%s", $row["purl"]);
print "\">\n";

//print path to images
print "<pics path1=\"";
printf ("%s", $row["path1"]);
print "\" path2=\"";
printf ("%s", $row["path2"]);
print "\" />\n";

//print description
print "<description descblurb=\"";
printf ("%s\">", $row["descblurb"]);
print "</description>\n";

print "</project>\n\n";
}

print "</root>";

mysql_close ($db);
?>