Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Magento: select from database

I am working on my first module for magento version 1.3.2.3. I have created a simple table (not EAV, just a primary key and 2 columns) and some classes to access it, following Alan Storm's articles which helped me a lot, but I can't figure out how to make a simple select: Alan explains how to load with the primary key, but not selecting rows that match some value.

In normal MySQL I'd write:

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

I've found a snippet which gives me the result I want:

$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);  

But there have to be an easier/prettier solution, using the model class I've created. The result will be a single row, not a collection.
I've tried everything I could think of, like:

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

and more stuff, but no luck so far: what am I missing??

like image 574
OSdave Avatar asked Jul 07 '10 17:07

OSdave


People also ask

How do I run a SQL query in Magento 2?

You need to use the $this->resourceConnection object to run any Direct SQL Query. Reading From The Database All the records, fetchAll() => This method Fetches all SQL result rows as a sequential array. This method takes a query as it's the first parameter, executes it, and then returns all of the results as an array.

How do I access database in Magento?

Access my Magento database: So there are three ways to access database: Login in cPanel and clieck on phpmyadmin to access DB. Ask Host for Database direct URL and use your DB username, password to access DB. download third party software like mysql workbench to access DB.


2 Answers

You probably want to use your model's Collection for that.

$collection = Mage::getModel('mygroup/mymodel')->getCollection();
$collection->addFieldToFilter('some_field',$some_value);

foreach($collection as $item)
{
    var_dump($item);
}

var_dump($collection->getFirstItem());
var_dump($collection->getLastItem());
like image 137
Alan Storm Avatar answered Oct 04 '22 05:10

Alan Storm


Here's an example of how this is achieved in the CoreUrlRewrite Model class:

public function loadByIdPath($path)
{
    $this->setId(null)->load($path, 'id_path');
    return $this;
}

You can create similar methods in your model classes. You can also use the alternative form of the load method anywhere in your code:

$model = Mage::getModel('modulename/classname')->load($someValue, 'some_field');
like image 30
silvo Avatar answered Oct 04 '22 05:10

silvo