Magento SQL statement not returning expected results

Question

I'm running Magento 1.7.0.2:

I'm trying to retrieve all the columns from a custom table using PHP & SQL, but the results returned are not what I expected or usually get:

$connection = $this->_getConnection('core_read');
$sql = "SELECT * FROM " .  $this->_getTableName('my_custom_table_name') . " cped
        WHERE  cped.id = ?";
$results = $connection->fetchOne($sql, array($id));
print_r($results); //this only prints out a single value

public function _getConnection($type = 'core_read'){
     return Mage::getSingleton('core/resource')->getConnection($type);
}

public function _getTableName($tableName){
     return Mage::getSingleton('core/resource')->getTableName($tableName);
}

The issue is, this only returns the first column (i.e in this case id) even though I've used the

Select *

Statement, which usually works perfectly fine. Coincidentally, if I try specify the column names that I'm interested in using:

Select id, name, sku, custom_value

It only returns the first value, so whichever column I specify first is the value it returns.

If I try running this same statement in PHPMyAdmin, it returns the expected results perfectly. Any ideas?

$id in this instance is the primary key of the table, its surrounded by array() because usually there are a few more values parsed in there for WHERE, ORDER BY etc. though in this instance it could be left out. Running it without the array() returns identical results. — Moose
– Moose, Commented Dec 11, 2013 at 8:30
Thanks @moose. this is something new for me. that's why I asked to u — Roopendra
– Roopendra, Commented Dec 11, 2013 at 8:36

Marius · Accepted Answer · 2013-12-11 08:29:44Z

4

That is what fetchOne does. It gets the first record. Notice the One in the function name.
Try using fetchAll.

answered Dec 11, 2013 at 8:29

Marius

15.2k9 gold badges60 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Moose Over a year ago

Thanks Marius, fetchAll does seem to do the trick! It's strange though, fetchOne usually fetches the whole record for me, i.e I still have access to any of the columns that record contains, in this instance though it limited me to one column only.. Any idea what may cause that behavior?

Marius Over a year ago

Sorry, NO idea. But I have a pointer. Don't use direct sql statements to fetch data from tables. Use the models in combination with load() method. It's safer.

Moose Over a year ago

Thanks for the tip Marius, the reason I'm using direct statements however is due to the nature of this data size, using SQL with read_only allows me to analyze the data much faster than using ORM. This one has really got me puzzled, thanks though, appreciate the input!

Marius Over a year ago

Ok. I get your reasons. You are right. The ORM is not that fast.

Moose · Accepted Answer · 2013-12-11 09:53:32Z

0

Turns out I was mistaken about the functionality of the direct SQL statements fetchOne will indeed fetch only one column result from the statement, the fetchRow query will return every column in that table.

answered Dec 11, 2013 at 9:53

Moose

6301 gold badge14 silver badges29 bronze badges

Collectives™ on Stack Overflow

Magento SQL statement not returning expected results

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related