This post show code for execute/run query for Direct MySQL query for create, read, update and delete.

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'");
x