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.
更多参考:
- Magento模块开发之数据库SQL操作方法说明
- Magento: addAttributeToFilter 和 addFieldToFilter 的区别 Difference between addAttributeToFilter and addFieldToFilter
- Magento模型集合addFieldToFilter常用过滤条件
- Magento: How to select, insert, update, and delete data?
原文/本文: Magento 数据库查询速记 select, insert, update, and delete data