Add some data to xml file using PHP xmlwriter

Question

I need to add an article for each month to the xml file using PHP xmlwriter :

 $sql = "SELECT *,YEAR(FROM_UNIXTIME(timestamp)) AS YEAR, MONTH(FROM_UNIXTIME(timestamp)) AS MONTH FROM ".NEWS_ARTICLES." GROUP BY YEAR, MONTH ORDER BY YEAR DESC, MONTH "; $newsdata = DataAccess::Fetch($sql); foreach($newsdata AS $news){ $writer->openURI('./cache/xmls/posts-'.$news['MONTH'].'-'.$news['YEAR'].'.xml'); $writer->startDocument('1.0','UTF-8'); $writer->setIndent(4); $writer->startElement('urlset'); $writer->writeAttribute('xmlns', $xmlns); $writer->startElement('url'); $writer->writeElement('loc',$news['title']); $writer->endElement(); $writer->endElement(); $writer->endDocument(); $writer->flush(); } 

This worked for me and generated a .xml file for each month. But do not add all the headings of the articles for each month, and I see only the heading one in each month! How can I add all articles for every month in xml files?

I think my problem is with the loop !!!

Result: posts-5-2015.xml

 <url> <loc>title</loc> </url> 

In fact, I have 5 articles and need:

Result: posts-5-2015.xml

 <url> <loc>title</loc> </url> <url> <loc>title2</loc> </url> <url> <loc>title3</loc> </url> <url> <loc>title4</loc> </url> <url> <loc>title5</loc> </url> 

print_r($newsdata) Result: NOTE: this is just from a large database and only for the schema

 Array ( [0] => Array ( [id] => 243 [title] => test2 [story] => desc2 [timestamp] => 1442389680 [update_time] => 1442389522 [YEAR] => 2015 [MONTH] => 9 ) [1] => Array ( [id] => 242 [title] => test1 [story] => desc [timestamp] => 1421230680 [update_time] => 1441026399 [YEAR] => 2015 [MONTH] => 1 ) [2] => Array ( [id] => 244 [title] => test3 [story] => desc3 [timestamp] => 1421230680 [update_time] => 1441026399 [YEAR] => 2015 [MONTH] => 5 ) [3] => Array ( [id] => 245 [title] => test4 [story] => desc4 [timestamp] => 1421230680 [update_time] => 1441026399 [YEAR] => 2015 [MONTH] => 5 ) [4] => Array ( [id] => 246 [title] => test5 [story] => desc5 [timestamp] => 1421230680 [update_time] => 1441026399 [YEAR] => 2015 [MONTH] => 5 ) [5] => Array ( [id] => 247 [title] => test6 [story] => desc6 [timestamp] => 1421230680 [update_time] => 1441026399 [YEAR] => 2015 [MONTH] => 5 ) [6] => Array ( [id] => 248 [title] => test7 [story] => desc7 [timestamp] => 1421230680 [update_time] => 1441026399 [YEAR] => 2015 [MONTH] => 5 ) ) 

Answer 1

Your problem in the request:

 $sql = "SELECT *,YEAR(FROM_UNIXTIME(timestamp)) AS YEAR, MONTH(FROM_UNIXTIME(timestamp)) AS MONTH FROM ".NEWS_ARTICLES." GROUP BY YEAR, MONTH ORDER BY YEAR DESC, MONTH "; 

Most databases, except MySQL, will reject this request because you group 2 fields when selecting many fields.
group by year, month will display only one (random) row from the month and hide all others.

The solution is to completely reject the group by clause and rewrite the request as follows:

 $sql = "SELECT *,YEAR(FROM_UNIXTIME(timestamp)) AS YEAR, MONTH(FROM_UNIXTIME(timestamp)) AS MONTH FROM ".NEWS_ARTICLES." ORDER BY YEAR DESC, MONTH ASC"; 

Note
It is strange that you do not have a WHERE . Do you really want to select articles every time?
A better approach would be to limit the number of articles selected in the query by some filter.
It is always faster filtered in the database, and not in php.

Answer 2

You rewrite your XML with each loop. Consider starting the document with the root and closing document outside the foreach :

 $writer->openURI('./cache/xmls/posts-'.$news['MONTH'].'-'.$news['YEAR'].'.xml'); $writer->startDocument('1.0','UTF-8'); $writer->setIndent(4); $writer->startElement('urlset'); foreach ($newsdata as $news){ $writer->startElement('url'); $writer->writeElement('loc',$news['title']); $writer->endElement(); } $writer->endElement(); $writer->endDocument(); $writer->flush();