Magento – Magento 2 – Admin module – how to SELECT data from database to display in Admin page

adminadminhtmldatabaseevent-observermagento2

I'm building out an Admin module that's intended to SELECT data from a specific table in magento database and display that data on page, it's a custom table called "commission_sales". At this point I just want to SELECT * (all data) from table and display on page.

The following code does work, i've tested on the order success page and data does pull through. I just can't seem to do the same for the admin page:

Note: I've also tried to trigger this code in events.xml with no luck

<?php
/**
 * Copyright © 2016 Magento. All rights reserved.
 * See COPYING.txt for license details.
 *  
 */
namespace MyCompany\ExampleAdminNewPage\Observer;

use Magento\Framework\Event\Observer;
use Magento\Framework\Event\ObserverInterface;


class commission_data implements ObserverInterface
{
    /**
     * @param Observer $observer
     * @return void
     */
    public function execute(\Magento\Framework\Event\Observer $observer)
    {
        $objectManager = \Magento\Framework\App\ObjectManager::getInstance();
        $resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
        $connection = $resource->getConnection();
        $tableName = $resource->getTableName('commission_sales');

        $sql = "SELECT * FROM " . $tableName;
        $result = $connection->fetchAll($sql); 
        echo '<pre> Data currently saved to commission_sales table</pre><pre>'; echo ($result); echo '</pre>';      
    }
}  
?>

How would you incorporate the above SELECT request and then display that data in an admin page/module?

Current files for the admin module so far:

   MyCompany
    `-- ExampleAdminNewPage
        |
        |-- Controller
        |   |-- Adminhtml
        |       |-- HelloWorld
        |           `-- Index.php
        |-- etc
        |   |-- adminhtml
        |   |   |-- menu.xml
        |   |   `-- routes.xml
        |   |   `-- events.xml
        |   `-- module.xml
        |-- view
        |   |-- adminhtml
        |       |-- layout
        |       |    `-- exampleadminnewpage_helloworld_index.xml
        |       `-- templates
        |           `-- helloworld.phtml
        |-- composer.json
        `-- registration.php

Appreciate any help!

Thanks,
Chris

Best Answer

I believe that you are using observer for any admin event. You can add below code in your observer to check that your observer file is being called or not.

public function execute(\Magento\Framework\Event\Observer $observer)
{
  $writer = new \Zend\Log\Writer\Stream(BP . '/var/log/test.log');
  $logger = new \Zend\Log\Logger();
  $logger->addWriter($writer);
  $logger->info(print_r($result,1));   
}

It will create new log file named 'test.log' and with fetched data print in it.

Related Solutions

Magento – How to get data from custom MySQL table into your Adminhtml grid table

The lex.you need to create a model using a custom module.

Step: You have define model class and resource type but not define resource Class

<models>
        <smsnotification>
            <class>VivasIndustries_SmsNotification_Model</class> <!-- model prefix class -->
            <resourceModel>vivasindustries_smsnotification_resource</resourceModel>
<!-- model resource type -->

        </smsnotification>
    </models>

So you need to add resource class which is interact the model with database.

  <models>
......
    <vivasindustries_smsnotification_resource>
        <class>VivasIndustries_SmsNotification_Model_Resource</class>
        <entities>
            <custommodule>
            <table>VivasIndustries_SmsNotification</table> <!-- VivasIndustries_SmsNotification is Your db tabale -->
            </custommodule>
        </entities>
        </vivasindustries_smsnotification_resource>
</models>

VivasIndustries_SmsNotification_Model_Resource VivasIndustries_SmsNotification

you need to define class VivasIndustries_SmsNotification_Model_Smsnotification

code is

<?php
class VivasIndustries_SmsNotification_Model_Smsnotification extends extends Mage_Core_Model_Abstract
{
    public function _construct()
    {
        $this->_init('smsnotification/smsnotification');
    }

}

Resource class model class:

<?php
class VivasIndustries_SmsNotification_Model_Resource_Smsnotification extends Mage_Core_Model_Resource_Db_Abstract
{
    /**
     * Initialize resource model
     *
     * @return void
     */
    public function _construct()
    {
        $this->_init('smsnotification/smsnotification','id');
    }
}

Resource Collection model

<?php 
class VivasIndustries_SmsNotification_Model_Resource_Smsnotification_Collection 
extends Mage_Core_Model_Resource_Db_Collection_Abstract{
    protected function _constuct(){
        $this->_init('smsnotification/smsnotification');    
    }
}

[Please study at how create model][1]

Now you get a collection mode.

Now on preapareCollection

you need change code:

$collection =Mage::getResourceModel('smsnotification/smsnotification_collection);

You grid code is:

<?php

class VivasIndustries_SmsNotification_Block_Adminhtml_Smsnotification_Grid extends Mage_Adminhtml_Block_Widget_Grid
{

        public function __construct()
        {
                parent::__construct();
                $this->setId("smsnotificationGrid");
                $this->setDefaultSort("id");
                $this->setDefaultDir("DESC");
                $this->setSaveParametersInSession(true);
        }

        protected function _prepareCollection()
        {
                $collection = Mage::getModel("smsnotification/smsnotification")->getCollection();
                $this->setCollection($collection);
                return parent::_prepareCollection();
        }
        protected function _prepareColumns()
        {
                $this->addColumn("id", array(
                "header" => Mage::helper("smsnotification")->__("ID"),
                "align" =>"right",
                "width" => "50px",
                "type" => "number",
                "index" => "id",
                ));

                $this->addColumn("receiver", array(
                "header" => Mage::helper("smsnotification")->__("Receiver"),
                "index" => "receiver",
                ));
                $this->addColumn("phone", array(
                "header" => Mage::helper("smsnotification")->__("Phome"),
                "index" => "phone",
                ));
                    $this->addColumn('date', array(
                        'header'    => Mage::helper('smsnotification')->__('Data'),
                        'index'     => 'date',
                        'type'      => 'datetime',
                    ));
            $this->addExportType('*/*/exportCsv', Mage::helper('sales')->__('CSV')); 
            $this->addExportType('*/*/exportExcel', Mage::helper('sales')->__('Excel'));

                return parent::_prepareColumns();
        }

        public function getRowUrl($row)
        {
               return $this->getUrl("*/*/edit", array("id" => $row->getId()));
        }



        protected function _prepareMassaction()
        {
            $this->setMassactionIdField('id');
            $this->getMassactionBlock()->setFormFieldName('ids');
            $this->getMassactionBlock()->setUseSelectAll(true);
            $this->getMassactionBlock()->addItem('remove_smsnotification', array(
                     'label'=> Mage::helper('smsnotification')->__('Remove Smsnotification'),
                     'url'  => $this->getUrl('*/adminhtml_smsnotification/massRemove'),
                     'confirm' => Mage::helper('smsnotification')->__('Are you sure?')
                ));
            return $this;
        }


}

Update code at config.xml

<global>
...
<models>
        <smsnotification>
            <class>VivasIndustries_SmsNotification_Model</class> <!-- model prefix class -->
            <resourceModel>vivasindustries_smsnotification_resource</resourceModel>
<!-- model resource type -->

        </smsnotification>
    <vivasindustries_smsnotification_resource>
        <class>VivasIndustries_SmsNotification_Model_Resource</class>
        <entities>
            <custommodule>
            <table>VivasIndustries_SmsNotification</table> <!-- VivasIndustries_SmsNotification is Your db tabale -->
            </custommodule>
        </entities>
        </vivasindustries_smsnotification_resource>    
    </models>
    <resources>
      <smsnotification_setup>
        <setup>
          <module>VivasIndustries_SmsNotification</module>
        </setup>
        <connection>
          <use>core_setup</use>
        </connection>
      </smsnotification_setup>
      <smsnotification_write>
        <connection>
          <use>core_write</use>
        </connection>
      </smsnotification_write>
      <smsnotification_read>
        <connection>
          <use>core_read</use>
        </connection>
      </smsnotification_read>
    </resources>
.....
</global>


  [1]: http://www.amitbera.com/create-an-magento-extension-with-custom-database-table/

Adminhtml MySQL – Error Getting Data from Custom Table in Grid View

Your problem is probably here:

I have in:     /app/code/community/VivasIndustries/SmsNotification/Model/SmsNotification.php:

<?php
class VivasIndustries_SmsNotification_Model_Smsnotification extends extends     Mage_Core_Model_Abstract

You have a case error and a typo. You have two "extends" and based on your directory structure your class should read:

class VivasIndustries_SmsNotification_Model_SmsNotification extends     Mage_Core_Model_Abstract

Best Answer

Related Solutions

Magento – How to get data from custom MySQL table into your Adminhtml grid table

Adminhtml MySQL – Error Getting Data from Custom Table in Grid View

Related Topic