Magento, Help with SQL Query to get full product URL's

Magento, Help with SQL Query to get full product URL's

August 28, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Magento, Help with SQL Query to get full product URL's

Retrieving Full Product URLs from Magento Database

In this post, we’ll guide you through retrieving full product URLs directly from the Magento database using SQL queries outside of the site’s normal methods.

Understanding the Required Tables

The catalog_product_entity table in your Magento database stores essential information about each product. To get a comprehensive list including URLs, we need to join this table with others like core_url_rewrite.

Here are the tables involved:

  • catalog_product_entity: Stores basic product details (sku, name, description)
  • core_url_rewrite: Stores rewritten URLs for products
  • catalog_category_entity (optional): To get category paths if needed

Building Your SQL Query

Let’s start with a simple query that retrieves the necessary fields:

SELECT
    ce.entity_id,
    ce.sku,
    ce.name,
    ce.description,
    uu.url_path
FROM
    catalog_product_entity AS ce
JOIN
    core_url_rewrite AS uu ON ce.entity_id = uu.product_id AND uu.target_path LIKE 'catalog/product/%'

This query joins the catalog_product_entity table (aliased as ce) with the core_url_rewrite table (aliased as uu), filtering for rows where the target path starts with 'catalog/product/%'. It selects essential fields like entity_id, sku, name, description, and url_path.

Enhancing the Query

To improve SEO relevance, let’s include the category hierarchy:

SELECT
    ce.entity_id,
    ce.sku,
    ce.name,
    ce.description,
    uu.url_path,
    GROUP_CONCAT(cce.path SEPARATOR '/') AS category_tree
FROM
    catalog_product_entity AS ce
JOIN
    core_url_rewrite AS uu ON ce.entity_id = uu.product_id AND uu.target_path LIKE 'catalog/product/%'
LEFT JOIN
    catalog_category_entity_int AS ccei ON ce.entity_id = ccei.entity_id
LEFT JOIN
    catalog_category_entity AS cce ON ccei.category_id = cce.entity_id
GROUP BY
    ce.entity_id

This updated query joins the catalog_category_entity and catalog_category_entity_int tables to retrieve the category hierarchy. The GROUP_CONCAT function combines all category paths into a single string separated by ’/’ for each product.

Storing Results in a New Table

To store these results in a new table named seo_product_data, you can use an INSERT INTO statement:

INSERT INTO seo_product_data (product_id, sku, name, description, url_path, category_tree)
SELECT
    ce.entity_id,
    ce.sku,
    ce.name,
    ce.description,
    uu.url_path,
    GROUP_CONCAT(cce.path SEPARATOR '/') AS category_tree
FROM
    catalog_product_entity AS ce
JOIN
    core_url_rewrite AS uu ON ce.entity_id = uu.product_id AND uu.target_path LIKE 'catalog/product/%'
LEFT JOIN
    catalog_category_entity_int AS ccei ON ce.entity_id = ccei.entity_id
LEFT JOIN
    catalog_category_entity AS cce ON ccei.category_id = cce.entity_id
GROUP BY
    ce.entity_id

Conclusion

By leveraging these SQL queries, you can efficiently retrieve all necessary product data, including full URLs, and store them in a new table for SEO or PPC initiatives.

Remember to replace the table names with those from your specific Magento instance. Also, ensure you have proper permissions to execute these queries on your database server.

For more advanced Magento database operations, consider exploring tools like Laravel’s raw SQL queries and proper data migration techniques for production environments.

Happy coding!

Catalog Database SEO

Our network