Handling Products Removed From Datafeeds

I received an email the other day from Garrett, and he asked:

I’m just curious how you might deal with pages/urls on your site that have been indexed (and may be getting good traffic/links), but all of a sudden they’ll disappear if the product happens to drop off the merchant’s feed.

After a couple emails back & forth, I think the best solution would be to find a similar product, and 301 redirect to that product. First, I’m going to change my .htaccess file to pass in the “name” of the item that was requested:

Change

RewriteRule ^(.*)\-p([0-9]+).php$ item.php?ProductID=$2 [L]

To

RewriteRule ^(.*)\-p([0-9]+).php$ item.php?ProductID=$2&cName=$1 [L]

Then edit item.php, and after $rsData = mysql_fetch_array($oResult); (about line 6), add:

if (!$rsData) {
	// We don't have this item anymore!

	// Get the parts of the product name
	// See http://ericnagel.wpengine.com/2010/03/follow-up-on-datafeeds-podcast.html
	$aNameParts = explode('-', $_GET['cName']);

	// Look up similar products in the database
	$cQuery = "select * from products where";
	foreach ($aNameParts as $cNamePart) {
		$cQuery .= " Name like '%" . mysql_real_escape_string($cNamePart) . "%' or ";
	} // ends foreach ($aNameParts as $cNamePart)
	$cQuery = ereg_replace(" or $", "",$cQuery ); // remove the last "or"
	$oResult = mysql_query($cQuery);
	$rsData = mysql_fetch_array($oResult);
	if (!$rsData) {
		// No clue
		header("Location:  /");
		exit();
	} // ends if (!$rsData)
	else {
		$p = 0;
		$nItemID = $cName = '';
		$cOrig = str_replace('-', ' ', $_GET['cName']);

		do {
			similar_text ($cOrig, $rsData['Name'], $fMatch);
			if ($fMatch > $p) {
				$nItemID = $rsData['ProductID'];
				$cName = $rsData['Name'];
				$p = $fMatch;
			} // ends if ($fMatch > $p)
		} while ($rsData = mysql_fetch_array($oResult));

		if ($p > 0) {
			// Best match
			header("Location:  /" . ereg_replace("[^A-Za-z0-9]", "-", $cName) . "-p" . (int)$nItemID . ".php", TRUE, 301);
			exit();
		} // ends if ($p > 0)
		else {
			// No good match
			header("Location:  /");
			exit();
		} // ends else from if ($p > 0)
	} // ends else from

} // ends if (!$rsData)

Now, if product 8675309 used to be at www.greenwhiteandblacktea.com/New-Snow-Sprout-p8675309.php, the script will see it’s no longer in the database, then get all products with “New”, “Snow”, or “Sprout” in the title, compare them to “New Snow Sprout” and redirect the user to the best choice (in this case, www.greenwhiteandblacktea.com/Ancient-Snow-Sprout-p469648555.php)

By default, the redirect is “HTTP/1.1 302 Found”, so we add “, TRUE, 301” to the header call to change it to a permanent redirect.

Of course, for all this to work, you have to be using .htaccess rewrites, otherwise there’s no way to get the name of the missing (and requested) product.

Thanks for the inspiration to this post, Garrett! Hope this answered your question for you!