1

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?

  • 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. – Jonathan Hussey Aug 5 '14 at 13:55
5

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

  • 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. – siliconrockstar Dec 4 '17 at 5:07
  • 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. – fantasticrice Jun 19 '18 at 19:25
7

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

  • REALLY don't recommend this approach. – Jonathan Hussey Aug 5 '14 at 14:09
  • 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. – Shadowbob Oct 31 '17 at 10:20
  • @Shadowbob look at what fetch returns and you will understand. – vitoriodachef Nov 19 '18 at 9:03
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.