Magento how to fetch row by row like PDO::fetch()

Question

I am running

$connection = Mage::getSingleton('core/resource')->getConnection('core_read');
$sql = "SELECT id FROM customers_old";
$old_customers = $connection->fetchAll($sql);

But it's a HUGH table and this does not fit into memory:

PHP Fatal error: Allowed memory size of 268435456 bytes exhausted

Normally I would make a loop an fetch row by row: $connection->fetch($sql)

But fetch does not exist here and fetchRow() always returns the same row.

Solution?

Answer 1

You'll want to use the core/resource_iterator. This passes each object in a collection through a callback method. This article from Atwix (I'm not connected to them in any way) explains it really well: http://www.atwix.com/magento/working-with-large-collections/.

Answer 2

Take a look at How to convert sql query to magento in order to fetch data

$read = Mage::getSingleton('core/resource')->getConnection('core_read');

$readresult = $read->query("SELECT id FROM customers_old");

while ($row = $readresult->fetch() ) {
    $Ids[] = $row['id'];
}

See Conquer the 5 Most Common Magento Coding Issues to Optimize Your Site for Performance

Answer 3

$tableName = 'customer_entity_varchar';

$db = Mage::getSingleton('core/resource')->getConnection('core_write');

$qry = "SELECT * FROM $tableName WHERE value = 240";

$result = $db->query($qry);

$data = $result->fetchAll();

echo '';print_r($data);

Try this, you can get this data in array format, if you wants to get single data then use $result->fetch()