Мне нужно добавить статью за каждый месяц в xml файле, используя 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(); }-$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(); }-$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(); }-$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(); }-$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(); }-$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(); }-$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(); }-$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(); }-$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(); }-$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(); }-$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(); }
Это сработало для меня и сгенерировало .xml файл за каждый месяц. Но не добавляйте все заголовки статей за каждый месяц, и я вижу только one заголовок в каждом месяце! как я могу добавить все статьи за каждый месяц в xml-файлах?
Я думаю, что моя проблема с циклом !!!
результат: posts-5-2015.xml
<url> <loc>title</loc> </url>
В действительности у меня есть 5 статей и нужно:
результат: 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) результат: ПРИМЕЧАНИЕ: это просто из большой БД и только для схемы
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 ) )
Ваша проблема в запросе:
$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 ";
Большинство баз данных, кроме MySQL, отклонят этот запрос, потому что вы группируете 2 поля при выборе многих полей.
group by year, month будет показывать только одну (случайную) строку с месяца и скрыть все остальные.
Решение состоит в том, чтобы полностью удалить предложение group by и переписать запрос следующим образом:
$sql = "SELECT *,YEAR(FROM_UNIXTIME(timestamp)) AS YEAR, MONTH(FROM_UNIXTIME(timestamp)) AS MONTH FROM ".NEWS_ARTICLES." ORDER BY YEAR DESC, MONTH ASC";
замечание
Странно, что у вас нет предложения WHERE . Вы действительно хотите каждый раз выбирать все статьи?
Лучшим подходом было бы ограничить количество статей, выбранных в запросе некоторым фильтром.
Всегда быстрее фильтровать в базе данных, а не в php.
Вы переписываете свой XML с каждым циклом. Рассмотрите возможность запуска документа с корневым и закрывающим документом за пределами цикла 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();-$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();-$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();-$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();-$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();-$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();-$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();-$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();-$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();-$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();