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.
Hope it will work for you!