Unlock Granular Sales Insights: Custom MySQL Queries for PrestaShop 'Payment Received' Reports

Unlock Granular Sales Insights: Custom MySQL Queries for PrestaShop 'Payment Received' Reports

As an e-commerce merchant, having a clear, real-time understanding of your sales data is paramount. While PrestaShop offers a robust suite of built-in reports, specific business needs often demand more granular or highly customized insights. What happens when a crucial reporting module becomes outdated, leaving you in the dark about vital metrics?

This was precisely the dilemma faced by a PrestaShop merchant on the official forums. Their once-brilliant module, designed to list products and quantities for 'Payment Received' orders within a specific date range, had become obsolete. This common scenario highlights a critical skill for any serious PrestaShop store owner or developer: the ability to directly query your database for custom reports.

The Challenge: When Modules Fall Short

The PrestaShop ecosystem thrives on modules, offering extended functionality for virtually every aspect of your store. However, reliance on third-party modules comes with its own set of challenges:

• Maintenance Issues: Developers may cease support, leaving modules incompatible with newer PrestaShop versions or PHP updates.
• Specific Needs: Off-the-shelf modules might not perfectly match your unique reporting requirements.
• Performance: Some modules can be resource-intensive, slowing down your back office.

In such cases, understanding the underlying PrestaShop database structure and crafting custom MySQL queries becomes an invaluable asset. It empowers you to extract precisely the data you need, on your terms.

Accessing Your PrestaShop Database: The Gateway to Data

Before you can run any queries, you need access to your PrestaShop database. The most common tools for this are:

• phpMyAdmin: A web-based interface provided by most hosting providers, offering a comprehensive way to manage MySQL databases.
• PrestaShop SQL Manager: For some PrestaShop versions, there might be a basic SQL manager module available in the back office, though phpMyAdmin offers more power and flexibility.
• Command Line: For advanced users, direct command-line access via SSH provides the most control.

Important Pre-Query Steps:

1. Backup Your Database: Always, always, ALWAYS create a full backup of your database before executing any custom queries, especially if you're new to this. A single mistake can lead to data loss.
2. Use a Staging Environment: Ideally, test all queries on a development or staging site first, never directly on your live production store.

Step-by-Step: Crafting Your Custom 'Payment Received' Sales Report

The solution provided in the forum thread offers a robust two-step approach to generate the desired report. Let's break it down.

Step 1: Identify the 'Payment Received' Order Status ID(s)

PrestaShop manages order statuses (e.g., 'Payment accepted', 'Shipped', 'Canceled') using unique numerical IDs. These IDs can vary slightly depending on your PrestaShop version or if you've customized your order statuses. The first step is to find the ID(s) corresponding to 'Payment Received' or similar statuses.

SELECT id_order_state, name FROM ps_order_state_lang WHERE name LIKE '%Payment%' OR name LIKE '%Received%';

Explanation:

• ps_order_state_lang: This table stores the names of your order statuses in different languages.
• id_order_state: The unique identifier for each order status.
• name: The human-readable name of the order status.
• WHERE name LIKE '%Payment%' OR name LIKE '%Received%': This condition filters for any status names containing 'Payment' or 'Received', ensuring you catch variations like 'Payment accepted', 'Payment received', etc.

Run this query. Note down the id_order_state value(s) that accurately represent 'Payment Received' for your store. You might get multiple IDs if you have different payment-related statuses.

Step 2: Construct the Core Sales Report Query

Once you have the correct id_order_state, you can build the main query to retrieve product details and their total quantities for orders with that status within your specified date range.

SELECT 
    od.product_id, 
    od.product_reference, 
    od.product_name, 
    SUM(od.product_quantity) AS qty_sold 
FROM 
    ps_orders o 
JOIN 
    ps_order_detail od ON od.id_order = o.id_order 
WHERE 
    o.current_state IN (/* put id_order_state(s) here */) 
    AND o.date_add >= '2026-01-01 00:00:00' 
    AND o.date_add <= '2026-01-31 23:59:59' 
GROUP BY 
    od.product_id, od.product_reference, od.product_name 
ORDER BY 
    qty_sold DESC;

Explanation of the Query:

• SELECT od.product_id, od.product_reference, od.product_name, SUM(od.product_quantity) AS qty_sold: This selects the product ID, reference, name, and calculates the total quantity sold (SUM(od.product_quantity)) for each product, aliasing it as qty_sold.
• FROM ps_orders o JOIN ps_order_detail od ON od.id_order = o.id_order: This joins the ps_orders table (containing general order information) with the ps_order_detail table (containing individual product details within each order) using the common id_order field.
• WHERE o.current_state IN (/* put id_order_state(s) here */): This is the crucial filter. Replace /* put id_order_state(s) here */ with the actual ID(s) you found in Step 1 (e.g., IN (2, 5) if you have multiple IDs).
• AND o.date_add >= '2026-01-01 00:00:00' AND o.date_add <= '2026-01-31 23:59:59': This filters orders by their creation date (date_add) within your desired range. Remember to adjust the dates to your specific reporting period.
• GROUP BY od.product_id, od.product_reference, od.product_name: This groups the results so that quantities for the same product are summed together.
• ORDER BY qty_sold DESC: This sorts the results, showing the best-selling products (by quantity) first.

Customization and Further Refinements

This query is a powerful starting point. You can customize it further:

• Different Date Ranges: Easily adjust the date_add conditions.
• Specific Products: Add another AND condition like AND od.product_id = 123 or AND od.product_reference = 'SKU456'.
• Other Order Statuses: Change the current_state IN (...) to report on 'Shipped' orders, 'Pending' orders, etc.
• Customer Information: Join with ps_customer table (c.id_customer = o.id_customer) to include customer details.
• Price Information: Include od.unit_price_tax_incl or od.total_price_tax_incl to calculate revenue.

Beyond the Query: What to Do with Your Data

Once you execute the query, you'll get a table of results. Most database management tools (like phpMyAdmin) allow you to export these results directly to various formats, such as CSV, Excel, or PDF. This exported data can then be used for:

• Spreadsheet Analysis: Dive deeper into trends, create charts, and compare performance.
• Custom Dashboards: Integrate into external reporting tools or build a simple custom module to display this data in your PrestaShop back office.
• Inventory Management: Inform purchasing decisions based on actual sales velocity for paid orders.

Migrate My Shop's Perspective: Database Knowledge and Migrations

At Migrate My Shop, we understand that a deep knowledge of your PrestaShop database is not just for reporting; it's fundamental to successful e-commerce migrations. When moving from an older PrestaShop version to a newer one, or even to a different platform, understanding how your data is structured and stored is critical. Custom queries like these help us:

• Validate Data Integrity: Ensure all essential data is correctly transferred.
• Identify Customizations: Pinpoint unique data structures from custom modules that need special handling.
• Clean Data: Identify and remove obsolete or redundant data before migration, leading to a cleaner, faster new store.

Whether you're looking to optimize your current PrestaShop store or planning a seamless migration, mastering your database is a powerful asset.

Conclusion

While PrestaShop modules offer convenience, the ability to craft custom MySQL queries provides unparalleled flexibility and control over your store's data. The forum thread's solution is a perfect example of how direct database interaction can solve specific business problems when off-the-shelf solutions fall short. By following these steps, you can generate precise sales reports, gain deeper insights into your 'Payment Received' orders, and make more informed decisions for your e-commerce business.

If you find yourself needing complex data extractions, custom integrations, or are considering a PrestaShop migration, don't hesitate to reach out to the experts at Migrate My Shop. We specialize in ensuring your data is accurate, accessible, and ready to power your e-commerce success.