How to Find Extra or Missing Product SEO URLs in OpenCart

Posted on by Priyo G

Problem Statement

When working with OpenCart, you might encounter inconsistencies between the total number of products in your database and the number of SEO URLs assigned to them. For example, suppose you have 158 products in the oc_product_description table for a specific language (language_id = 1), but the oc_seo_url table shows 159 entries for product-related queries. This means there is an extra SEO URL entry that does not correspond to a valid product.

Such discrepancies can cause SEO issues, broken links, or incorrect redirects. In this article, we will go through a SQL query to identify any extra product SEO URLs that do not have a corresponding product entry.

Understanding the Issue

Step 1: Check the Number of Products

To get the total number of products in the oc_product_description table for a specific language, use the following SQL query:

SELECT COUNT(*) AS total_products 
FROM oc_product_description 
WHERE language_id = 1;

This will return the total number of products available in that language.

Step 2: Check the Number of Product SEO URLs

Now, check how many SEO URLs exist for products in oc_seo_url with:

SELECT COUNT(*) AS total_seo_urls 
FROM oc_seo_url 
WHERE language_id = 1 AND query LIKE 'product_id=%';

If the count from this query is greater than the count from oc_product_description, there are extra SEO URLs that need to be identified.

Finding the Extra Product SEO URL

To find the product IDs that exist in oc_seo_url but do not have a corresponding entry in oc_product_description, run the following SQL query:

SELECT SUBSTRING_INDEX(su.query, '=', -1) AS product_id
FROM oc_seo_url su
LEFT JOIN oc_product_description pd 
    ON SUBSTRING_INDEX(su.query, '=', -1) = pd.product_id 
    AND pd.language_id = 1
WHERE su.language_id = 1 
AND su.query LIKE 'product_id=%' 
AND pd.product_id IS NULL;

Explanation:

  1. SUBSTRING_INDEX(su.query, '=', -1): Extracts the numeric product_id from query in oc_seo_url, which is stored in the format 'product_id=123'.
  2. LEFT JOIN with oc_product_description to match extracted product_id values.
  3. pd.product_id IS NULL: Filters out the product IDs that do not exist in oc_product_description.

This will return a list of product_ids that have an entry in oc_seo_url but do not exist in oc_product_description.

Fixing the Issue

Option 1: Manually Delete the Extra SEO URL

If you identify an extra SEO URL that does not correspond to any product, you can manually delete it with:

DELETE FROM oc_seo_url 
WHERE language_id = 1 
AND query = 'product_id=EXTRA_ID';

Replace EXTRA_ID with the actual product ID that should not be in oc_seo_url.

Option 2: Remove All Extra Entries Automatically

If there are multiple incorrect entries, you can remove them in one go:

DELETE su 
FROM oc_seo_url su
LEFT JOIN oc_product_description pd 
    ON SUBSTRING_INDEX(su.query, '=', -1) = pd.product_id 
    AND pd.language_id = 1
WHERE su.language_id = 1 
AND su.query LIKE 'product_id=%' 
AND pd.product_id IS NULL;

This will delete all orphaned SEO URLs that do not have a corresponding product.

Conclusion

Maintaining a clean oc_seo_url table is essential for proper SEO performance and avoiding broken product links. By following this method, you can quickly identify and fix any extra SEO URLs that do not belong to valid products.

If you’re managing an OpenCart store, periodically running these queries can help keep your database optimized and error-free. 🚀