My site has a Wordpress blog on it, but it does not power the whole site, just the blog. If my entire site were powered by Wordpress, then I could generate a sitemap for Google using Wordpress functions.
I did not want to have Wordpress control everything; I wanted to have more control. By doing some quick queries, you can generate a sitemap for Google that has all of your blog entries in it.
First, you will need to send the correct headers, create the opening xml tag, and connect to your database.
<?php
header("Content-Type: text/xml;charset=iso-8859-1");
echo '<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">';
require_once('DB CONNECTION GOES HERE'); //This is where I would require my DB connection file
If you are not sure how to connect to your database, you will need to contact your host to get that information, and then do a search for php MySQL connection. If you can’t figure it out still, let me know, and I can help.
Next, we want to query the database to get the categories used in Wordpress:
$query = "SELECT cat_ID, category_nicename
FROM wp_categories
ORDER BY category_nicename";
$result = @mysql_query($query);
Then, we need to loop through the categories and display a url entry for each category:
while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo '<url>
<loc>//trevor-davis.com/blog/' . $row['category_nicename'] . '/</loc>
<changefreq>weekly</changefreq>
</url>';
Next, we need to create a query to return all of the entries for each category:
$artQuery = "SELECT p.post_name, DATE_FORMAT(p.post_date, '%Y-%m-%d') AS createdOn
FROM wp_posts AS p, wp_categories AS cat, wp_post2cat AS pc
WHERE p.ID = pc.post_id AND pc.category_id = " . $row['cat_ID'] . "
GROUP BY p.ID
ORDER BY p.ID DESC";
$artResult = @mysql_query($artQuery);
Finally, we want to create a url entry for each blog entry:
while($artRow = mysql_fetch_array($artResult, MYSQL_ASSOC)) {
echo '<url>
<loc>//trevor-davis.com/blog/' . $row['category_nicename'] . '/'. $artRow['post_name'] . '.php</loc>
<lastmod>'.$artRow['createdOn'].'</lastmod>
<changefreq>weekly</changefreq>
</url>';
}
To finish it off, we just close everything up:
}
echo'</urlset>';?>
Here is the finished script:
<?php
header("Content-Type: text/xml;charset=iso-8859-1");
echo '<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">';
require_once('DB CONNECTION GOES HERE'); //This is where I would require my DB connection file
$query = "SELECT cat_ID, category_nicename
FROM wp_categories
ORDER BY category_nicename";
$result = @mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo '<url>
<loc>//trevor-davis.com/blog/' . $row['category_nicename'] . '/</loc>
<changefreq>weekly</changefreq>
</url>';
$artQuery = "SELECT p.post_name, DATE_FORMAT(p.post_date, '%Y-%m-%d') AS createdOn
FROM wp_posts AS p, wp_categories AS cat, wp_post2cat AS pc
WHERE p.ID = pc.post_id AND pc.category_id = " . $row['cat_ID'] . "
GROUP BY p.ID
ORDER BY p.ID DESC";
$artResult = @mysql_query($artQuery);
while($artRow = mysql_fetch_array($artResult, MYSQL_ASSOC)) {
echo '<url>
<loc>//trevor-davis.com/blog/' . $row['category_nicename'] . '/'. $artRow['post_name'] . '.php</loc>
<lastmod>'.$artRow['createdOn'].'</lastmod>
<changefreq>weekly</changefreq>
</url>';
}
}
echo'</urlset>';
?>
We also want to rewrite the url for this file so that it is available at //trevor-davis.com/sitemap.xml. Open up your .htaccess file, or create one if you don’t have one. Then add the following:
RewriteEngine on
RewriteRule sitemap.xml googleSitemap.php
Upload this file along with the googleSitemap.php script to your site root, and you are set. You can also add the rest of your site’s file structure to the sitemap as well. You can see how I did this in my google sitemap.
You can read more about the sitemaps protocal to see what other attributes you can apply to each url entry.
Let me know if you can think of any way to improve this.
";s:13:"numberOfLikes";N;s:10:"bodyBlocks";a:0:{}}}}}i:1;O:25:"yii\caching\TagDependency":3:{s:4:"tags";a:4:{i:0;s:7:"element";i:1;s:29:"element::craft\elements\Entry";i:2;s:40:"element::craft\elements\Entry::section:4";i:3;s:7:"graphql";}s:4:"data";a:4:{s:40:"CraftCMSce35088bdfe0816226cd17fd051a4803";s:21:"0.70573500 1736501960";s:40:"CraftCMS2743a789e8993267a348eee1ee7e4450";s:21:"0.67537500 1713205687";s:40:"CraftCMS2ac34726f299f7e18e449d8e536c67f8";s:21:"0.84529700 1741778847";s:40:"CraftCMS3817d4a648fcfac939af46605323feb0";s:21:"0.36746500 1735923287";}s:8:"reusable";b:0;}}