Creating/updating an xml file from a mysql database table with php

Question

I have a javascript image gallery which is getting data from an XML file. Im in the process of building a backend function in order for someone else to easy update the gallery. All the data required in the XML file, is available to me from a table in my database.

<?xml version="1.0" encoding="UTF-8"?>
<portfolio>
<items> 
    <item>
        <thumbnail>images/banner001.jpg</thumbnail>
        <preview>images/preview.jpg</preview>
        <category>banner</category>
        <description>Swatch MTV </description>
    </item>
    <item>
        <thumbnail>images/banner002.jpg</thumbnail>
        <preview>images/preview.jpg</preview>
        <category>banner,campaign</category>
        <description>Nike iD</description>
    </item>
    <item>
        <thumbnail>images/banner003.jpg</thumbnail>
        <preview>images/preview.jpg</preview>
        <category>banner</category>
        <description>Sony Handycam</description>
    </item>
    <item>
        <thumbnail>images/banner006.jpg</thumbnail>
        <preview>images/preview.jpg</preview>
        <category>banner</category>
        <description>Statoil App kampagne</description>
    </item>     
    <item>
        <thumbnail>images/design006.jpg</thumbnail>
        <preview>images/preview.jpg</preview>
        <category>web</category>
        <description>Hempel Website design</description>
    </item>

</items>
</portfolio>

In this regard I have a couple of questions (please keep in mind that im fairly new to php):

which is the better/easier option: update the existing xml file or simply overwrite the entire xml file with newer/updated data?
how do I create the same xml format using php?
I have been looking at both SimpleXML and the DOMDocument. Which one is better/easier to use in my case?

I would be grateful for any help you guys can give me. If posting code, please include a short description/explanation.

Please let me know if you need anything clarified.

score 1 · Accepted Answer · answered Apr 11 '12 at 12:32

I think it is easy when you overwrite the entire xml file with newer/updated data. Below is the php file which creates the xml data by connecting to the database.

test.php

<?php header('Content-type: application/xml');echo "<?xml version=\"1.0\" encoding=\"UTF-8\"?>";

    //connect to db here
    $query1 = "select datas";
    $res1   = mysql_query($query1);
    echo '<portfolio>';
    echo '<items>';
    while($row1 = mysql_fetch_array($res1)) 
    {
    ?>
      <item>
        <thumbnail><?php echo $row1['yourdata1']; ?></thumbnail>
        <preview><?php echo $row1['yourdata2']; ?></preview>
        <category><?php echo $row1['yourdata3']; ?></category>
        <description><?php echo $row1['yourdata4']; ?></description>
      </item>
   <?php
   } 
  ?>
  </items>
</portfolio>

If the above filename is test.php, then you can write .htaccess redirection to redirect the url to .php file when the url is given as .xml

Below code redirects to test.php when the url is given as http://yoursitename.com/test.xml

RewriteCond %{REQUEST_URI} test.xml
RewriteRule test.xml test.php [L]

Are the redirect commands shell? Can you please provide a PHP command so the rewrite can be done automatically prior to any sql requests? — Jim22150, Jan 09 '14 at 16:30
Whilo this script works, you should not use mysql_* family of functions as they are deprecated and will be removed in a future version of PHP. Use mysqli or PDO instead. Also, you will need to sanitize your database values before writing them out to XML. What if a Category is called "Friends & Family"? The & needs to be sent as &. Also, if your description is going to have HTML tags or markup, either encode it or send it as CDATA. — Oscar M., May 12 '14 at 16:57

score 0 · Answer 2 · answered Apr 11 '12 at 11:49

ad 1) imho is better updating (adding/removing) entries to xml file, If you would generate new file every time (in case there are tons of images) you would have to save a lot of data to file...it doesnt seem to be right approach to me.

ad 2) php has several libraries which will help creating xml files, for instance you mentioned simplexml - i use it from times to time and works fine.

ad 3) again imho is pretty straitforward, easy to understand a use, i would recommed it. i think theres no need to show here how it works...google got a lot of examples.

in case u will stuck on something dont hasitate to ask here.

Creating/updating an xml file from a mysql database table with php

2 Answers2