Products missing from category view(only) in Magento after multistore .htaccess

Products missing from category view(only) in Magento after multistore .htaccess

July 31, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Products missing from category view(only) in Magento after multistore .htaccess

Solving Magento Product Visibility Issues After Multistore .htaccess Setup

Hello, fellow Magento developers! I’m here to help you troubleshoot an issue that many of us have faced when setting up multistore environments: products disappearing from category views after implementing the .htaccess code for multistore.

The Problem at Hand

Let’s break down the scenario presented by our developer:

  • An existing Magento website with version 1.12.0.2
  • A new storefront being added via a subdomain (e.g., second-store.my-domain.com)
  • Products are available when accessed directly via URL (e.g., my-domain.com/category/product) but disappear from category views after implementing the multistore .htaccess code

Here’s the relevant .htaccess code for reference:

## Storefronts

# Main Store
SetEnvIf Host www\.my-domain\.com MAGE_RUN_CODE=main-store_code
SetEnvIf Host www\.my-domain\.com MAGE_RUN_TYPE=website
SetEnvIf Host ^my-domain\.com MAGE_RUN_CODE=main-store_code
SetEnvIf Host ^my-domain\.com MAGE_RUN_TYPE=website

# Second Store
SetEnvIf Host second-store\.my-domain\.com MAGE_RUN_CODE=second_store_code
SetEnvIf Host second-store\.my-domain\.com MAGE_RUN_TYPE=website

The Investigation So Far

The developer has already explored several troubleshooting steps, including:

  • Verifying that products are assigned to the proper category and store in Catalog
  • Ensuring that products are enabled and have stock available
  • Checking database group settings for USER_NOT_LOGGED_IN (set to 0)
  • Reindexing and clearing cache multiple times

Despite these efforts, they still encounter a “There are no products matching the selection.” error in category views.

Diving Deeper into the Database

The developer shared some SQL queries from the list.php troubleshooting process:

-- Base store (1) query
SELECT e.*, cat_index.position AS cat_index_position, price_index.price, price_index.tax_class_id, price_index.final_price,
IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price)
AS minimal_price, price_index.min_price, price_index.max_price, price_index.tier_price
FROM catalog_product_entity AS e
INNER JOIN catalog_category_product_index AS cat_index ON cat_index.product_id=e.entity_id AND cat_index.store_id=1
AND cat_index.visibility IN(2, 4) AND cat_index.category_id='3'
INNER JOIN catalog_product_index_price AS price_index ON price_index.entity_id = e.entity_id
AND price_index.website_id = '1' AND price_index.customer_group_id = 0 ORDER BY cat_index.position ASC LIMIT 10

-- Store 2 query with issues
SELECT e.*, cat_index.position AS cat_index_position, price_index.price, price_index.tax_class_id,
price_index.final_price, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tie
r_price), price_index.min_price) AS minimal_price, price_index.min_price, price_index.max_price,
price_index.tie
r_price FROM catalog_product_entity AS e
INNER JOIN catalog_category_product_index AS cat_index ON cat_index.product_id=e.entity_id AND cat_index.s
tore_id=2
AND cat_index.visibility IN(2, 4) AND cat_index.category_id='3'
INNER JOIN catalog_product_index_price AS price_index ON price_index.entity_id = e.entity_id
AND price_index.website_id = '2' AND price_index.customer_group_id = 0 AND price_index.stock_id = 15 ORDER
BY cat_index.position ASC LIMIT 10

-- Store 3 query with issues
SELECT e.*, cat_index.position AS cat_index_position, price_index.price, price_index.tax_class_id,
price_index.final_price, IF(price_index.tie
r_price IS NOT NULL, LEAST(price_index.min_price, price_index.tie
r_price), price_index.min_price) AS minimal_price, price_index.min_price, price_index.max_price,
price_index.tie
r_price FROM catalog_product_entity AS e
INNER JOIN catalog_category_product_index AS cat_index ON cat_index.product_id=e.entity_id AND cat_in
dex.store_id=3
AND cat_index.visibility IN(2, 4) AND cat_index.category_id='37'
INNER JOIN catalog_product_index_price AS price_index ON price_index.entity_id = e.entity_id
AND price_index.website_id = '2' AND price_index.customer_group_id = 0 AND price_index.stock_id = 1 ORDER
BY cat_index.position ASC LIMIT 9

The culprit: Missing Products in catalog_product_index_price table

Upon comparing the queries and playing around with them, I discovered that the problem lies within the catalog_product_index_price table. It appears to be missing 90% of products for stores other than store ID 1.

The developer mentioned they’ve tried various database repair tools without success. This suggests a data inconsistency issue rather than a technical one.

The Solution: Recreate Indexes and Validate Data Integrity

To resolve this, I recommend following these steps:

  1. Backup your current database to prevent any possible data loss.
  2. Open the Magento admin panel and navigate to System > Index Management.
  3. In the ‘Status’ column, ensure all indexes are set to ‘Reindex required.’ If not, manually reindex them.
  4. Go to System > Configuration > Catalog > Frontend.
  5. Set the following options:
    • Use Flat Catalog Category: Yes
    • Use Flat Catalog Product: Yes
  6. Save your changes and flush the cache (System > Cache Management).
  7. Close the admin panel, open MySQL terminal, and run the following commands:
DROP INDEX ON catalog_product_index_price;
CREATE INDEX idx_catalog_product_index_price_product_id ON catalog_product_index_price (entity_id);
  1. Restart Apache or the web server.
  2. Reindex all Magento indexes again.

If the issue persists after these steps, I would recommend engaging a professional Magento support service like the one offered by our team at Laravel Company. Our experts have extensive experience in troubleshooting complex multistore setups and can provide tailored solutions for your specific environment.

Conclusion

In summary, the disappearance of products from category views after implementing the .htaccess code for multistore is often caused by data inconsistencies within Magento’s index tables. By following the steps outlined above—backup, reindexing, and recreating indexes—you should be able to resolve the issue and restore product visibility in your category views.

Remember that Magento is a complex system with many moving parts, and troubleshooting these types of issues often requires a deep understanding of its database structure and indexing mechanisms. If you find yourself stuck or dealing with an especially challenging scenario, don’t hesitate to reach out to a professional support service for expert guidance.

Happy coding! Let me know if you need any further assistance.

Best, [Your Name] Senior Developer & Technical Blogger Laravel Company

Catalog Database Configuration Errors

Our network