Need a bit of sql help with Back in Stock page

[14Steve14]

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.

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';

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

Code: Select all

ORDER BY p.products_restocked_date DESC
EOSQL

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.

[azpro]

Only quick look but maybe you forgot to include products_table p?

instead

Code: Select all

SELECT m.*, %s

try

Code: Select all

SELECT p.*, m.*, %s

[BrockleyJohn]

On the face of it, that should be ok. However the actual SQL that's running clearly isn't.

Maybe you have another ORDER BY getting appended later on - echo out the final SQL maybe.

Another place where queries break is when split page results counts the number of results.

[14Steve14]

Sorry for the delay in replying.

I will look into this further over the weekend. The page shows as it should and seems to be working so there cannot be a lot wrong with it. I may even leave it as it is.