Magento 1 SQL Queries

  • 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?

  • 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 */

    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'";

    Hope this helps out for you.

    Thanks @Kay do you know what the consequences would be of me querying the db outside of Magento?

    Not that much, its just not best practice. and you can risk inconsistencies but normally no problem. but now you keep it all tight in one workflow

    Where to find all of these queries ?

    Please keep in mind that writing to the database in this way introduces a **SQL injection vulnerability**. Only do this if you are _sure_ that your values are safe.

License under CC-BY-SA with attribution

Content dated before 7/24/2021 11:53 AM