Hi stephenhb,
I agree with you, and I faced that issue as well. The problem is that we don't get a choice of clearing the mysql result resource when calling e.g. loadRow and loadObject (I believe these would be the closest methods to what you're mentioning). The resource is cleared automatically straight after getting the first record.
Although I really don't like to modify core joomla files, here's what I did in the libraries/joomla/database/database/mysql.php file:
Code: Select all
function loadRow( $first=true, $clear=true )
{
if( $first ) {
if( !$cur = $this->query() ) {
return null;
}
} else {
if( !$cur = $this->_cursor ) {
return null;
}
}
$ret = null;
if ( $row = mysql_fetch_row( $cur ) ) {
$ret = $row;
if( $clear ) {
mysql_free_result( $cur );
}
} else {
mysql_free_result( $cur );
}
return $ret;
}
It's compatible with current uses of loadRow thanks to the default values of $first and $clear.
Now if I use loadRow( false, false ) I'm actually keeping the mysql resource (not doing the query again) and moving pointer to the next record. In the end the resource is cleared.
Then to obtain what you mentioned, I use: (in a model)
Code: Select all
// any query
$query = 'SELECT * FROM #__weblinks WHERE 1';
$this->_db->setQuery( $query );
// First record, need to create the mysql resource
$row = $this->_db->loadRow( true, false ); // do what you need with this row
// Now go through all the rows without reloading the mysql resource
while( $row = $this->_db->loadRow( false, false ) ) {
// do what you need with each row
echo $row[0];
}
The $clear parameter helps if you want to stop your iteration on the way and still clear the resource properly.
Did the same with loadObject() as well.
But maybe there is a reason for the behavior chosen by the core developers. If so, it would be great if someone could let us know.