Product Listing under Category List

[tessthepup]

Hi,

I have copied and changed the product_listing module to be an index_nested module so I can display products under sub categories.

What I would like to happen is it to only show products from the sub categories listed under the main category.

Could anyone suggest a change to this code to only show products from the sub categories

Code: Select all

$listing_sql = sprintf(<<<'EOSQL'
SELECT m.*, %s
 FROM
  products p
    LEFT JOIN specials s ON p.products_id = s.products_id
    INNER JOIN products_description pd ON p.products_id = pd.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
EOSQL
      , Product::COLUMNS);

// show the products of a specified manufacturer
      if (empty($_GET['manufacturers_id'])) {
// show the products in a given category
        if (isset($_GET['filter_id']) && !Text::is_empty($_GET['filter_id'])) {
// We are asked to show only a specific manufacturer
          $listing_sql .= sprintf(<<<'EOSQL'
    INNER JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id
    INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id
 WHERE p.products_status = 1 AND m.manufacturers_id = %d AND pd.language_id = %d AND p2c.categories_id = %d
EOSQL
          , (int)$_GET['filter_id'], (int)$_SESSION['languages_id'], (int)$current_category_id);
        } else {
// We show them all
          $listing_sql .= sprintf(<<<'EOSQL'
    INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id
    LEFT JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id
  WHERE p.products_status = 1 AND pd.language_id = %d AND p2c.categories_id = %d 
EOSQL
          , (int)$_SESSION['languages_id'], (int)$current_category_id);
        }
      } else {
        if (isset($_GET['filter_id']) && !Text::is_empty($_GET['filter_id'])) {
// We are asked to show only a specific category
          $listing_sql .= sprintf(<<<'EOSQL'
    INNER JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id
    INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id
  WHERE p.products_status = 1 AND m.manufacturers_id = %d AND pd.language_id = %d AND p2c.categories_id = %d
EOSQL
          , (int)$_GET['manufacturers_id'], (int)$_SESSION['languages_id'], (int)$_GET['filter_id']);
        } else {
// We show them all
          $listing_sql .= sprintf(<<<'EOSQL'
    INNER JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id
  WHERE p.products_status = 1 AND pd.language_id = %d AND m.manufacturers_id = %d
EOSQL
          , (int)$_SESSION['languages_id'], (int)$_GET['manufacturers_id']);
        }
      }

Thanks

[Kofod95]

Maybe change p2c.categories_id = %d to p2c.categories_id IN (" . Tree->get_descendants((int)$current_category_id) . ")

//Daniel

[tessthepup]

Maybe change p2c.categories_id = %d to p2c.categories_id IN (" . Tree->get_descendants((int)$current_category_id) . ")

//Daniel

Thanks Daniel but that did not work as no products were shown.

[Kofod95]

Did you change the first instance or the second or both?

//Daniel

[ecartz]

Code: Select all

p2c.categories_id IN (%s)

and

Code: Select all

implode(', ', array_map('intval', Guarantor::ensure_global('category_tree')->get_descendants((int)$current_category_id)))

replaces

Code: Select all

(int)$current_category_id

[Kofod95]

I was close enough to make me happy

//Daniel

[tessthepup]

@ecartz @Kofod95

Thanks guys, although this error happens

PHP Warning: DB: [1064] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(" . Tree->get_descendants(implode(', ', array_map('intval', Guarantor::ensur...' at line 7 from <SELECT COUNT(p.products_id) AS total FROM
products p
LEFT JOIN specials s ON p.products_id = s.products_id
INNER JOIN products_description pd ON p.products_id = pd.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 INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id
LEFT JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id
WHERE p.products_status = 1 AND pd.language_id = 1 AND p2c.categories_id IN (463) (" . Tree->get_descendants(implode(', ', array_map('intval', Guarantor::ensure_global('category_tree')->get_descendants((int)$current_category_id)))) . ") > in /home/***/public_html/includes/system/versioned/1.0.8.1/database_core.php on line 44

or did I just mess it up again lol

Code: Select all

// We show them all
          $listing_sql .= sprintf(<<<'EOSQL'
    INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id
    LEFT JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id
  WHERE p.products_status = 1 AND pd.language_id = %d AND p2c.categories_id IN (%s) (" . Tree->get_descendants(implode(', ', array_map('intval', Guarantor::ensure_global('category_tree')->get_descendants((int)$current_category_id)))) . ") 
EOSQL
          , (int)$_SESSION['languages_id'], (int)$current_category_id);

[Kofod95]

Code: Select all

// We show them all
          $listing_sql .= sprintf(<<<'EOSQL'
    INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id
    LEFT JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id
  WHERE p.products_status = 1 AND pd.language_id = %d AND p2c.categories_id IN (%s) (" . Tree->get_descendants(implode(', ', array_map('intval', Guarantor::ensure_global('category_tree')->get_descendants((int)$current_category_id)))) . ") 
EOSQL
          , (int)$_SESSION['languages_id'], (int)$current_category_id);

Should be

Code: Select all

// We show them all
          $listing_sql .= sprintf(<<<'EOSQL'
    INNER JOIN products_to_categories p2c ON p.products_id = p2c.products_id
    LEFT JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id
  WHERE p.products_status = 1 AND pd.language_id = %d AND p2c.categories_id IN (%s) 
EOSQL
          , (int)$_SESSION['languages_id'], implode(', ', array_map('intval', Guarantor::ensure_global('category_tree')->get_descendants((int)$current_category_id))));

//Daniel

[tessthepup]

@Kofod95

Thanks Daniel, getting this fatal error now

PHP Fatal error: Uncaught Error: Undefined constant "Tree" in

I have searched github but cant seem to find any reference to Tree

[Kofod95]

Sorry, copy error on my part. Last post edited.

//Daniel