Magento 数据库查询速记 select, insert, update, and delete data

query.php

<?php

include_once 'app/Mage.php';

Mage::app();



//====================================================================


//$product  = Mage::getModel('catalog/product')->load(1);
//echo '<pre>'; print_r($product); echo '</pre>';

/*
 *  select query
 */
 
// First Way
//$description = $product->getData('description');

// Second Way
//$description = $product->getDescription();

//echo '<pre>'; print_r($description); echo '</pre>';


/*
 *  update query
 */
 
// First Way
//$product->setData('description','text updated')->save();

// Second Way
//$product->setDescription('text updated')->save();



/*
 *  delete query
 */

 //$product->delete();


//====================================================================

// no attributes

/*
$products  = Mage::getModel('catalog/product')->getCollection();

foreach($products as $product)
{
    echo '<pre>'; print_r($product); print_r($product->getData()); echo '</pre>';
}
*/

// with attributes 
/*
$products  = Mage::getModel('catalog/product')->getCollection()
            ->addAttributeToSelect(array('name','price','description')) // selected fields or ->addAttributeToSelect('*')
            ->addFieldToFilter('price',array('lt'=>50)); // search
            //->->getFirstItem();
       

foreach($products as $product)
{
    echo '<pre>'; print_r($product); print_r($product->getName()); echo '</pre>';
}
*/


//====================================================================

// custom query

$read= Mage::getSingleton('core/resource')->getConnection('core_read');


// first way
$query = $read->query("select * from mgt_customer_feedback");
while ($row = $query->fetch()) 
{ 
    echo '<pre>'; print_r($row); echo '</pre>';
}

// second way
//$results = $read->fetchAll("select * from customer_entity where email like '%@ikeepstudying.com'");
//foreach($results as $row)


// For Write
// fetch write database connection that is used in Mage_Core module
//$write = Mage::getSingleton('core/resource')->getConnection('core_write');

// now $write is an instance of Zend_Db_Adapter_Abstract
//$write->query("insert into tablename values ('aaa','bbb','ccc')");

// Magento Insert Query.
$write->insert(
        "A_TABLE_NANE",
        array("a_column_name" => value, "a_column_name" => value)
);

// Magento Update Query.
$write->update(
        "A_TABLE_NANE",
        array("a_column_name" => value, "a_column_name" => value),
        "entity_id=18"
);

// Magento Delete Query.
$write->delete(
    "A_TABLE_NANE",
    "entity_id=18"
);

Magento: How to select, insert, update, and delete data?

Here, I will be showing how to select, insert/add, update/edit and delete data in the Magento way. It’s about implementing the CRUD (Create Read Update Delete) concept. 🙂

Suppose, I have a database table named ‘news‘ with the following fields:-

id : int, auto increment, primary key
title : varchar
content : text
status : 0 or 1

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.

Hope this helps. Thanks.

更多参考：

1. Magento模块开发之数据库SQL操作方法说明
2. Magento: addAttributeToFilter 和 addFieldToFilter 的区别 Difference between addAttributeToFilter and addFieldToFilter
3. Magento模型集合addFieldToFilter常用过滤条件
4. Magento: How to select, insert, update, and delete data?

原文/本文： Magento 数据库查询速记 select, insert, update, and delete data