Check 404 URLs in Database

There are many ways to do this. I wanted the quickest and easiest. I run Yourls on several sites to create shortlinks for my books. Each instance of Yourls contains many URLs. I like to keep my books current. URLs tend to change and break over time. It is a chore to check 800+ links in each of my books, page after page. So I wanted a quick way to check for 404 and other broken links. In this post, I share the technique that works best for me; your mileage may vary.

Contents

• Overview
• Types of errors
• Specific example walkthrough
  • Step 1: Export the data
  • Step 2: Format the data
  • Step 3: Analyze the data

Overview

Here is a quick overview of what we’re doing here:

1. Export the URLs from your database. This can be easy or tricky depending on how things are set up and the app you’re using to interact with the database.
2. Create a PHP or other script to output the exported URLs in HTML format, as a plain list of hyperlinks, all on the same page.
3. Use a free browser extension to crawl each link and check for any errors.

That’s all there is to it. I am writing this down so I can easily reference it for future book updates. Hopefully it helps you too.

Types of errors

This technique catches most common errors, for example:

• 5xx errors (e.g., 503 Service Unavailable, 504 Gateway Timeout)
• 4xx errors (e.g., 404 Not Found, 403 Forbidden)
• 3xx redirects (e.g., 301 Permanent, 302 Temporary)
• 2xx issues (e.g., 205 Reset Content, 206 Partial Content)
• Empty response

This technique does not catch the case where a link points to a page where the content itself has changed. For example, if you have a link that originally pointed to an article about SQL, but the article content has changed and now is about squirrels or something. Maybe you can tap AI for that sort of task?

Fortunately, in my experience, the content-completely-changing-but-at-the-same-URL case is rare. As most of the time when links change it means that the content went offline or was redirected. This technique catches both of these key scenarios.

Specific example walkthrough

To provide a real-world example, I’ll walk through the steps and code for doing this with the URLs in a Yourls database. There may already be an extension/addon or plugin/script that can do this, I didn’t check tbh. Without further ado..

Step 1: Export the data

First. I use the well-established, completely free and open-source phpMyAdmin to manage my MySQL databases. So that makes it easy to export all of the URLs as a PHP array, ready to go. For the URLs database, you can get all URLs with the following SQL query:

SELECT `url` FROM `yourls_url`;

That is, select all rows from the url column in the yourls_url table. Depending on how your database is setup, the names of columns and rows may vary; adjust the above query accordingly.

After running the query, scroll down to the bottom of the SQL results and click on the Export button, as shown here (click image to view full size):

Screenshot showing the Export button provided by phpMyAdmin

That will take you to the Export screen. There you can select any format, I prefer PHP so I chose PHP array, as shown here (click image to view full size):

Screenshot showing export options for phpMyAdmin

Then scroll down and click the Go button to download the generated PHP file.

Step 2: Format the data

After downloading the file, open it. Copy the variable name given for the array (in this example it is $taowp_url), and scroll down to the end of the file. There, add the following PHP code snippet (just after the array of URLs):

echo '<h1>Total: '. count($taowp_url) .'</h1>';
echo '<ol>';
foreach ($taowp_url as $url) {
	
	foreach ($url as $key => $value) {
		
		echo '<li><a href="'. $value .'">'. $value .'</a></li>';
		
	}
	
}
echo '</ol>';

Save changes and upload to your server. There you can access the file in your browser to run the script and view the output HTML hyperlinks. It will simply be a list of links, one for each URL in your database. Feel free to customize and embellish the above script to suit your needs.

Step 3: Analyze the data

The last step is to use a free broken link checker extension for your browser. I use Chrome mostly, so this Broken Link Checker works great.

After installing/activating the extension, simply click its icon/button to check all URLs on the page, automatically.

Done! 😎

About the Author

Jeff Starr = Web Developer. Book Author. Secretly Important.