Magento 1.9 – Essential SQL Queries

databasemagento-1.9sql

I am in the process of setting up some reporting functionality for my company, Im gathering data from the likes of Google, Moz and our Courier. As part of the reporting I also want to get data from Magento. As this will be hosted in a very secure folder on our server. What I would like to know is what is the safest way for me to run queries on the Magento Data?

I could run

  • Direct SQL queries outside of Magento

  • SQL queries inside Magento but then would have issues getting them out of Magento automatically

  • Magento API

Which am I best doing from a safety and performance point of view for my website?

Best Answer

Yes you can run direct sql queries within Magento, the best way to do this is to use the read write resource. You can insatiate it with:

    $resource = Mage::getSingleton('core/resource');

    $readConnection = $resource->getConnection('core_read');

    $writeConnection = $resource->getConnection('core_write');

To run a select you can do something like this: 

    $readConnection = $resource->getConnection('core_read');

    $query = 'SELECT * FROM ' . $resource->getTableName('catalog/product');

    $results = $readConnection->fetchAll($query);

    /* get the results */
    var_dump($results);

To write something to the database use:

    $resource = Mage::getSingleton('core/resource');

    $writeConnection = $resource->getConnection('core_write');

    $table = $resource->getTableName('catalog/product');

    $query = "UPDATE {$table} SET {item} = '{value}' WHERE entity_id = 'value'";

    $writeConnection->query($query);

Hope this helps out for you.

Related Topic