How to create sitemap for +150k links

[Stefce]

iI was searching the online "free/cheap" generators and i found out that they are not so cheap...
They advertise the PRO plan for just $3 and when i came to the checkout page it was $1800
So is there other free tool for this job or how do i manually create the sitemap my self. I read on google that one sitemap file should contain no more than 50k links. So in this case i should create sitemap index file.

Any recommendations ?

[pritaeas]

Are you using a CMS? If the CMS does not support it and your links are in a database (or anything similar), use PHP (or any other language of your choosing) to generate the sitemap(s).

[Stefce]

Its build with PHP and yes the links are generated from database input

[Stefce]

I solved it with dynamic sitemaps and as i have read on google docs these kind of sitemaps are way better than static ones because they get updated automatically when some link is inserted or updated.

Here is the code for sitemap1 which selects the first 40k lines from DB. And i have 3 more sitemaps with 40k lines each.

<?php 
require_once("../core/core.php");

$query = "SELECT url, posttime FROM tablename LIMIT 40000";
$result = $conn->query($query);
//$result = mysqli_query($connect, $query);

$base_url = "http://domain.com/sitemap/";

header("Content-Type: application/xml; charset=utf-8");

echo '<?xml version="1.0" encoding="UTF-8"?>'.PHP_EOL; 

echo '<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.sitemaps.org/schemas/sitemap/0.9 http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd">' . PHP_EOL;

while($row = mysqli_fetch_array($result))
{
 echo '<url>' . PHP_EOL;
 echo '<loc>'.$base_url. $row["url"] .'/</loc>' . PHP_EOL;
 echo '<lastmod>'.$row['posttime'].'</lastmod>' . PHP_EOL;
 echo '<changefreq>daily</changefreq>' . PHP_EOL;
 echo '</url>' . PHP_EOL;
}

echo '</urlset>' . PHP_EOL;

?>

and here is the .htaccess code that converts sitemap.php into sitemap.xml (this file needs to be inside the folder where the sitemaps are)

RewriteEngine ON

#show sitemap file with .xml
RewriteRule ^sitemap1\.xml/?$ sitemap1.php

[dannette]

We use this one. They have a free online version. If you want to have it on your website, the base cost is $19.99. We bought it a long time ago and when we need to update, we just use the same link they gave us and download it. It has a lot of features. Click Here

[Stefce]

this is the website i was talking about that at the checkout was $1800

[Dani]

I would definitely build your own dynamic sitemap if the list of pages on your site change frequently (e.g. a blog or user generated content). I'm glad you were able to get it working.

[Stefce]

Dani is this good i have right now for a dynamic sitemap ? Or there are more good practices about this?

[Dani]

A dynamic sitemap is optimal if you have more than a handful of URLs. Depending on how often it changes, you can set cache-control HTTP headers, or even cache it in your file system on your end as well.

[Stefce]

Probably i will update all links automaticly every 5-6 days. Just so the sitemap updates, which Google will have to crawl again.
There will be only images that will be updated with ALT tags.

This way i should trick google into thinking i update my website regulary and maybe index some links in the first page.

[Dani]

Google catches onto those tricks very, very quickly. It’s a sure way for Google to start not trusting your sitemap. And it also makes your content easily detectable as automated. I highly suggest not doing it, but of course you’re free to do a test run and see what works for you.

[Stefce]

But isn't considered this as new content? The sitemap lastpost tag time will be updated and the related page will have new image...

[Dani]

It’s not considered new content if there isn’t a substantial change to the content of the page. Google has reiterated this recently as well, specifically going after blogs that have articles such as “Top X of 2022” and then update it to be “Top X of 2023” and make a couple small changes. That was the example Google mentioned, anyways, but I suspect the algorithm would extend to what you’re doing as well.

[Stefce]

While i was trying to submit my sitemaps to google search console i got an error
An invalid date was found. Please fix the date or formatting before resubmitting.
https://prnt.sc/u7YVw6WErRQa

What should i do to change the date in my sitemap?

[Dani]

The date has to be formatted in W3C Datetime format.

That means it needs to be YY-MM-DD and you currently have YY-DD-MM.

Also, the times are formatted incorrectly as well. Try using ISO 8601 date @ https://www.php.net/manual/en/datetime.format.php

[Stefce]

I have used this code
echo '<lastmod>'.date("Y-m-d", strtotime($row['posttime'])).'</lastmod>' . PHP_EOL;
only to display the date. Is that good dani?

[Dani]

That should work, yes.