Magento Core Query – Read Write : Magento provides data models to access the data from tables and modify them. Using models to perform data operations is easier and good practice. Sometimes we need to perform Direct SQL queries to perform read or write operation. In some cases writing direct SQL queries becomes simpler & faster in terms of performance. For example if we want to update the rows of a table using magento model, for this we need to loop all the rows and then set value to update them which will take more time than updating the rows using Direct SQL Queries which will take 2-3 seconds. Here in this tutorial we are going to explain how to use the Direct SQL Queries in Magento.
Magento Core Query – Read Write
Table Name & Table Prefix
While installing magento we have option to provide the table prefix which is appended before the each table name of magento. You can get table name prefix as below –
Magento Core Query – Read Write: Table Name & Table Prefix
<?php /**
* Get Resource model
*/
$resource = Mage::getSingleton('core/resource');
/**
*Get Table name
*/
$tableName = $resource-?>getTableName('your_table_name');
or
/**
*Get Table Name From Entity
*/
$tableName = $resource->getTableName('catalog/product');
echo $tableName;
// will give you name with prefix ie. magento_your_table_name
?>
|
Read Data From Database:
If you want to preform read operation use the following syntax :
Magento Core Query – Read Write: Read operation
<?php /**
* Get Resource model
*/
$resource = Mage::getSingleton('core/resource');
/**
*Get Table Name From Entity
*/
//$tableName = $resource-?>getTableName('catalog_product_entity');
$read = Mage::getSingleton( 'core/resource' )->getConnection( 'core_read' );
// used to perform read operation
$tableName = $resource->getTableName('catalog/product');
$query = "SELECT * FROM " . $tableName . " WHERE created_at > $createdAt ";
$result = $read->query($query);
print_r($result);
?>
|
Mage::getSingleton( ‘core/resource’ )->getConnection( ‘core_read’ ); is used to perform the read operation on the database. The above example will execute the query and give the result.
Write Data to Database:
If you want to preform write operation use the following syntax :
Magento Core Query – Read Write: Write Operation
<?php /**
* Get Resource model
*/
$resource = Mage::getSingleton('core/resource');
$write = Mage::getSingleton( 'core/resource' )-?>getConnection( 'core_write' );
$tableName = $resource->getTableName('your_table_name');
$write->query("insert into " . $tableName . "(field1, field2, field3) values('value1', 'value2', 'value3')");
?>
|
Mage::getSingleton( ‘core/resource’ )->getConnection( ‘core_write’ ); is used to perform the write operation on the database. The above example will execute the insert query.
More About Magento Custom Query
Let us have a look over other Read – Write Example.
Magento Read::fetchAll
Magento Core Query – Read Write: Read operation
<?php /**
* Get Resource model
*/
$resource = Mage::getSingleton('core/resource');
/**
*Get Table Name From Entity
*/
//$tableName = $resource-?>getTableName('catalog_product_entity');
$read = Mage::getSingleton( 'core/resource' )->getConnection( 'core_read' );
// used to perform read operation
$tableName = $resource->getTableName('catalog/product');
$query = "SELECT * FROM " . $tableName . ";
$result = $read->fetchAll($query);
print_r($result);
?>
|
The above example will give you all result from the table.
Magento Read::fetchOne
Magento Core Query – Read Write: Read operation
<?php /**
* Get Resource model
*/
$resource = Mage::getSingleton('core/resource');
/**
*Get Table Name From Entity
*/
//$tableName = $resource-?>getTableName('catalog_product_entity');
$read = Mage::getSingleton( 'core/resource' )->getConnection( 'core_read' );
// used to perform read operation
$tableName = $resource->getTableName('catalog/product');
$query = "SELECT * FROM " . $tableName . ";
$result = $read->fetchOne($query);
print_r($result);
?>
|
The above example will give the only one value from the first row.