Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Jun 2008
    Posts
    41
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Dynamically create XML from MySQL db? possible?

    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.

  • #2
    New to the CF scene
    Join Date
    Jul 2009
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Yes sure can

    Quote Originally Posted by jeffmc21 View Post
    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.
    Code:
    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 ..
    Code:
    $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

  • #3
    New to the CF scene
    Join Date
    Jul 2009
    Location
    india
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    how to use this codung

  • #4
    New to the CF scene
    Join Date
    Jul 2009
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #5
    New Coder
    Join Date
    Nov 2008
    Posts
    10
    Thanks
    1
    Thanked 0 Times in 0 Posts
    PHPMYADMIN will do it. You can also automatically do it with PHP.

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



  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •