Magento - Module INSERT,UPDATE, DELETE, SELECT code

Suppose, I have a module named mynews. Here follows the code to select, insert, update, and delete data from the news table.

INSERT DATA

$data contains array of data to be inserted. The key of the array should be the database table’s field name and the value should be the value to be inserted.

$data = array('title'=>'hello there','content'=>'how are you? i am fine over here.','status'=>1);
$model = Mage::getModel('mynews/mynews')->setData($data);
try {
    $insertId = $model->save()->getId();
    echo "Data successfully inserted. Insert ID: ".$insertId;
} catch (Exception $e){
 echo $e->getMessage();   
}

SELECT DATA

$item->getData() prints array of data from ‘news’ table.
$item->getTitle() prints the only the title field.

Similarly, to print content, we need to write $item->getContent().

$model = Mage::getModel('mynews/mynews');
$collection = $model->getCollection();
foreach($collection as $item){
print_r($item->getData());
print_r($item->getTitle());
}

UPDATE DATA

$id is the database table row id to be updated. $data contains array of data to be updated. The key of the array should be the database table’s field name and the value should be the value to be updated.

// $id = $this->getRequest()->getParam('id');
$id = 2;
$data = array('title'=>'hello test','content'=>'test how are you?','status'=>0);
$model = Mage::getModel('mynews/mynews')->load($id)->addData($data);
try {
    $model->setId($id)->save();
    echo "Data updated successfully.";

} catch (Exception $e){
    echo $e->getMessage(); 
}

DELETE DATA

$id is the database table row id to be deleted.

// $id = $this->getRequest()->getParam('id');
$id = 3;
$model = Mage::getModel('mynews/mynews');
try {
    $model->setId($id)->delete();
    echo "Data deleted successfully.";

} catch (Exception $e){
    echo $e->getMessage(); 
}

In this way you can perform select, insert, update and delete in your custom module and in any magento code.

How to use select, update, delete and insert custom queries in magento

- We can Perform select, update, delete and insert queries through php file by following steps.


require_once '../../app/Mage.php';
Mage::app('default');


        Select query to get the value form table           

$connection = Mage::getSingleton('core/resource')
->getConnection('core_read');
$select = $connection->select()
->from('tablename', array('*')) // select * from tablename or use array('id','name') selected values
->where('id=?',1)               // where id =1
->group('name');         // group by name
$rowsArray = $connection->fetchAll($select); // return all rows
$rowArray =$connection->fetchRow($select);   //return row


             Insert query                  

$connection = Mage::getSingleton('core/resource')
->getConnection('core_write');
$connection->beginTransaction();
$fields = array();
$fields['name']= 'test';
$fields['age']='25';
$connection->insert('tablename', $fields);

$connection->commit();

            update query                 

$connection = Mage::getSingleton('core/resource')
->getConnection('core_write');
$connection->beginTransaction();
$fields = array();
$fields['name'] = 'jony';

//for single parameter
$where = $connection->quoteInto('id =?', '1');

/* For mulitple parameters
$where = $connection->quoteInto('id =?', '1')
                 .$connection->quoteInto('name =?', 'Mathi') ;
*/

$connection->update('tablename', $fields, $where);

$connection->commit();


                delete query                    

$condition = array($connection->quoteInto('id=?', '1'));

$connection->delete('tablename', $condition);