Need a bit of sql help with Back in Stock page
Posted: Sat Aug 23, 2025 9:54 am
I have created a page that shows products that are back in stock, Products show if their stock figures have been increased. They show for 30 days. The code his basically the featured products page code modified to show updated stock items.
I have this currently as the sql to show the products which works well and allows sorting using the standard CE Phoenix sort listings dropdowns.
What I want it to do is to initially show the products listed with newest updated first, but to also allow the standard sort to work if required.
I have tried adding to the bottom but that just threw a fatal error related to the sql above.
Can anyone suggest an improvement to that sql or a change that would work.
I have this currently as the sql to show the products which works well and allows sorting using the standard CE Phoenix sort listings dropdowns.
Code: Select all
$listing_sql = sprintf(<<<'EOSQL'
SELECT m.*, %s
FROM
products_description pd
INNER JOIN products p ON p.products_id = pd.products_id
LEFT JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id
LEFT JOIN specials s ON p.products_id = s.products_id
LEFT JOIN (SELECT products_id, COUNT(*) AS attribute_count FROM products_attributes GROUP BY products_id) a ON p.products_id = a.products_id
WHERE p.products_status = 1
AND p.products_quantity > 0
AND p.products_restocked_date IS NOT NULL
AND p.products_restocked_date >= NOW() - INTERVAL %d DAY
AND pd.language_id = %d
EOSQL
, Product::COLUMNS, (int)$days_limit, (int)$_SESSION['languages_id']);
$default_column = 'PRODUCT_LIST_ID';
$sort_order = 'd';I have tried adding to the bottom
Code: Select all
ORDER BY p.products_restocked_date DESC
EOSQLCan anyone suggest an improvement to that sql or a change that would work.