yuri1000 yuri1000 - 5 months ago 14
PHP Question

Split sitemaps writes whole query to one file in PHP

I have written this sitemap creation function where it writes to multiple pages splitting 1000 links.

<?php
//write sitemap file
function BuildSitemap($filename,$header,$footer,$body,$filetype,&$i){
global $config;
$ffile = $config['basedir'].'/'.$filename.$i.$ext;
$fp = @fopen($ffile, "wa+");

$filecontent = $header.'\n'.$body.'\n'.$footer;
fwrite($fp, $filecontent);
fclose($fp);
$i++;
}

function Sitemap_posts(){
global $config,$con;
$k = '';
$header = '';
$footer = '';
$body = '';
$ensm = $config['en_sm'];//enable
$burl = $config['burl'];

if($ensm == "1"){
$frequency = 'weekly';
$priority = '0.8000';


//MAKE DB QUERIES
$sql = $con->Execute("SELECT `perma`, `subtime`
FROM `posts`
WHERE `pstatus` = '1'
ORDER BY `id` DESC");

$header .= '<?xml version="1.0" encoding="utf-8"?>'."\n";
$header .= '<urlset
xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xhtml="http://www.w3.org/1999/xhtml"
xsi:schemaLocation="
http://www.sitemaps.org/schemas/sitemap/0.9
http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd">';
$footer .= '</urlset>';

$count = $sql->RecordCount();
$page = ceil($count/1000);
$maxlink = 1000;
for($i=0;$i < $page;$i++){
//if($i < $maxlink){
$stfrom = ($i) * $maxlink;
//} else {
//$stfrom = 0;
//}

$Query = "SELECT `perma`, `subtime`
FROM `posts`
WHERE `pstatus` = '1'
ORDER BY `id` DESC
LIMIT ".$stfrom.", ".$maxlink;
//echo $Query."<br>";
$sub = $con->Execute($Query);
$scount = $sub->RecordCount();
$results = $sub->getrows();
if($scount > 0){
//while ($row = $sub->FetchRow()){
for($j=0;$j<count($results);$j++){
$post_link = $MasterUrl.'/'.$results[$j]['perma'].'/';
$body .= '<url>
<loc>'.$post_link.'</loc>
<lastmod>'.datetimeToutc($results[$j]['subtime']).'</lastmod>
<changefreq>'.$frequency.'</changefreq>
<priority>'.$priority.'</priority>
</url>';
}
$filename = "postsitemap";
BuildSitemap($filename,$header,$footer,$body,".xml",$k);
}
}

}
}
Sitemap_posts();


I have total 1215 records in my DB, it writes the first 1000 properly in the first file, whereas when it writes the second file, it writes the whole 1215 records. Its not splitting at all.

When I view the looped query it comes as

SELECT `perma`, `subtime` FROM `posts` WHERE `pstatus` = '1' ORDER BY `id` DESC LIMIT 0, 1000
SELECT `perma`, `subtime` FROM `posts` WHERE `pstatus` = '1' ORDER BY `id` DESC LIMIT 1000, 1000


I dont know where am going wrong, its been whole night I have been struggling to get through, but no luck.

I need help in... It should split to 2 files, while writing the first 1000 links to the first file and rest 215 links to the second file.

Answer

Here is what happens. On the line

                    $body .= '<url>

you actually concatenate $body with the previous value of the variable - in this case the first 1000 rows. You should re-initialise $body for each new batch of 1000 you start. For instance, you can do it like:

            if($scount > 0){
            unset($body);  // Start with empty body!
            for($j=0;$j<count($results);$j++){

This way, for each 1000-batch starts with clean $body.

I hope this helps!

--

Additionally, if you accept another advise, you might optimise the query you use to get the number of total records:

        $sql = $con->Execute("SELECT `perma`, `subtime`
        FROM `posts` 
        WHERE `pstatus` = '1'
        ORDER BY `id` DESC");
        ...
        $count = $sql->RecordCount();

This unnecessarily runs a full fledged query, fetching lots of data, even sorting it. This will ultimately slow down your code quite a lot and your hosting provider will hate you for it. Instead you could something like:

    $count = $con->Execute("SELECT COUNT(*) as count FROM `posts` WHERE `pstatus` = '1'"); 
    $row = $count->FetchRow();
    $count = $row['count'];  // This is your record count

http://dev.mysql.com/doc/refman/5.7/en/counting-rows.html