SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Zealot
    Join Date
    Feb 2004
    Location
    UK
    Posts
    147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql to xml using php

    Not sure if this is the right forum.

    I am using this code to take data from the sql database and convert it into an xml file. However some of the row results from the mysql database have characters that cause errors with the xml file and it doesn't close the elements. For example if there is an apostrophe or euro sign. I thought the using writecdata would sort it but it still failed (also is the resulting xml meant to show the element as <![CDATA[ name ]]> ?)

    Code:
    <?php
    include ("db.php");
    global $dbServer, $dbUser, $dbPass, $dbName;
    $cxn = @ConnectToDb($dbServer, $dbUser, $dbPass, $dbName);
    $sql_0 = "SELECT Name FROM properties";
    $query0 = mysql_query($sql_0) or die("Cannot query the database.<br>" . mysql_error());
    
    $xml = new XMLWriter();
    $xml->openURI("php://output");
    $xml->startDocument('1.0', 'UTF-8');
    $xml->setIndent(true);
    
    $xml->startElement("Envelope");
    $xml->startElement("Body");
    $xml->startElement("add_adverts");
    
    while($row = mysql_fetch_array($query0)){
    $name = $row["Name"];
      $xml->startElement('advert');
      
      $xml->startElement("Name");
      $xml->writeCData ($name);
      $xml->endElement();
       
      $xml->endElement();
    
    } 
    $xml->endElement();
    $xml->endElement();
    $xml->endElement();
    header('Content-type: application/xml; charset=utf-8');
    header('Content-type: text/xml');
    $xml->flush();
    ?>
    Any help or pointers would be appreciated.

    Thanks

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    Well i'd eliminate that space between writeCData and the (.

    You may need to wrap your data in utf8_encode(); Your server may not be using UTF-8 as its standard charset.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  3. #3
    SitePoint Zealot
    Join Date
    Feb 2004
    Location
    UK
    Posts
    147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply, I have removed the space but its made no difference. How do I wrap the data in utf8_encode?

  4. #4
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    quite literally.
    utf8_encode($name);
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  5. #5
    SitePoint Zealot
    Join Date
    Feb 2004
    Location
    UK
    Posts
    147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yay, that's it working now, thanks very much

    Is there a need for Cdata? My results are coming out like this

    <Name>
    <![CDATA[ Dunroamin ]]>
    </Name>

    Which doesn't seem right or is that valid XML?

  6. #6
    SitePoint Zealot
    Join Date
    Feb 2004
    Location
    UK
    Posts
    147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This may sound like a stupid question, while I am using php to create the xml output, what should the file extension be? php or xml? What if I wanted it as xml?

  7. #7
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,181
    Mentioned
    65 Post(s)
    Tagged
    2 Thread(s)
    Your file extension is php, its processing and running in PHP. Your header() function takes care of making sure that whoever is reading your page, knows its being sent in XML.

    Did you not like SimpleXMLElement and DOM? I've never heard of XMLWriter

  8. #8
    SitePoint Zealot
    Join Date
    Feb 2004
    Location
    UK
    Posts
    147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Its just an example I found and I have adapted it to work.

    I have answered my own question regarding making an xml file by used fopen, fwrite and fclose. I take it to keep the xml file up to date I would need to set up a cron job to run the script?

    Thanks

  9. #9
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,181
    Mentioned
    65 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by SQHell View Post
    Hi,

    Its just an example I found and I have adapted it to work.

    I have answered my own question regarding making an xml file by used fopen, fwrite and fclose. I take it to keep the xml file up to date I would need to set up a cron job to run the script?

    Thanks
    I think you've got the wrong idea. You can server the xml file up dynamically, you should never have to write to a file and then read from it again UNLESS you want to prevent a db connection on every connection and update the data on an interval of your choosing, through a cron job.

    If you want to serve dynamically, it should be pretty straight forward:

    Code PHP:
    <?php
    header('Content-type: text/xml');
     
    $result = array('foo' => 1, 'bar' => "dfdf"); //fetch result from db to $result (get rid of deprecated mysql_* extension and use PDO) but heres a fake result
     
    $xml = new SimpleXMLElement("<response></response>");
     
    foreach($result as $column => $value) {
        $xml->addChild($column, $value);
    }
     
    print $xml->saveXML();
     
    /*
    <?xml version="1.0"?>
    <response><foo>1</foo><bar>dfdf</bar></response>
    */

    Why not switch to this and see what happens with your encoding issue?

  10. #10
    SitePoint Zealot
    Join Date
    Feb 2004
    Location
    UK
    Posts
    147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK I will give that a go. Thanks.

  11. #11
    SitePoint Zealot
    Join Date
    Feb 2004
    Location
    UK
    Posts
    147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just looking at your example, surely if I wanted to see the result of that script I would go to the url with an php extension eg mysqltoxml.php but what if I needed mysqltoxml.xml though?

  12. #12
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,181
    Mentioned
    65 Post(s)
    Tagged
    2 Thread(s)
    Apparently I exited my tab without posting my response. Let's go roll back to what you are actually looking to accomplish. Are you trying to load a page and it gives you a physical xml file? Where are you hoping to retrieve this xml payload from? Normally, the client (where you are retrieving the xml from) will do a curl call and parse it from there.

  13. #13
    SitePoint Zealot
    Join Date
    Feb 2004
    Location
    UK
    Posts
    147
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Right, I need to provide an xml file with an .xml extension to another company so that the properties can be displayed on their website.

  14. #14
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,181
    Mentioned
    65 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by SQHell View Post
    Right, I need to provide an xml file with an .xml extension to another company so that the properties can be displayed on their website.
    right, setting the header to text/xml does this, it just happens that your browser opens it up to be viewed in your browser. You can save the web page and it will automatically be an xml file. Exactly the same as when you open a PDF online. You know its a PDF, its just that the browser is handling it for you and you can save the pdf from there if you want.


Bookmarks

Posting Permissions

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