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

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

July 8, 2026 · Illustrated author avatarBy Stefan Bogdanescu
How to fetch db table values using mysql query in magento 2. Magento 2 use sql query?

Fetching Database Table Values Using MySQL Queries in Magento 2

Hello! Welcome to another technical blog post where we’ll dive into fetching database table values using MySQL queries in Magento 2. As a new developer, you might be wondering how to execute raw SQL queries and fetch data from your Magento store’s database. Let’s break down the code provided and understand what’s happening step by step.

Understanding the Code Structure

The given code consists of three main components:

  1. Controller (CustomModule\Database\Controller\Page\Index.php): This is a simple controller that extends Magento\Framework\App\Action\Action. It doesn’t perform any database operations, but it sets up a view page using the provided layout file.

  2. Block (CustomModule\Database\Block\Index.php): The block class is where the real magic happens. It has a protected property $resourceConnection, which provides access to the Magento store’s database connection object.

  3. Template (CustomModule\Database\view\frontend\templates\index.phtml): This is the PHP template file that gets rendered when the controller action is executed. It contains the actual SQL query and execution logic.

Fetching Database Table Values

In your index.phtml file, you’re using the $resourceConnection->getConnection()->fetchCol() method to execute a raw MySQL query and fetch column values from the sales_invoice_item table. The query is constructed by concatenating the table name with the columns you want to select.

Here’s how it works:

  • You first call getTableName('sales_invoice_item') which retrieves the fully qualified table name (including the database prefix) using Magento’s table naming conventions.
  • Then, you create a SQL query string by concatenating the selected columns with the table name obtained from the previous step.
  • Finally, you execute the query using $resourceConnection->getConnection()->fetchCol($query) and store the results in the $results variable.

The Issue at Hand

The error message you’re encountering is due to a type mismatch when creating the CustomModule\Database\Block\Index object. Let’s look at the stack trace:

Exception #0 (Magento\Framework\Exception\RuntimeException):
    Type Error occurred when creating object: CustomModule\Database\Block\Index

The error occurs because Magento expects the block class to extend Magento\Framework\View\Element\Template, but in your case, you’re extending the standard PHP Template class instead. This mismatch causes the type error.

The Solution

To fix this issue, simply change your CustomModule\Database\Block\Index.php file as follows:

namespace CustomModule\Database\Block;

class Index extends \Magento\Framework\View\Element\Template
{
    // ... (other methods remain unchanged)

}

By extending the correct Template class (Magento\Framework\View\Element\Template), you resolve the type mismatch and eliminate the error.

Conclusion

In this blog post, we’ve explored how to fetch database table values using MySQL queries in Magento 2. We’ve seen that Magento provides a straightforward way to interact with your store’s database through the $resourceConnection object available in block classes. By constructing the correct SQL query and executing it using the fetchCol() method, you can easily retrieve data from any of your Magento tables.

Remember that while raw SQL queries are powerful, they should be used judiciously to avoid potential security risks. Always ensure you’re properly sanitizing user input and protecting against SQL injection attacks when working with MySQL queries in Magento 2.

I hope this post has provided a clear understanding of the process involved in fetching database table values in Magento 2. If you have any further questions or need additional guidance, feel free to leave a comment below. Happy coding!

Don’t forget to check out our Magento development services at Laravel Company for professional assistance with your e-commerce projects.

Thank you for reading!

Magento 2 Database Extensions Themes

Our network