XML, creating the file with PHP

Question

I have a SQL database and i am performing the following query on it.

        $statistics="SELECT firstname AS 'user', count( * ) AS 'num',member_id AS ID
        FROM members
        JOIN member_photo
        USING ( member_id )
        GROUP BY firstname ";
        $result= mysql_query($statistics);

This will retrieve the uploaders and the number of photos they have uploaded in the following format.

        Uploader  Number of photos uploaded
          mun             20
          ris             10
          moz              5

What i want is for a PHP script to create the XML file. It has to actually save it in my directory. I used SimpleXML to do that.

This is what i did..

    $xmlDoc=loadXMLDoc("photo.xml");

        $statistics="SELECT firstname AS 'user', count( * ) AS 'num',member_id AS ID
        FROM members
        JOIN member_photo
        USING ( member_id )
        GROUP BY firstname ";
        $result= mysql_query($statistics);


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

    $uploader=$xml->addChild('uploader');
    $uploader->addAttribute('ID',$row['ID']);
    $uploader->addChild('Name', $row['user']);
    $uploader->addChild('Photos_Uploaded', $row['num']);
    $xml->asXML('photo.xml');

    }

where photo.xml is the file the XML nodes are being saved to. They are in this format.

      <?xml version="1.0"?>

      <?xml-stylesheet type="text/xsl" href="user.xsl"?>


      <users xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="users.xsd">




       <uploader ID="11"><Name>moz</Name><Photos_Uploaded>2</Photos_Uploaded>             </uploader>

       <uploader ID="13"><Name>Mun</Name><Photos_Uploaded>6</Photos_Uploaded></uploader>

      <uploader ID="12"><Name>ris</Name><Photos_Uploaded>10</Photos_Uploaded></uploader>

      </users>

Now suppose user "Moz" uploads a new photo. His photo counter is initially 2 which should change to three. The XML file should reflect this change right? It does since it's pulling data from the database (remember the query?) but here's the problem.

      <uploader ID="11"><Name>moz</Name><Photos_Uploaded>2</Photos_Uploaded>             </uploader>

       <uploader ID="13"><Name>Mun</Name><Photos_Uploaded>6</Photos_Uploaded></uploader>

      <uploader ID="12"><Name>ris</Name><Photos_Uploaded>10</Photos_Uploaded></uploader>

moz3

       <uploader ID="13"><Name>Mun</Name><Photos_Uploaded>6</Photos_Uploaded></uploader>

      <uploader ID="12"><Name>ris</Name><Photos_Uploaded>10</Photos_Uploaded></uploader>

It replicates the other nodes too which creates duplication into the XML file.

What i thought of was to

first clear the file before populating the XML file again.

Anybody knows how to do that? with php or javascript?

+1 for showing enough sample code for others to understand your question — ford
– ford, Commented Nov 15, 2011 at 16:26
You're always fetching the complete dataset you want to write to the file from the database? — VolkerK
– VolkerK, Commented Nov 15, 2011 at 16:41
Yeah always because its easier and simpler to just delete content and write the whole new content again! — Mozammil_K
– Mozammil_K, Commented Nov 15, 2011 at 16:46

mason81 · Accepted Answer · 2011-11-15 16:38:54Z

2

So you're asking how to 'empty' the photo.xml file before you write/rewrite it? Is that correct?

I'm not familiar with SimpleXML, so I don't know if there is a flag you can use for it or not, but you could always use:

$f = fopen("photo.xml", "w");
fclose($f);

This will: "Open for writing only; place the file pointer at the beginning of the file and truncate the file to zero length. If the file does not exist, attempt to create it."

(see: http://www.php.net/manual/en/function.fopen.php)

EDIT

After looking a little closer at SimpleXML, you might want to have 2 files, 1 a template for the xml doc that you will load and the second will be the one you save (photo.xml). Using the fopen with the 'w' flag will clear the contents, but you will lose your xml structure too.

So actually, you might not even need to use fopen at all...

$xmlDoc=loadXMLDoc("photo_template.xml");

        $statistics="SELECT firstname AS 'user', count( * ) AS 'num',member_id AS ID
        FROM members
        JOIN member_photo
        USING ( member_id )
        GROUP BY firstname ";
        $result= mysql_query($statistics);


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

    $uploader=$xml->addChild('uploader');
    $uploader->addAttribute('ID',$row['ID']);
    $uploader->addChild('Name', $row['user']);
    $uploader->addChild('Photos_Uploaded', $row['num']);
    $xml->asXML('photo.xml');

    }

where photo_template.xml looks like:

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="user.xsl"?>
    <users xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="users.xsd">
    </users>

And photo.xml will end up looking like:

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="user.xsl"?>
    <users xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="users.xsd">
       <uploader ID="11"><Name>moz</Name><Photos_Uploaded>2</Photos_Uploaded>             </uploader>
       <uploader ID="13"><Name>Mun</Name><Photos_Uploaded>6</Photos_Uploaded></uploader>
       <uploader ID="12"><Name>ris</Name><Photos_Uploaded>10</Photos_Uploaded></uploader>
    </users>

edited Nov 15, 2011 at 16:38

answered Nov 15, 2011 at 16:22

mason81

1,7502 gold badges18 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mark Grey Over a year ago

+1 for open flag 'w'. This probably makes a lot more sense than conditionalized checks for xml nodes that would selectively append new entries.

Mozammil_K Over a year ago

But open flag 'w' would delete the XML structure which i did not want to.

Collectives™ on Stack Overflow

XML, creating the file with PHP

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related