user3103915 user3103915 - 5 months ago 37
SQL Question

Build an sql query for all enabled products in magento database

I am new to magento and I want to fetch enabled products from the database.The fields I require are:

Name
of the product:
PRICE
:
SKU
: and
Quantity
.

I don't have much idea regarding the magento table structure. Is it possible from the admin panel or if someone has a sql query it would be really helpful. Thanks

Answer

Using Backend:

You can Export all enabled Products from admin. You can export this from Top Navigation System >> Configuration >> Import/Export >> Dataflow - Profiles.

In Dataflow - Profiles, you will see Export Product Stocks Profile. you can set Required Fields in Field Mapping to export Products with required attribute.

Using Code:

You can create stand alone script in your magento installation root directory and add the below code:

define('MAGENTO_ROOT', getcwd());
$mageFilename = MAGENTO_ROOT . '/app/Mage.php';
require_once $mageFilename;
Mage::app();

$productCollection = Mage::getModel('catalog/product')->getCollection();
$productCollection->addAttributeToSelect('name');
$productCollection->addAttributeToSelect('price');
$productCollection->addAttributeToSelect('sku');
$productCollection->addAttributeToSelect('qty');

Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($productCollection);


foreach($productCollection as $_product) {

    $qty = (int)Mage::getModel('cataloginventory/stock_item')->loadByProduct($_product)->getQty();

    echo $_product->getName() .'<br />';
    echo $_product->getSku() .'<br />';
    echo $_product->getPrice() .'<br />';   
    echo $qty .'<br />';
    echo '<hr />';
}