UPDATE WORDPRESS LINKS DATABASE WITH FEED URLS via COMMAND LINE

update-wp-links-with-rss.php

<?php
/**
 * UPDATE WORDPRESS LINKS DATABASE WITH FEED URLS via COMMAND LINE
 *
 * @author Joe Crawford
 * @license MIT
 * @version 0.1
 * @link https://artlung.com/
 * @date 2024-03-06
 *
 * Intended to work against the deprecated WordPress Links Database,
 * Run this against your WordPress instance to check for RSS feeds in the links database
 * and update the database with the feed URL if found.
 *
 * This is a one-time script, and should be removed after running.
 *
 * Note that if you don't see a change in the WordPress admin despite this script
 * saying it worked, it may be the object cache, run `wp cache flush-group bookmark`
 * with the WordPress CLI to clear the cache.
 *
 */

$current_dir = dirname(__FILE__);
// load wordpress functions, this path is relative to a theme folder
// .. wp-load.php is in the root of the WordPress install
require_once( $current_dir . '/../../../wp-load.php');

$bookmarks = get_bookmarks();
foreach ($bookmarks as $bookmark) {
	if ($bookmark->link_url) {
		echo  "Checking Link: " . $bookmark->link_url . "\n";
		echo "Named: " . $bookmark->link_name . "\n";
		if (!$bookmark->link_rss) {
			echo "No RSS in WordPress bookmarks db for ID {$bookmark->link_id}, attempting to fetch RSS or Atom:\n";
			$ch = curl_init();
			curl_setopt($ch, CURLOPT_URL, $bookmark->link_url);
			curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
			$page = curl_exec($ch);
			curl_close($ch);

			if (!$page) {
				echo "Error fetching page with curl\n";
				continue;
			}

			$matches = [];

			$dom = new DOMDocument();
			@$dom->loadHTML($page);
			$xpath = new DOMXPath($dom);
			$nodes = $xpath->query('//link[@type="application/rss+xml" or @type="application/atom+xml"]');
			foreach ($nodes as $node) {
				// ignore links with the word comments in them
				if (strpos(strtolower($node->getAttribute('title')), 'comment') !== false) {
					continue;
				}
				$href = $node->getAttribute('href');
				// if it does not begin with http, it's a relative link
				if (strpos($href, 'http') !== 0) {
					// get the root of the link_url
					$root = parse_url($bookmark->link_url, PHP_URL_SCHEME) . '://' . parse_url($bookmark->link_url, PHP_URL_HOST);
					$href = $root . (strpos($href, '/') === 0 ? '' : '/') . $href;
				}
				$matches[] = $href;
			}

			if (empty($matches)) {
				echo "No RSS or Atom found in the html\n";
				continue;
			}

			// if matches, set it and save
			if (!empty($matches)) {
				$linkdata = [
					'link_id' => $bookmark->link_id,
					'link_rss' => $matches[0],
				];
				print "Feed URL found: {$linkdata['link_rss']}\n";
				$wpdb->show_errors();
				$wpdb->query(
					$wpdb->prepare(
						"UPDATE $wpdb->links SET link_rss = %s WHERE link_id = %d",
						$linkdata['link_rss'],
						$linkdata['link_id']
					)
				);
			}

		} else {
			echo "RSS url in db: " . $bookmark->link_rss . "\n";
		}
	}
}