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" ); ?>