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 productscatalog_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!