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 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.<\/p>\n\n\n\n To get the total number of products in the This will return the total number of products available in that language.<\/p>\n\n\n\n Now, check how many SEO URLs exist for products in If the count from this query is greater than the count from To find the product IDs that exist in This will return a list of If you identify an extra SEO URL that does not correspond to any product, you can manually delete it with:<\/p>\n\n\n\n Replace If there are multiple incorrect entries, you can remove them in one go:<\/p>\n\n\n\n This will delete all orphaned SEO URLs that do not have a corresponding product.<\/p>\n\n\n\n Maintaining a clean If you’re managing an OpenCart store, periodically running these queries can help keep your database optimized and error-free. \ud83d\ude80<\/p>\n","protected":false},"excerpt":{"rendered":" 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 […]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18,22],"tags":[],"class_list":["post-5890","post","type-post","status-publish","format-standard","hentry","category-blogs","category-technical-guide"],"yoast_head":"\noc_product_description<\/code> table for a specific language (language_id = 1<\/code>), but the oc_seo_url<\/code> 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.<\/p>\n\n\n\n
\n\n\n\nUnderstanding the Issue<\/h2>\n\n\n\n
Step 1: Check the Number of Products<\/h3>\n\n\n\n
oc_product_description<\/code> table for a specific language, use the following SQL query:<\/p>\n\n\n\nSELECT COUNT(*) AS total_products \nFROM oc_product_description \nWHERE language_id = 1;\n<\/code><\/pre>\n\n\n\nStep 2: Check the Number of Product SEO URLs<\/h3>\n\n\n\n
oc_seo_url<\/code> with:<\/p>\n\n\n\nSELECT COUNT(*) AS total_seo_urls \nFROM oc_seo_url \nWHERE language_id = 1 AND query LIKE 'product_id=%';\n<\/code><\/pre>\n\n\n\noc_product_description<\/code>, there are extra SEO URLs that need to be identified.<\/p>\n\n\n\n
\n\n\n\nFinding the Extra Product SEO URL<\/h2>\n\n\n\n
oc_seo_url<\/code> but do not have a corresponding entry in oc_product_description<\/code>, run the following SQL query:<\/p>\n\n\n\nSELECT SUBSTRING_INDEX(su.query, '=', -1) AS product_id\nFROM oc_seo_url su\nLEFT JOIN oc_product_description pd \n ON SUBSTRING_INDEX(su.query, '=', -1) = pd.product_id \n AND pd.language_id = 1\nWHERE su.language_id = 1 \nAND su.query LIKE 'product_id=%' \nAND pd.product_id IS NULL;\n<\/code><\/pre>\n\n\n\nExplanation:<\/h3>\n\n\n\n
\n
SUBSTRING_INDEX(su.query, '=', -1)<\/code>: Extracts the numeric product_id<\/code> from query<\/code> in oc_seo_url<\/code>, which is stored in the format 'product_id=123'<\/code>.<\/li>\n\n\n\nLEFT JOIN<\/code> with oc_product_description<\/code> to match extracted product_id<\/code> values.<\/li>\n\n\n\npd.product_id IS NULL<\/code>: Filters out the product IDs that do not exist in oc_product_description<\/code>.<\/li>\n<\/ol>\n\n\n\nproduct_id<\/code>s that have an entry in oc_seo_url<\/code> but do not exist in oc_product_description<\/code>.<\/p>\n\n\n\n
\n\n\n\nFixing the Issue<\/h2>\n\n\n\n
Option 1: Manually Delete the Extra SEO URL<\/h3>\n\n\n\n
DELETE FROM oc_seo_url \nWHERE language_id = 1 \nAND query = 'product_id=EXTRA_ID';\n<\/code><\/pre>\n\n\n\nEXTRA_ID<\/code> with the actual product ID that should not be in oc_seo_url<\/code>.<\/p>\n\n\n\nOption 2: Remove All Extra Entries Automatically<\/h3>\n\n\n\n
DELETE su \nFROM oc_seo_url su\nLEFT JOIN oc_product_description pd \n ON SUBSTRING_INDEX(su.query, '=', -1) = pd.product_id \n AND pd.language_id = 1\nWHERE su.language_id = 1 \nAND su.query LIKE 'product_id=%' \nAND pd.product_id IS NULL;\n<\/code><\/pre>\n\n\n\n
\n\n\n\nConclusion<\/h2>\n\n\n\n
oc_seo_url<\/code> 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.<\/p>\n\n\n\n