Magento – Products exist in the database but are not showing in backend or frontend

ce-1.9.1.0magento-1.9product

In the Magento back-end interface, when viewing Catalog / Manage Products, the list of products is empty.

In addition, in the Magento front-end interface, when viewing any list of products, such as a category, the list of products is empty:

http://example.com/category.html

However, I am able to view individual products in the front-end interface by typing in the url for the individual product:

http://example.com/product.html

In addition, I am able to verify that the correct number of products exist in the database using:

select count(*) from `catalog_product_entity`;

Between when the products were showing and when they disappeared, I had done a System / Import / Dataflow Profiles / Import All Products from a CSV containing data like the following:

sku, status
101, enabled
102, disabled

The import did not throw any errors, but the status field did not understand the use of enabled / disabled, so I imported again from a CSV containing data like the following:

sku, status
101, 1
102, 0

Again, the import did not throw any errors, but the values for the status field were still not correct, so after reference to http://www.magentocommerce.com/knowledge-base/entry/working-with-product-csv-files I imported again from a CSV containing data like the following:

sku, status
101, 1
102, 2

Once again, the import completed without throwing any errors. But even after indexing, none of the products now display in any list of products.

I also tried setting visibility by importing from a CSV containing data like:

sku, visibility
101, 4
102, 4

Again, the import completed without throwing any errors. But still, none of the products display in any list of products.

Running System / Import / Dataflow Profiles / Export All Products to CSV shows that the visibility for most records is currently Catalog / Search but for the subset of records where visibility was explicitly set to Catalog / Search by importing a CSV as above, the visibility is now set to Not Visible Individually. It appears that explicitly setting the visibility performs some internal validation that fails and then defaults the visibility to Not Visible Individually. What could cause this import validation to fail?

I believe this is not a duplicate of Products not showing up in frontend or backend but are enabled and stored correctly in flat table because the symptoms of the issue (not displaying in back-end vs not displaying in back-end or front-end) and the versions of Magento (1.5 vs 1.9) are significantly different.

Best Answer

I managed to reproduce the effect. If the status doesn't have the value "1" (enabled) or "2" (disabled), the product doesn't display at all.

You can fix this as follows. Create a PHP file in the base directory named "update_status.php". Paste the following contents:

<?php
require_once 'app/Mage.php';
Mage::app()->setCurrentStore(0);

$collection = Mage::getResourceModel('catalog/product_collection');
$collection->addAttributeToFilter('status', array(
    array('nin' => array(1,2)),
    array('null' => true)
), 'left');
foreach ($collection as $product) {
    $product->addAttributeUpdate('status', 1, 0);
}

This will set the status "1" to all products which don't have a correct status. Don't forget to delete the file afterwards.

Related Topic