How to connect to the database in Magento?

Question

I'm trying to build a simple Magento Module that needs to connect to the database at the start of each page request. All tables need to be accessible to it. I've been pulling my hair out trying to understand how to do it. The best I could figure out was that I need to set this in the config.xml file of my module, but exactly what that command is/how it may be used, i haven't been able to figure out.

Can someone please guide me or show me how to accomplish this? If not, would it be too bad a practice to include a custom config.php file which connects to the database manually?

Answer 1

Are you trying to use a resource model or entity ? here is how you can query a table using raw SQL but you need to know the tablename.

$w = Mage::getSingleton(’core/resource’)->getConnection(’core_write’);
$result = $w->query(’select ’entity_id’ from ’catalog_product_entity’);

if (!$result) {
return false;
}

$row = $result->fetch(PDO::FETCH_ASSOC);
if (!$row) {
return false;
}

When trying to all a model you can use

$products = Mage::getModel(’catalog/product’)->getCollection();

$products->addAttributeToFilter(’entity_id’,
array(’in’=> array(1,2) )
);

$products->addAttributeToSelect(’*’);
$products->load();

foreach($products as $_prod) {
var_dump($_prod->getData());
}

I use the second method for my custom modules and it has worked out fine for me , hope this answer helps :)

Answer 2

The second one is worked well for me also, actually i surfed this code in net on vary long time but i cant to get, but unfortunatley i got this one. very thanks for Rick J

Answer 3

What about objects that do not have addAttributeToFilter? I've been trying for hours to load a role object by its name and nothing works. The only possible method is load, which would work perfectly if not for an unexplicable

if (!intval($value) && is_string($value)) { $field = 'role_id'; }

in Mage_Admin_Model_Mysql4_Role::load, which only makes it impossible to filter using a string value.

Magento has a Rube Goldberg architecture...

Answer 4

I hvve some eror , when I m trying to write this query with having clause its give me error of sqlstate column not found

$collection = Mage::getResourceModel('sales/order_collection')
        ->addAttributeToSelect('*')
        ->joinAttribute('billing_firstname', 'order_address/firstname', 'billing_address_id', null, 'left')
        ->joinAttribute('billing_lastname', 'order_address/lastname', 'billing_address_id', null, 'left')
        ->joinAttribute('shipping_firstname', 'order_address/firstname', 'shipping_address_id', null, 'left')
        ->joinAttribute('shipping_lastname', 'order_address/lastname', 'shipping_address_id', null, 'left')
        ->addExpressionAttributeToSelect('billing_name',
            'CONCAT({{billing_firstname}}, " ", {{billing_lastname}})',
            array('billing_firstname', 'billing_lastname'))
        ->addExpressionAttributeToSelect('shipping_name',
            'CONCAT({{shipping_firstname}}, " ", {{shipping_lastname}})',
            array('shipping_firstname', 'shipping_lastname'))
        ->joinAttribute('shipping_country_id', 'order_address/country_id', 'shipping_address_id', null, 'left')
        ->addExpressionAttributeToSelect('international',
            '("US" = {{shipping_country_id}})',
        array('shipping_country_id'))

        ->joinField('item_product_id', 'sales/order_item', 'product_id', 'order_id=entity_id', null, 'left')
        ->groupByAttribute('entity_id');

    // Add the presale column
    $collection->getSelect()->joinLeft(
            array('product_presale'=>'catalog_product_entity_int'),
            'product_presale.entity_id=`_table_item_product_id`.`product_id` and product_presale.attribute_id='.$productattribute->getAttributeId(),
            array('presale_item_count'=>new Zend_Db_Expr('SUM(product_presale.value)'))
        );

but when I dump that query and run on phpmyadmin it works fine

Any help

Sajid

Answer 5

I am try to build a simple drop down list for my product sizes; and it have to get a record from database in mageto.

Example Product A have 3 size, now my page show all three, even picture is the same size and description is the same. I want it shows only one with one photo and have a dropdown list size for browser to choose a size after select the size it will go direct to the product detials and shoping.

Could anyone help me please?

Thanks.