Magento Question posted in
The official documentation can be found here.

How to fetch db table values using mysql query in magento 2. Magento 2 use sql query?

I’m new in Magento.

CustomModuleDatabaseControllerPageIndex.php

<?php
namespace CustomModuleDatabaseControllerPage;
class Index extends MagentoFrameworkAppActionAction
{
    protected $_pageFactory;

    public function __construct(
        MagentoFrameworkAppActionContext $context,
        MagentoFrameworkViewResultPageFactory $pageFactory)
    {
        $this->_pageFactory = $pageFactory;
        return parent::__construct($context);
    }

    public function execute()
    {
        return $this->_pageFactory->create();
    }
}

CustomModuleDatabaseBlockIndex.php

<?php

namespace CustomModuleDatabaseBlock;

use MagentoFrameworkViewElementTemplate;

class Index extends Template
{
 
    /**
     * @var MagentoFrameworkAppResourceConnection
     */
    public $resourceConnection;
 
    public function __construct(
        MagentoFrameworkAppActionContext $context,
        MagentoFrameworkAppResourceConnection $resourceConnection
    ) {
        $this->resourceConnection = $resourceConnection;
        parent::__construct($context);
    }
 
    /**
     * Get Table name using direct query
     */
    public function getTablename($tableName)
    {
        /* Create Connection */
        $connection  = $this->resourceConnection->getConnection();
        $tableName   = $connection->getTableName($tableName);
        return $tableName;
    }
}

CustomModuleDatabaseviewfrontendtemplatesindex.phtml

<?php
$tableName = $this->getTableName('sales_invoice_item');
$query = 'SELECT name,sku,price,qty FROM' . $tableName;
/**
 * Execute the query and store the results in $results variable
 */
$results = $this->resourceConnection->getConnection()->fetchCol($query);
echo "<pre>";print_r($results);
?>

Showing an error.

1 exception(s):
Exception #0 (MagentoFrameworkExceptionRuntimeException): Type Error occurred when creating object: CustomModuleDatabaseBlockIndex

Exception #0 (MagentoFrameworkExceptionRuntimeException): Type Error occurred when creating object: CustomModuleDatabaseBlockIndex
<pre>#1 MagentoFrameworkObjectManagerFactoryDynamicDeveloper->create('CustomModuleDat...', array('data' => array())) called at [vendormagentoframeworkObjectManagerObjectManager.php:56]
#2 MagentoFrameworkObjectManagerObjectManager->create('CustomModuleDat...', array('data' => array())) called at [vendormagentoframeworkViewElementBlockFactory.php:46]
#3 MagentoFrameworkViewElementBlockFactory->createBlock('CustomModuleDat...', array('data' => array())) called at [vendormagentoframeworkViewLayoutGeneratorBlock.php:272]
#4 MagentoFrameworkViewLayoutGeneratorBlock->getBlockInstance('CustomModuleDat...', array('data' => array())) called at [vendormagentoframeworkViewLayoutGeneratorBlock.php:252]
#5 MagentoFrameworkViewLayoutGeneratorBlock->createBlock('CustomModuleDat...', 'index', array('data' => array())) called at [vendormagentoframeworkViewLayoutGeneratorBlock.php:229]
#6 MagentoFrameworkViewLayoutGeneratorBlock->generateBlock(&MagentoFrameworkViewLayoutScheduledStructure#0000000006b3bc800000000037a2235d#, &MagentoFrameworkViewLayoutDataStructure#0000000006b3b9d10000000037a2235d#, 'index') called at [vendormagentoframeworkViewLayoutGeneratorBlock.php:134]
#7 MagentoFrameworkViewLayoutGeneratorBlock->process(&MagentoFrameworkViewLayoutReaderContext#0000000006b3b33b0000000037a2235d#, &MagentoFrameworkViewLayoutGeneratorContext#0000000006b3bc8d0000000037a2235d#) called at [vendormagentoframeworkViewLayoutGeneratorPool.php:81]
#8 MagentoFrameworkViewLayoutGeneratorPool->process(&MagentoFrameworkViewLayoutReaderContext#0000000006b3b33b0000000037a2235d#, &MagentoFrameworkViewLayoutGeneratorContext#0000000006b3bc8d0000000037a2235d#) called at [vendormagentoframeworkViewLayout.php:352]
#9 MagentoFrameworkViewLayout->generateElements() called at [vendormagentoframeworkInterceptionInterceptor.php:58]
#10 MagentoFrameworkViewLayoutInterceptor->___callParent('generateElements', array()) called at [vendormagentoframeworkInterceptionInterceptor.php:138]
#11 MagentoFrameworkViewLayoutInterceptor->MagentoFrameworkInterception{closure}() called at [vendormagentoframeworkInterceptionInterceptor.php:153]
#12 MagentoFrameworkViewLayoutInterceptor->___callPlugins('generateElements', array(), array(array('layout-model-cac...', 'core-session-dep...', 'customer-session...', 'catalog-session-...', 'persistent-sessi...', 'checkout-session...', 'tax-session-depe...'))) called at [generatedcodeMagentoFrameworkViewLayoutInterceptor.php:91]
#13 MagentoFrameworkViewLayoutInterceptor->generateElements() called at [vendormagentoframeworkViewLayoutBuilder.php:129]
#14 MagentoFrameworkViewLayoutBuilder->generateLayoutBlocks() called at [vendormagentoframeworkViewPageBuilder.php:55]
#15 MagentoFrameworkViewPageBuilder->generateLayoutBlocks() called at [vendormagentoframeworkViewLayoutBuilder.php:65]
#16 MagentoFrameworkViewLayoutBuilder->build() called at [vendormagentoframeworkViewPageConfig.php:224]
#17 MagentoFrameworkViewPageConfig->build() called at [vendormagentoframeworkViewPageConfig.php:237]
#18 MagentoFrameworkViewPageConfig->publicBuild() called at [generatedcodeMagentoFrameworkViewPageConfigInterceptor.php:37]
#19 MagentoFrameworkViewPageConfigInterceptor->publicBuild() called at [vendormagentoframeworkViewResultPage.php:242]
#20 MagentoFrameworkViewResultPage->render(&MagentoFrameworkAppResponseHttpInterceptor#0000000006b3bbde0000000037a2235d#) called at [vendormagentoframeworkViewResultLayout.php:171]
#21 MagentoFrameworkViewResultLayout->renderResult(&MagentoFrameworkAppResponseHttpInterceptor#0000000006b3bbde0000000037a2235d#) called at [vendormagentoframeworkInterceptionInterceptor.php:58]
#22 MagentoFrameworkViewResultPageInterceptor->___callParent('renderResult', array(&MagentoFrameworkAppResponseHttpInterceptor#0000000006b3bbde0000000037a2235d#)) called at [vendormagentoframeworkInterceptionInterceptor.php:138]
#23 MagentoFrameworkViewResultPageInterceptor->MagentoFrameworkInterception{closure}(&MagentoFrameworkAppResponseHttpInterceptor#0000000006b3bbde0000000037a2235d#) called at [vendormagentoframeworkInterceptionInterceptor.php:153]
#24 MagentoFrameworkViewResultPageInterceptor->___callPlugins('renderResult', array(&MagentoFrameworkAppResponseHttpInterceptor#0000000006b3bbde0000000037a2235d#), array(array('result-messages', 'result-builtin-c...', 'result-varnish-c...'))) called at [generatedcodeMagentoFrameworkViewResultPageInterceptor.php:130]
#25 MagentoFrameworkViewResultPageInterceptor->renderResult(&MagentoFrameworkAppResponseHttpInterceptor#0000000006b3bbde0000000037a2235d#) called at [vendormagentoframeworkAppHttp.php:120]
#26 MagentoFrameworkAppHttp->launch() called at [generatedcodeMagentoFrameworkAppHttpInterceptor.php:24]
#27 MagentoFrameworkAppHttpInterceptor->launch() called at [vendormagentoframeworkAppBootstrap.php:261]
#28 MagentoFrameworkAppBootstrap->run(&MagentoFrameworkAppHttpInterceptor#0000000006b3bbd70000000037a2235d#) called at [index.php:39]
</pre>
Tags:

2

Answers


  1. 0

    You can get data from the database table by MySQL query with below code:

    protected $_resource;

public function __construct(
    MagentoFrameworkAppResourceConnection $resource
) {
    $this->_resource = $resource;
}

public function execute()
{
    // get connection
    $connection     = $this->_resource->getConnection();
    // table name
    $tableName = $this->_resource->getTableName('your table name');
    // Query
    $select_sql = "Select * FROM " . $tableName;
    // fetch result
    $results = $connection->fetchAll($select_sql);
    // print result
    print_r($results);
}
    Login or Signup to reply.
  2. 0

    Short answer is: You don’t.
    Magento 2 offers you Collections and Factories to access Database resources, not requiring you do write any plain SQL queries like any modern framework should.
    You can inject CollectionFactory classes into your Classes constructor like this:

       /** @var MagentoCatalogModelResourceModelProductCollectionFactory */
   private $productCollectionFactory;

   public function __construct(MagentoCatalogModelResourceModelProductCollectionFactory $productCollectionFactory) {
      $this->productCollectionFactory = $productCollectionFactory;
   }

    Like this you can get object representations of any invoice, order, product, product categories and many more.
    However, if you really need to directly run SQL, Amit Saini`s answer is the way to go.
    I found this source on that.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search