Trouble with featured products and others (Dr Byte - Linda or others??)

A couple years ago worked with Linda on an issue we were having

We wanted the following to happen:

On the featured products (and some others) we wanted only those products that had at least quantity 1 in stock to be displayed in the featured products results and featured products box and not those items that had quantity 0.

We have all of our products remain in the catalog even when they are sold out but only have them DISPLAYED if they have quantity 1 or higher. If doing a search, you can still view the products that are sold out and if you have the direct URL you can view them but they are not viewable in the general browsing catalog or specials, new products and featured products pages.

The solution we were provided (back on version 1.2.25) was to go into (we will only talk about the featured products now - but it is the same for the others as well) /includes/modules/featured products.php and make the queries look like this

Code:

---

$title = TABLE_HEADING_FEATURED_PRODUCTS;

  if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {
    $featured_products_query = "select distinct p.products_id, p.products_image, pd.products_name
                          from " . TABLE_PRODUCTS . " p
                          left join " . TABLE_FEATURED . " f on p.products_id = f.products_id
                          left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id
                          where p.products_id = f.products_id and p.products_quantity >= '1' and p.products_id = pd.products_id and p.products_status = '1' and f.status = '1' and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'";
  } else {
    $featured_products_query = "select distinct p.products_id, p.products_image, pd.products_name
                          from " . TABLE_PRODUCTS . " p
                          left join " . TABLE_FEATURED . " f on p.products_id = f.products_id
                          left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id, " .
                              TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " .
                              TABLE_CATEGORIES . " c
                          where p.products_id = p2c.products_id
                                  and p.products_quantity >= '1'
                          and p2c.categories_id = c.categories_id
                          and c.parent_id = '" . (int)$new_products_category_id . "'
                          and p.products_id = f.products_id and p.products_id = pd.products_id and p.products_status = '1' and f.status = '1' and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'";

---

You will notice that we added the p.products_quantity >= '1' in a couple of places which basically is saying to only display products that are equal to or greater than quantity one.

We did this same sort of thing in the file /includes/sideboxes/featured.php

Code:

---

$show_featured= true;

  if ($show_featured == true) {
    $random_featured_products_query = "select p.products_id, p.products_image, pd.products_name
                          from " . TABLE_PRODUCTS . " p
                          left join " . TABLE_FEATURED . " f on p.products_id = f.products_id
                          left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id
                          where p.products_id = f.products_id and p.products_quantity >= '1' and p.products_id = pd.products_id and p.products_status = '1' and f.status = '1' and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
                          order by pd.products_name desc
                          limit " . MAX_RANDOM_SELECT_FEATURED_PRODUCTS;

---

This was the query for the featured sidebox which basically did the same thing.

Well now there is an issue - working with the new version 1.3.7 and making these same query changes in the same files - it appears that this no longer works and we are not quite sure on why.

Can Dr Byte or Linda - or perhaps someone else advise why? We know there have been quite a few changes in the code but not sure why this is apparently affected or what we are missing.

Check that you updated the files correctly ...

If using an overrides file that file needs to be changed ...

Quote:

---

Originally Posted by Ajeh

Check that you updated the files correctly ...

If using an overrides file that file needs to be changed ...

---

Linda - we have done the upgrade in simplier manner

1. Copied our existing database
2. Fresh install of 1.3.37 into a test installation directory
3. Deleted all newly created tables from new installation
4. Restored our existing database tables to new database
5. Ran database update to database structure from old existing version to 1.3.37

Everything is working fine in our test installation as we are only using the FRESHLY installed files - but when going into the new files and making these query changes, the featured products sidebox and featured products listing is still showing all the OUT OF STOCK (less that quantity 1) products in that list.

Thanks

I am not sure where you are missing the boat on this ...

I add to any of the WHERE statements:
and p.products_quantity >= 1

And I do not see any Products with quantities of less than 1

Check your two configure.php files ...

Both your Catalog and Admin are looking at the same database and paths etc. correct?

Quote:

---

Originally Posted by Ajeh

I am not sure where you are missing the boat on this ...

I add to any of the WHERE statements:
and p.products_quantity >= 1

And I do not see any Products with quantities of less than 1

Check your two configure.php files ...

Both your Catalog and Admin are looking at the same database and paths etc. correct?

---

Ah, thanks for your help... It appeared forgot to edit the queries in the header file in the featured products folder in pages.

That solved it.

Thanks Linda

Glad to of been of help ... hope this solves all of your dilemas ... :smile: