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?

• Jonathan Hussey: If you are pulling that much data into PHP to process it then you are asking for trouble from the offset. Look for a better more memory efficient way to achieve the same result and pull only the data you need from the database. Also, importantly, you should be working with a collection in the situation above - using MySQL directly is a big no-no particularly as Magento is already a good way down the road to supporting multiple databases.

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/.

• siliconrockstar: Note: this answer forgoes record set iteration and instead uses OO collection walking. Useful 99% of the time, but if you know what you're doing and actually want to use PDO-style iteration, see Renon Stewart's answer below.
• fantasticrice: This is helpful, but you should always explain and excerpt the code in your answer while citing the source in case the link goes dead later.

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

• Jonathan Hussey: REALLY don't recommend this approach.
• Shadoweb: Good code! But DO NOT try to be smart and add it to 1 line like this while ($row = $read->query($select)->fetch()) as it will create an infinite loop, and this I have no idea why.
• vitoriodachef: @Shadowbob look at what fetch returns and you will understand.

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()