Magento - Displaying Data from Custom MySQL Table in Adminhtml Grid
Introduction
In this blog post, we will guide you through displaying data from a custom MySQL table in the Magento adminhtml grid. We’ll use Magento version 1.9.0.1 as an example.
Understanding the Current Setup
The provided code consists of several files aimed at creating a custom extension to display order status changes. The key components include:
config.xml: Defines the custom module, models, resources, and adminhtml router.Adminhtml/Sales/Status.php: Extends the Magento grid container class for displaying the grid.Adminhtml/Sales/Status/Grid.php: Customizes the grid with specific columns and data sources.
The Current Grid Configuration
The current Grid.php file is a modified version of the Inchoo guide How to Create a Custom Grid from Scratch. It retrieves order data, including products purchased, customer name, city, country, and total amount.
The Custom Table Structure
The custom table VivasIndustries_SmsNotification is designed to store SMS-related data. The structure includes columns such as:
id: Unique identifier for each recordmobile_number: The recipient’s mobile numberstatus: Status of the SMS (e.g., delivered, pending)created_at: Timestamp when the SMS was sent
Displaying Custom Table Data in the Grid
To display data from the custom table in your grid, you need to make several modifications:
Step 1: Update the Collection Preparation
In your Grid.php file, update the _prepareCollection() method to use your custom model and collection:
protected function _prepareCollection()
{
$collection = Mage::getModel('smsnotification/smsnotification')->getCollection();
$this->setCollection($collection);
parent::_prepareCollection();
return $this;
}
Step 2: Modify the Grid Columns
Update the _prepareColumns() method to match your custom table’s columns:
protected function _prepareColumns()
{
// ... (keep existing columns)
$this->addColumn('mobile_number', array(
'header' => $helper->__('Mobile Number'),
'index' => 'mobile_number'
));
$this->addColumn('status', array(
'header' => $helper->__('SMS Status'),
'index' => 'status',
'type' => 'options',
'options' => Mage::getSingleton('smsnotification/status')->getAvailableStatuses()
));
$this->addColumn('created_at', array(
'header' => $helper->__('Sent At'),
'index' => 'created_at',
'type' => 'datetime'
));
// ... (keep existing export types)
}
Step 3: Update the Status Model
Create a new file app/code/community/VivasIndustries/SmsNotification/Model/Status.php to handle SMS status options:
class VivasIndustries_SmsNotification_Model_Status extends Mage_Core_Model_Abstract
{
const STATUS_PENDING = 1;
const STATUS_DELIVERED = 2;
public function getAvailableStatuses()
{
return array(
self::STATUS_PENDING => 'Pending',
self::STATUS_DELIVERED => 'Delivered',
);
}
}
Step 4: Update the Grid URL
Update the getGridUrl() method in your Grid.php file to match your custom grid controller:
public function getGridUrl()
{
return $this->getUrl('adminhtml/smsnotification_grid/grid', array('_current' => true));
}
Conclusion
To display data from a custom MySQL table in the Magento adminhtml grid, you need to create a new model and collection for your custom table. Then, update your Grid.php file to use this new model, modify the columns to match your custom table’s structure, and update the status options if needed.
Following these steps should allow you to display data from any custom MySQL table in your Magento adminhtml grid seamlessly.
For further assistance with Magento development or other technologies, feel free to explore our services at Laravel Company.