This post show code for execute/run query for Direct MySQL query for create, read, update and delete.
Method 1
// database Connection for read query
$core_read = Mage::getSingleton('core/resource')->getConnection('core_read');
// database Connection for write query
$core_write = Mage::getSingleton('core/resource')->getConnection('core_write');
// read and write function for query
$read_methods = get_class_methods(get_class($core_read));
foreach($read_methods as $value)
{
echo $value."\n"; // echo value
}
$write_methods = get_class_methods(get_class($core_write));
foreach($write_methods as $r)
{
echo $value."\n"; // echo value
}
Read Query and returns as array In Magento.
$query_results = $core_read->fetchAll("select * from your_table_name where your_column_name like '%some_data'");
var_dump($query_results[0]);
foreach($query_results as $value)
{
var_dump($value); // print result
}
Insert Query For Magento.
$core_write->insert(
"your_table_name",
array("your_column_name" => column_value, "your_column_name" => column_value ));
// add your column name and value
Update Query For Magento.
$core_write->update(
"your_table_name",
array("your_column_name" => column_values, "your_column_name" => column_values ),
"entity_id=116");
// add your column name, value and entity_id
Delete Query For Magento.
$core_write->delete( "your_table_name", "entity_id=116" );
Magento Query for create, read, update, and delete operations.
$query_results = $core_read->query("select * from your_table_name");
var_dump($query_results);
foreach($query_results as $value)
{
var_dump($value); // print result
}
Method 2
Magento : Create Database Connections
// database Connection for read query
$core_read = Mage::getSingleton('core/resource')->getConnection('core_read');
// database Connection for write query
$core_write = Mage::getSingleton('core/resource')->getConnection('core_write');
Magento : Custom Insert Query
// database Connection for write query
$connection = Mage::getSingleton('core/resource')->getConnection('core_write');
// Custom insert query in Magento
$sql = "INSERT INTO `your_table_name` (`YOUR_COLUMN_NAME`,`YOUR_COLUMN_NAME`,`YOUR_COLUMN_NAME`) VALUES ('116','TEST','Test this query')";
$connection->query($sql);
<strong>Magento : Custom Select Query</strong>
// database Connection for read query
$core_read = Mage::getSingleton('core/resource')->getConnection('core_read');
// add your table name
$query_value = $core_read->query("select * from your_table_name");
while ($rows = $query_value->fetch()) // fetch value
{
// print result of query
print_r($rows);
};
Magento : Custom Truncate a Table Query
// database Connection for read query
$core_read = Mage::getSingleton('core/resource')->getConnection('core_read');
// Truncate Table
$core_read->query("truncate table your_table_name"); // add your table name
Magento : Custom Update Query
$data = array("YOUR_COLUMN_NAME" => "column_value", "YOUR_COLUMN_NAME" => "column_value");
$where_condition = " id = 116 "; // add your where condition
$core_write->update("TABLE_NAME", $data, $where_condition);
// database Connection for write query
$core_write = Mage::getSingleton('core/resource')->getConnection('core_write');
$core_write->query("update your_table_name set YOUR_COLUMN_NAME = 'column_value'");
Magento : Custom Delete Query
$where_condition = " id = 116 "; // add your where condition
// database Connection for write query
$core_write = Mage::getSingleton('core/resource')->getConnection('core_write');
$core_write->delete("your_table_name", $where_condition); // Or Try this
$core_write->query("delete from your_table_name where YOUR_COLUMN_NAME = 'column_value'");