Magento 1.9 – Fetch Details from catalog_product_flat_1 Table

catalogcollection;magento-1.9moduleproduct

In catalog_product_flat_1 table, we created a custom column custom_uid & saving customer ids

Assume below image is the catalog_product_flat_1 table. You can see the columns contain Product name and customer id.

enter image description here

We are trying to fetch Product information from catalog_product_flat_1 table & trying to display in My account section. but its not fetching.

Means in customer with id 101's account, we are trying to display Product 1 & product2 . & in in customer id 102's account, we are trying to display Product 3

app/code/local/Amasty/Example/Block – List.php

class Amasty_Example_Block_List extends Mage_Core_Block_Template
{
 public function __construct() {
        parent::__construct();
        $idc = Mage::getSingleton('customer/session')->getId();
     $customerSavedProductArray = array();
     $customerSavedProductArray = Mage::getModel('catalog/product')->getCollection()->addFieldToFilter('custom_uid',$idc)->getColumnValues("name");
//  $customerSavedProductArray = Mage::getResourceModel('catalog/product_collection')->addFieldToFilter('custom_uid',$idc)->getColumnValues("name");
    $collection = Mage::getModel('catalog/product')->getCollection()->addAttributeToSelect('*')->addAttributeToFilter('entity_id', array("in"=>$customerSavedProductArray));

    $collection->load();
    $this->setCollection($collection);
    }

phtml

<?php echo $this->getMessagesBlock()->getGroupedHtml() ?>
<?php $collections = $this->getCollection(); ?>
<?php $_product = $this->getProduct(); ?>


<?php foreach ($collections as $key => $product) {

    echo $product->getName().' <br><br>';
    echo $product->getId().'<br><br>';  
    $_product = Mage::getModel('catalog/product')->load($product->getId());  

?>

update

In config.xml, i used table name : catalog_product_flat_1

<?xml version="1.0"?>
<config>
    <modules>
        <Amasty_Example>
            <version>0.1.0</version>
        </Amasty_Example>
    </modules>
    <frontend>
        <routers>
            <example>
                <use>standard</use>
                <args>
                    <module>Amasty_Example</module>
                    <frontName>example</frontName>
                </args>
            </example>
        </routers>
        <layout>
            <updates>
                <example>
                    <file>example.xml</file>
                </example>
            </updates>
        </layout>
        <events>

        <controller_action_predispatch>
            <observers>
                <Amasty_Example>
                    <class>example/observer</class>
                    <method>onControllerActionPredispatch</method>
                </Amasty_Example>
            </observers>
        </controller_action_predispatch>
        </events>       
    </frontend>


    <global>
        <models>
            <example>
                <class>Amasty_Example_Model</class>
                <resourceModel>example_mysql4</resourceModel>
            </example>
            <example_mysql4>
                <class>Amasty_Example_Model_Mysql4</class>
                <entities>
                    <example>
                        <table>example</table>
                    </example>
                     <mydesign>
                        <table>catalog_product_flat_1</table>
                    </mydesign>
                </entities>
            </example_mysql4>
        </models>
        <resources>
            <example_setup>
                <setup>
                    <module>Amasty_Example</module>
                </setup>
                <connection>
                    <use>core_setup</use>
                </connection>
            </example_setup>
            <example_write>
                <connection>
                    <use>core_write</use>
                </connection>
            </example_write>
            <example_read>
                <connection>
                    <use>core_read</use>
                </connection>
            </example_read>
        </resources>

    </global>
</config>

Best Answer

First of all, you are doing it wrong.
You should never manually add columns to any catalog_product_flat_* table.
These tables is just an index of the active products for each store view.
When you rebuild the indexes, these tables are regenerated. If you add a column to it you will lose it on the next reindex.
You should add a product attribute called custom_uid with the flag used in product listing set to yes.
Then reindex everything.
After that you can get the products collection for a specific customer like this: 

$customerId = 8; //your customer id here - it can come from session
$collection = Mage::getModel('catalog/product')->
    ->addAttributeToSelect('*') // instead of * you can put an array with only the attributes you need
    ->getCollection()->addAttributeToFilter('custom_uid', $customerId);

Then you can simply loop through the collection and do something with each product, but don't use load in the foreach loop. 

foreach ($collection as $product) {
    //do something with $product
}
Related Topic