Dynamically create XML from MySQL db? possible?

[jeffmc21]

I have a database that is updated by multiple people through the use of a web form. They are basically able to upload an mp3 clip (interview, audio blog,etc) and then, using the form, input info about it so that it shows up in the audio portion of the site. Creating that was the easy part for me..

I want to know if there's a way to create an XML file from that database info (all the necessary parts are there) that will be readable by iTunes, etc for making a podcast/podfeed?

The people/person who adds the audio is 100% tech unsavvy (whereas I'm only 95%) and there's no way they could manually comprehend how to update an XML podfeed, so it'd be good if there was a way to have it dynamically created and updated every time a new file is loaded to the database.

[RedTussock]

I have a database that is updated by multiple people through the use of a web form. They are basically able to upload an mp3 clip (interview, audio blog,etc) and then, using the form, input info about it so that it shows up in the audio portion of the site. Creating that was the easy part for me..

I want to know if there's a way to create an XML file from that database info (all the necessary parts are there) that will be readable by iTunes, etc for making a podcast/podfeed?

The people/person who adds the audio is 100% tech unsavvy (whereas I'm only 95%) and there's no way they could manually comprehend how to update an XML podfeed, so it'd be good if there was a way to have it dynamically created and updated every time a new file is loaded to the database.

Yes by using the mysql client or mysqldump, E.g.

mysql -uroot --xml -e 'SELECT * FROM foo.footable ORDER BY foo_idx DESC LIMIT 1' > /tmp/foodata.xml


Obviously use a where statement to drill down on the record you want.

OR you can use PHP to create a file ..

$query = "SELECT * FROM footable ORDER BY foo_idx DESC LIMIT 1";
$resultID = mysql_query($query, $linkID) or die("Data not found.");

$xml_output = "<?xml version=\"1.0\"?>\n<?xml-stylesheet type=\"text/xsl\" href=\"/includes/foo.xslt\"?>\n<foodata>\n";

for($x = 0 ; $x < mysql_num_rows($resultID) ; $x++){
$row = mysql_fetch_assoc($resultID);

$xml_output .= "\t<rec>\n";
$xml_output .= "\t\t<idx>" . $row['foo_idx'] . "</idx>\n";
$xml_output .= "\t\t<foo1>" . $row['foo1'] . "</foo1>\n";
$xml_output .= "\t\t<foo2>" . $row['foo2'] . "</foo2>\n";
$xml_output .= "\t\t<foo3>" . $row['foo3'] . "</foo3>\n";

$xml_output .= "\t</rec>\n";

}
$xml_output .= "</foodata>";

$foodata = $xml_output;
$fp = fopen("/tmp/foo.xml", "w");
fwrite($fp, $foodata);
fclose($fp);


Have a read / search for mysql to XML in the tutorial sites, lots of good resource reading.

Cheers

[sunita chauhan]

how to use this codung

[RedTussock]

I have used wget successfully to create an xml file locally to feed to a website for current data.
Use the php or mysql version of the above method, and call it with a script.
I am sorry I cannot imagine how you would start in a windows environment, but I assume there are tools available in Windows to achieve this. My apologies for the brief reply, What I do suggest is that you have a good read of of a book like XML,XSLT, Java and JSP by Westy Rockwell, to get a start. Do the example application, it will get you going in the right direction. Also have a look at the AJAX methods, which is nothing new, other than it uses methods with XML, Java etc to update just the dynamic content of the page, as apposed to the whole page.

[rizzo89]

PHPMYADMIN will do it. You can also automatically do it with PHP.

http://us3.php.net/manual/en/book.dom.php

http://www.soft32.com/screenshot/49-0-30130.html