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:
-
Controller (
CustomModule\Database\Controller\Page\Index.php): This is a simple controller that extendsMagento\Framework\App\Action\Action. It doesn’t perform any database operations, but it sets up a view page using the provided layout file. -
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. -
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$resultsvariable.
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!