CODEWITHSUNDEEP
phpxml
Michael JS
Michael JS

Reputation: 27

PHP mysql - Generating a sitemaps without exceeding 50k rows limit

I have the follwing PHP / MYSQL sitemap that generates a sitemap into the browser. How can i make it save itself to a file and split on 50.000 ? i cant get it to work properly as the script just outputs into plain text. I want the script to output into a file

currently 

<?

$xmlfile = 'sitemap.xml';

// this variable will contain the XML sitemap that will be saved in $xmlfile
$xmlsitemap = '<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">';

// Connection data (server_address, name, password, database_name)
$hostdb = '';
$userdb = '';
$passdb = '';
$namedb = '';

try {
  // Connect and create the PDO object
  $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
  $conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8

  // Define and perform the SQL SELECT query
  $sql = "SELECT `shortUrl` FROM `shorturl`";
  $result = $conn->query($sql);

  // If the SQL query is succesfully performed ($result not false)
  if($result !== false) {
    // Parse the result set, and add the URL in the XML structure
    foreach($result as $row) {
      $xmlsitemap .= '
<url>
<loc>'. $row['shortUrl'] .'</loc>
<priority>0.5</priority>
<changefreq>weekly</changefreq>
</url>';
    }
  }

  $conn = null;        // Disconnect
}
catch(PDOException $e) {
  echo $e->getMessage();
}

$xmlsitemap .= '</urlset>';
file_put_contents($xmlfile, $xmlsitemap);          // saves the sitemap on server

// outputs the sitemap (delete this instruction if you not want to display the sitemap in browser)
echo $xmlsitemap;

?>

Upvotes: 2

Views: 448

Answers (2)

Teoman Tıngır
Teoman Tıngır

Reputation: 2866

Let's take a look at the code that generating url records..

<url>
<loc>'. $row['shortUrl'] .'</loc>
<priority>0.5</priority>
<changefreq>weekly</changefreq>
</url>';

As above, one record contains 5 rows. So your array must contains max 10.000 records right ?

If we split our array into the chunks which contains max 10.000 records, we can easily save each chunk records in different files. 

try {
  // connect db and get records

  # give attention here
  $chunks = array_chunk($result, 10000)
  // If the SQL query is succesfully performed ($result not false)
  if($result !== false) {
    // Parse the result set, and add the URL in the XML structure
        foreach ($chunks as $key => $chunk) {
            $xmlsitemap .= '</urlset $namespaces>';
            foreach ($chunk as $row){
                $xmlsitemap .= '
            <url>
            <loc>' . $row['shortUrl'] . '</loc>
            <priority>0.5</priority>
            <changefreq>weekly</changefreq>
            </url>';
            }
            $xmlsitemap .= '</urlset>';
            file_put_contents("path/to/directory/sitemap-".$key.".xml", $xmlsitemap);
        }
  }
}
// check errors

Btw don't forget to create sitemap index for those

Upvotes: 1

metalfight - user868766
metalfight - user868766

Reputation: 2750

Welcome to StackOverflow.

Simply run a counter in your foreach and break it to your desired value.

Not tested code is below

    if($result !== false) {
        // Parse the result set, and add the URL in the XML structure
         $counter = 0;
        foreach($result as $row) {
          $counter++
          $xmlsitemap .= '
    <url>
    <loc>'. $row['shortUrl'] .'</loc>
    <priority>0.5</priority>
    <changefreq>weekly</changefreq>
    </url>';
if($counter==4900){
 break;
}
        }
      }

Upvotes: 0

Related Questions