Hi Guys,

This Magento tutorial explains about database connections through Magento core resource models.

By default, Magento has provided two resources to connect database to access data: core_read and core_write.

core_read is used for reading from the database.
core_write is used for writing to the database.

<?php
/**
 * Get the core resource model
 */
$resource = Mage::getSingleton('core/resource');

/**
 * Retrieve the read connection
 */
$readAdapter = $resource->getConnection('core_read');

/**
 * Retrieve the write connection
 */
$writeAdapter = $resource->getConnection('core_write');

/**
 * Get the table name
 */
$tableName = $resource->getTableName('catalog_product_entity');
OR
$tableName = $resource->getTableName('catalog/product');

?>

/*****************Reading From The Database***************/
1) To fetch all the rows as a result in array format, magento uses
    Varien_Db_Select::fetchAll
Ex.
<?php
/**
 * Get the resource model
 */
$resource = Mage::getSingleton('core/resource');

/**
 * Retrieve the read connection
 */
$readAdapter = $resource->getConnection('core_read');

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

/**
 * Execute the query and store the results in $results as an array
 */
$results = $readAdapter->fetchAll($query);

/**
 * Print out the results
 */
var_dump($results);
?>

2) To fetch the first column of each result row in array format, magento uses
   Varien_Db_Select::fetchCol
Ex.
<?php
/**
 * Get the resource model
 */
$resource = Mage::getSingleton('core/resource');

/**
 * Retrieve the read connection
 */
$readAdapter = $resource->getConnection('core_read');


/**
 * Retrieve our table name
 */
$tableName = $resource->getTableName('catalog/product');

/**
 * Execute the query and store the results in $results
 */
$results = $readAdapter->fetchCol('SELECT sku FROM ' . $tableName);

/**
 * Print out the results
 */
var_dump($results);
?>

 

3) To fetch the first column of each result row in array format, magento uses
   Varien_Db_Select::fetchOne
Ex.
<?php
/**
 * Get the resource model
 */
$resource = Mage::getSingleton('core/resource');

/**
 * Retrieve the read connection
 */
$readAdapter = $resource->getConnection('core_read');

/**
 * Retrieve our table name
 */
$tableName = $resource->getTableName('catalog/product');

/**
 * Set the product ID
 */
$productId = 10;

$query = 'SELECT sku FROM ' . $tableName . ' WHERE entity_id = '
    . (int)$productId . ' LIMIT 1';

/**
 * Execute the query and store the result in $sku as a value(not in array like fetchAll & fetchCol)
 */
$sku = $readAdapter->fetchOne($query);

/**
 * Print the product sku
 */
echo 'Product sku: ' . $sku;
?>

/*****************Writing To The Database***************/
1) To insert the new row to the table, you can use following test reference:
<?php
/**
 * Get the resource model
 */
$resource = Mage::getSingleton('core/resource');

/**
 * Retrieve the write connection
 */
$writeAdapter = $resource->getConnection('core_write');

/**
 * Retrieve our table name
 */
$tableName = $resource->getTableName('catalog/product');

/**
 * Set the test product ID and sku
 */
$productId = 10;
$newTestSku = 'new-test-sku';
$now = date("Y-m-d h:i:s"); //you can use now() also

// to update sku of concerned/related product id
$writeAdapter->insert(
    $tableName,
    array("entity_id" => $productId,
        "entity_type_id" => 4,
        "attribute_set_id" => 4,
        "sku" => $newTestSku,
        "type_id" => 'simple',
        "has_options" => 0,
        "required_options" => 0,
        "created_at" => $now,
        "updated_at" => $now)
);
?>

2) To update the existing row, you can use following test reference:
<?php
/**
 * Get the resource model
 */
$resource = Mage::getSingleton('core/resource');

/**
 * Retrieve the write connection
 */
$writeAdapter = $resource->getConnection('core_write');

/**
 * Retrieve our table name
 */
$tableName = $resource->getTableName('catalog/product');

/**
 * Set the test product ID and sku
 */
$productId = 10;
$newTestSku = 'new-test-sku';

// to update sku of concerned/related product id
$query = "UPDATE {$tableName} SET sku = '{$newTestSku}' WHERE entity_id = "
    . (int)$productId;

/**
 * Execute the query
 */
$writeAdapter->query($query);
?>

3) To delete the existing row, you can use following test reference:
<?php
/**
 * Get the resource model
 */
$resource = Mage::getSingleton('core/resource');

/**
 * Retrieve the write connection
 */
$writeAdapter = $resource->getConnection('core_write');

/**
 * Retrieve our table name
 */
$tableName = $resource->getTableName('catalog/product');

/**
 * Set the test product ID(entity id) to delete
 */
$productId = 10;

// to update sku of concerned/related product id
$writeAdapter->delete(
    $tableName,
    "entity_id=$productId"
);
?>