Magento – How to run a SQL query against the database

In order to run a SQL query against the Magento database, you first need a resource model then a database connection.

 

7 thoughts on “Magento – How to run a SQL query against the database”

  1. Just so everyone else knows. To get rows of data do a while loop or similar.

    {syntaxhighlighter brush: php}

    $db = Mage::getSingleton(‘core/resource’)->getConnection(‘core_write’);

    // now $write is an instance of Zend_Db_Adapter_Abstract

    $result = $db->query(“select id,name from dealerdoc_categories“);

    if(!$result) {

    return FALSE;

    }

    $categories=array();

    while ($row = $result->fetch(PDO::FETCH_ASSOC)) {

    $categories[] = array(‘value’=>$row[‘id’],’label’=>$row[‘name’]);

    }

    {/syntaxhighlighter}

  2. here is my php test code…

    {syntaxhighlighter brush: php}

    < ?php getConnection('core_write'); ?>

    {/syntaxhighlighter}

    this is error message…

    Fatal error: Call to a member function getResourceModelInstance() on a non-object in /var/www/magento.homeunix.com/app/Mage.php on line 460

    help me….please….

  3. reposting…

    my code..is………..

    {syntaxhighlighter brush: php}

    error_reporting(E_ALL | E_STRICT);

    ini_set(‘display_errors’, 1);

    require_once “app/Mage.php”;

    $db = Mage::getResourceSingleton(‘core/resource’)->getConnection(‘core_write’);

    {/syntaxhighlighter}

  4. Fixed a couple problems in the above code:

    1. I added a limit of 10 to the statement so it doesn’t crash your browser if you have a lot of products

    2. Added “fetchAll” to the code so it fetches all the available rows (max 10 becuase of the above mentioned limit)

    3. Added support for prefixes if your tables have a predefined prefix.

    4. Changed to Mage::getSingleton(‘core/resource’). The above code is wrong.

    5. Also, we should be using core_read here instead of core_write since we’re only reading data, but we’ll keep using core_write since it works for both.

    {syntaxhighlighter brush: php}

    $db = Mage::getSingleton(‘core/resource’)->getConnection(‘core_write’);

    $table_prefix = Mage::getConfig()->getTablePrefix();

    $result = $db->query(“SELECT entity_id FROM {$table_prefix}catalog_product_entity LIMIT 10”);

    if(!$result) {

    return FALSE;

    }

    $rows = $result->fetchAll(PDO::FETCH_ASSOC);

    if(!$rows) {

    return FALSE;

    }

    print_r($rows);

    {/syntaxhighlighter}

    Check out the Sweet Tooth reward points extension at http://www.getsweettooth.com which using this code somewhere in the source.

    Cheers!

Leave a Reply

Your email address will not be published. Required fields are marked *