Magento Direct SQL Queries

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”

  1. 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.
    Cheers,
    Naveed.

Leave a Reply

Your email address will not be published.