1

In programming languages there are 2 patterns for selecting records from the database:

//prepare SELECT statement
while($record = $query->fetchRow())
{
    //do the job
}

And the second:

//prepare SELECT statement
$recordsArray = $query->fetchAllRows();
//do the job with all the records in a 2-dimensional array

To my surprise only the second option is available in JDatabaseQuery: loadRowList, loadAssocList etc.

When I tried:

$query = $db->getQuery(true);
$query->select(array('id', 'name'))
    ->from($db->quoteName('#__my_table'));

$db->setQuery($query);
while($row = $db->loadAssoc())
{
    var_dump($row);
}

It hanged. Can I fetch the records one by one rather then get a 2-dimensional array?

1

There are quite a few methods of Joomla's db driver that are not documented on the doc page. The API summary page for 3.8 is here

https://api.joomla.org/cms-3/classes/JDatabaseDriver.html

What you're looking for is the iterator.

$query = $db->getQuery(true);
$query->select('id,name')->from($db->qn('#__my_table'));
$db->setQuery($query);
$iterator = $db->getIterator();
foreach($iterator as $row){
    var_dump($row);
}

It will return one stdClass at a time and you can 'break' out of the loop to stop it running.

  • We can get a record as a class only? No arrays? – user4035 Feb 4 '18 at 1:07
  • The iterator only returns objects (which you could just cast to arrays as they're always simple 1 dimensional objects). Is there a reason you want 1 row at a time? Why not just load the data and loop through it? If there's too many to hold in memory then just chunk through. I've never seen anyone use the iterator. – jamesgarrett Feb 4 '18 at 1:16
  • I thought that using an iterator may be faster as inside loadAssocList Joomla must be iterating across the records, attaching them to array. – user4035 Feb 4 '18 at 1:27

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.