Queries for fetching in an array
/** * Get the resource model */ $resource = Mage::getSingleton('core/resource'); /** * Retrieve the read connection */ $readConnection = $resource->getConnection('core_read'); $query = 'SELECT * FROM ' . $resource->getTableName('catalog/product'); /** * Execute the query and store the results in $results */ $results = $readConnection->fetchAll($query); /** * Print out the results */ var_dump($results);
Fetching one query
Therefore use the method fetchCol() instead of fetchAll, so like:
$sku = $readConnection->fetchCol('SELECT sku FROM ' . $table . ');
Read more at FishPig.
Queries for wrtiting
$resource = Mage::getSingleton('core/resource'); $writeConnection = $resource->getConnection('core_write'); $query = "INSERT INTO abc_hit SET visitor_id = :visitor_id, hit_date = :hit_date, "; $binds = array( 'visitor_id' => $_SESSION['visitor_id'], 'hit_date' => date("Y-m-d H:i:s"), ); $writeConnection->query($query, $binds);
Get Last Inserted ID
$write = Mage::getSingleton('core/resource')->getConnection('core_write'); $sql = "INSERT INTO ......."; $write->query($sql); $lastInsertId = $write->lastInsertId();
Thanks to sbditto85.
2 thoughts on “Magento Direct SQL Queries”
Many thanks for the above article. I am experienced PHP developer, that I can understand the above code but I am new to magento therefore don’t know how to run the above code in Magento Context. So, it would be great if you write the above article in a way to write in Magento from start to finish then it would be great for the newbies like me. Hope to get the updated article soon.