Magento: select from database

Magento: select from database

July 2, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Magento: select from database

Selecting Data from Magento Database

Hello developers! I hope this post helps you navigate a common challenge when working with Magento - selecting data from your custom tables using the right approach.

Understanding the Current Scenario

You’re building your first Magento module and have created a simple table (non-EAV) with a primary key and two columns. Following Alan Storm’s excellent articles (https://alanstorm.com/), you’ve successfully set up your models to access this data, but now face a roadblock when trying to perform a basic SELECT query.

The Basic MySQL Select Query

In standard MySQL, the query would look something like this:

SELECT *
FROM my_table
WHERE some_field = '" . $someValue . "'

However, Magento’s architecture requires us to use its built-in resources and connections for database operations. Let’s explore how to achieve your goal within this framework.

The Current Snippet - Using Core Resources

The snippet you found works as expected:

$resource = new Mage_Core_Model_Resource();
$read = $resource->getConnection('core_read');
$select = $read->select()
               ->from('my_table')
               ->where('some_field = ?', $someValue);
return $read->fetchAll($select);

This code does exactly what you need - it selects all rows from my_table where the value of some_field matches $someValue. The core_read connection is the recommended option for reading data.

What’s Wrong with Your Attempts?

You tried using your custom model class to perform this operation:

return Mage::getModel('modulename/classname')->select()->where('some_field = ?', $someValue);

And other variations like:

return Mage::getModel('modulename/classname')->load()->where('some_field = ?', $comeValue);
return Mage::getModel('modulename/classname')->load(array('some_field = ?', $comeValue));

The issue lies in the fact that these methods are designed for loading models by primary key, not for executing arbitrary SELECT queries.

The Right Way - Using Your Model’s Resource

To make your code cleaner and more maintainable, you should use your model’s resource to perform the query. Here’s how:

  1. Access Your Model’s Resource
$resource = Mage::getResourceSingleton('modulename/classname');
  1. Get the Connection
$read = $resource->getReadConnection();
  1. Create and Execute the Select Query
$select = $read->select()
               ->from($resource->getMainTable())
               ->where('some_field = ?', $someValue);
return $read->fetchAll($select);

This approach uses your model’s main table name (obtained via getMainTable()) and the connection specific to your resource. It ensures that your query is executed using the appropriate database configuration.

Conclusion

In Magento, selecting data from custom tables requires a careful balance between using the core resources for efficiency and leveraging your model’s capabilities for maintainability. The key takeaways are:

  • Always use core_read connection for reading data.
  • Use your model’s resource to construct the SELECT query.
  • Be mindful of the database configuration associated with each resource.

Remember, Magento is a powerful platform designed to handle complex e-commerce applications efficiently. By understanding its database access patterns and leveraging its built-in resources, you can build robust and scalable modules.

Happy coding! If you’re looking for more in-depth guides on Magento development, feel free to explore https://laravelcompany.com.

Database Extensions

Our network