Here we will explain to work with database collection in Magento. As we know that Magento is enrich with MVC (Model – View – Controller). So we will basically starts with some main concepts of models, resource models, and collections.
Also, read:
- Display Related products on product details page in Magento
- Know About Magento Object Relational Mapping (ORM)
- Creating Custom Magento URL Rewrites
- Generate CSV file in Magento
Basic concepts of models, resource models, and collections
A “model” is used to store data, and perhaps performs some business logics against that data.
A “resource model” is used to interact with the database on behalf of the “model”. The “resource model” actually performs the CRUD operations.
A “collection model” holds from one to many “models” and knows how to tell the “resource model” to get rows in the basis of information it is given.
There’s a basic ActiveRecord-like/one-object-one-table Model, and there’s also an Entity Attribute Value (EAV) Model.
Configure a database connection
<resources> <affiliateplus_setup> <setup> <module>Phpzag_Affiliate</module> </setup> <connection> <use>core_setup</use> </connection> </affiliateplus_setup> <affiliateplus_write> <connection> <use>core_write</use> </connection> </affiliateplus_write> <affiliateplus_read> <connection> <use>core_read</use> </connection> </affiliateplus_read> </resources>
Create and register new entities
<entities> <account> <table>affiliate_account</table> </account> </entities>
As we know that Zend_Db and its related classes provide a simple SQL database interface for Zend Framework. The Zend_Db_Adapter is the basic class you use to connect your PHP application to Mysql database.
Here we will use Zend_Db_Adapter_Pdo_Mysql classes to query the Database
<?php $db = new Zend_Db_Adapter_Pdo_Mysql(array( ‘host’ => ‘127.0.0.1’, ‘username’ => ‘dbuser’, ‘password’ => ‘xxxxxxxx’, ‘dbname’ => ‘dbname’, ‘profiler’ => true, ));
Returns to the information of queries.
<?php $profiler = $db->getProfiler();
Zend_Db_Statement
$sql = ‘SELECT * FROM order WHERE status = ?’; $stmt = new Zend_Db_Statement_Mysqli($db, $sql);
Zend_Db_Table:
Each class interacts with one table in the database, and you need to declare the database table for which this class define.
Example:
class Orders extends Zend_Db_Table_Abstract { protected $_oname = ‘order’; } $table = new Order(array(‘db’ => $db));
With $table object, you can use some methods to operate with the database such as: insert, update, delete.
Zend_Db_Table_Row:
This will return the record object in the table
$bugs = new Order(); $row = $bugs->fetchRow($bugs->select()->where(‘order_id = ?’, 1)); $rowArray = $row->toArray();
Zend_Db_Select:
$select = $db->select() ->from( …specify table and columns… ) ->where( …specify search criteria… ) ->order( …specify sorting criteria… );
Database collection in Magento
The collection in Magento usually extends from class Mage_Core_Model_Resource_ Collection_Abstract or Mage_Core_Model_Mysql4_ Collection_Abstract. The collection has some methods for you to filter, sort and specify the selected values:
- addFieldToFilter(,): used to filter data
- setOrder(): used to sort data
- getSelect(): returns the selected query (is instance object of class Varien_Db_Select) to this collections. And you are able to use it to add specific selected value.
- Database resource
- abstract class Mage_Core_Model_Resource_Abstract
- abstract class Mage_Core_Model_Mysql4_Abstract
The database model and the collection connect to database through database resource layer. The resource class extends from an abstract class:
In this class, you need to declare your database table and the id field of this table.
For example:
public function _construct(){ $this->_init(‘affiliate/program’, ‘order_id’); }
We can also use and resolve existing table names without hard coding them
$resource = Mage::getSingleton(‘core/resource’); $eavAttributeTable = $resource->getTable(‘eav/attribute’);
“eav/attribute” here is your configuration for table eav_entity_attribute in your database.
You may also like:
- Star Rating System with Ajax, PHP and MySQL
- Create Event Calendar with jQuery, PHP and MySQL
- Build Your Own CAPTCHA Script with PHP
- Convert Unix Timestamp To Readable Date Time in PHP
- Inventory Management System with Ajax, PHP & MySQL
- Create Live Editable Table with jQuery, PHP and MySQL
- Live Add Edit Delete datatables Records with Ajax, PHP and MySQL
- Stripe Payment Gateway Integration in PHP
- Export Data to Excel with PHP and MySQL
- Star Rating System with Ajax, PHP and MySQL
- Create Dynamic Bootstrap Tabs with PHP & MySQL
- How To Create Simple REST API in PHP