Page 1 of 1
Resultset or PHP array?
Posted: Wed Feb 11, 2009 10:57 pm
by alex.barylski
So at work I have this task of taking dozens of transation type scripts and for starters extracting the HTML from the PHP.
In order to do this, I will need to iterate over a resultset of ODBC and convert the resultset into PHP native array. Because all resultsets are minimal (< 20 records) this is not a big deal, but my cohort today asked me whether it was more taxing on the system, to which I replied yes, no, maybe too.
I suppose more memory is consumed as a resultset is only going to return a single record at a time and therefore memory is likely to be reused. However using a PHP array is certainly more natural and it removes any dependency on the ODBC/PDO API.
I hate nothing more than seeing fetch_field() type functions in HTML templates when the template is probably best just passed a variable/array and have it deal with that instead.
Other than the abstraction, can you think of any other arguments in favour of dumping a resultset into an array immediately after query?
About the only time I can think where using a result set is good practice is in the case of sending 1000's of emails where the resultset is very large and it's easier to implement so one doesn't have to track each email address as to see who has received or been sent emails, etc. For general application development and especially rendering of results...
Re: Resultset or PHP array?
Posted: Thu Feb 12, 2009 1:44 am
by Chris Corbyn
How's about having the best of both worlds and using SPL's ArrayObject/iterator stuff? We do this at work and it means we can use RecordSets and Records like arrays. So:
Code: Select all
//$recordSet is an instance of RecordSet and has count(), offset() etc methods
foreach ($recordSet as $record) {
//$record is an instance of Record and has getValue() getBlahBlah() etc methods
printf("Record #%d has title %s\n", $record['id'], $record['title']);
}
Re: Resultset or PHP array?
Posted: Thu Feb 12, 2009 11:37 am
by pickle
Interesting Chris.
So your RecordSet object is an implementation of the ArrayIterator SPL class? And I assume your Record object is a custom object indicating a single row of the query result?
Doesn't this use hella more resources than simply iterating through a MySQL result & treating each row like an array? Guess I'm kind of mirroring ~PCSpectra's original question.
Re: Resultset or PHP array?
Posted: Thu Feb 12, 2009 7:08 pm
by alex.barylski
Chris can you demonstrate with a breif example?
Re: Resultset or PHP array?
Posted: Thu Feb 12, 2009 9:41 pm
by josh
Code: Select all
<?php
/**
* Collection of models
*
* @category Ne8
* @package Ne8_Model
*/
class Ne8_Collection extends Ne8_Base implements IteratorAggregate
{
protected $position = 0;
protected $models = array();
/**
* Set an array of objects in the constructor
*
* @param array of objects to initialize with
* @param string optional type, if passed an exception will be thrown if any class is added that isnt an instance of this
*
* @todo tests for the $type param
*/
public function __construct( array $array = array(), $type = NULL )
{
$this->position = 0;
if( !is_null( $type ) )
{
foreach( $array as $model )
{
Ne8::assertInstance( $model, $type );
}
}
$this->models = $array;
}
public function getIterator()
{
return new ArrayIterator( $this->models );
}
/**
* Adds a certain model from the collection
* @param Ne8_Model to add
*/
public function addModel( Ne8_Model $model)
{
array_push( $this->models, $model );
}
/**
* Removes a certain model from the collection
* @param Ne8_Model to remove
*/
public function removeModel( Ne8_Model $model )
{
unset( $this->models[ $this->getArrayIndexForModel( $model ) ] );
}
public function getModelAtIndex( $index )
{
return $this->models[ $index ];
}
/**
* Searches $this->models and returns the index of the passed model arguments, returns false if we dont have it
* @param Ne8_Model to search for
* @return mixed, integer or false on failure
*/
protected function getArrayIndexForModel( Ne8_Model $model )
{
foreach( $this->models as $index => $each )
{
if( $each->equals( $model ) )
{
return $index;
}
}
return false;
}
public function contains( $compare )
{
if( $compare instanceof Ne8_Model )
{
return $this->containsModel( $compare );
}
else if ( $compare instanceof Ne8_Collection )
{
return $this->containsCollection( $compare );
}
throw new Exception( get_class( $compare ) . ' given, Ne8_Model | Ne8_Collection expected' );
}
/**
* Check if the model is in this collection
* @param Ne8_Model to search for
* @return bool
*/
public function containsModel( Ne8_Model $model )
{
return $this->getArrayIndexForModel( $model ) !== false;
}
/**
* Check if this collection contains another
*
* @param Ne8_Collection collection to compare with
* @return bool
*/
public function containsCollection( Ne8_Collection $collection )
{
foreach( $collection as $model )
{
// a single item not equals means the whole collection is not equal
if( !$this->containsModel( $model ) )
{
return false;
}
}
return true;
}
public function getModels()
{
return new ArrayIterator( $this->models );
}
/**
* Get # of models this collection currently has
*
* @return int count
*/
public function count()
{
return count( $this->models );
}
/**
* Merge a collection into this one
*
* @param Ne8_Collection collection to merge in
*/
public function merge( Ne8_Collection $collection )
{
foreach( $collection as $model )
{
$this->addModel( $model );
}
return $this;
}
/**
* Check if this collection is equal to another
*
* @param Ne8_Collection collection to compare with
* @return bool
*/
public function equals( Ne8_Collection $collection )
{
return( $this->contains( $collection ) && $collection->contains( $this ) );
}
}
* still has too many dependencies but illustrates the point. I have unit tests but they have even more dependencies I still need to refactor.
Re: Resultset or PHP array?
Posted: Fri Feb 13, 2009 10:03 am
by alex.barylski
OK not sure I see how this addresses my issue though. I thought SPL would somehow provide a wrapper around the resultset of a query, so that instead of pulling on the DB specific API within a template or section of code, you would iterate using SPL and it would provide the mechanism to fetch rows, etc.
Thus abstracting yourself from the database while also capitalizing on the fact you were minimizing memory footprint by not dumping the resultset into the an native array???
Re: Resultset or PHP array?
Posted: Fri Feb 13, 2009 10:10 am
by josh
That's lazy load ( ps ) why would you want to introduce ripple loading into the system?
Re: Resultset or PHP array?
Posted: Fri Feb 13, 2009 5:15 pm
by alex.barylski
I have never heard of ripple loading...
Basically this is what I'm working with:
Code: Select all
<?php
$res = odbc_exec('SELECT * FROM table');
foreach(odbc_fetch_something($res) as $row){
echo '<div>First Name: '.odbc_fetch_field($row, 'fname').'</div>';
}
This approach is nasty but it probably is slightly more efficient, despite it being neglible in 99% of cases. Here is how I do it:
Code: Select all
<?php
$arr = array();
$res = odbc_exec('SELECT * FROM table');
while(($arr[] = odbc_fetch($res) !== false);
foreach(odbc_fetch_something($res) as $row){
echo '<div>First Name: '.$arr['fname'].'</div>';
}
I basically dump the entire contents of the resultset into a native array and pass the array to the template layer so I can easily switch DBS if required. Yes it loads the resultset into memory and causes duplication, but:
1. Buffered statements prevent you from running separate queries, so dumping the results to an array is a good fix
2. The array is usually small (within 10-15 records at most) and the overhead is almost insignificant
Working with an array is actually quite possibly faster though, because calling a method to fetch the field from a record stored in a resultset is additional overhead as well, at least with the array it's usually all dumped to memory at once.
I have a resultset object in my Rapid_PDO library that essentially lazy loads the record on each iteration but there was an issue with that design from what I remember so I scrapped it.
Re: Resultset or PHP array?
Posted: Fri Feb 13, 2009 7:06 pm
by josh
Ripple loading is if you lazy load a collection and then iterate it, you're making a network call at each iteration, the solution is to aggressively load your collections w/ special case finder code
Re: Resultset or PHP array?
Posted: Fri Feb 13, 2009 7:34 pm
by Chris Corbyn
pickle wrote:Interesting Chris.
So your RecordSet object is an implementation of the ArrayIterator SPL class? And I assume your Record object is a custom object indicating a single row of the query result?
Well, it's not "my" code really, it's part of SPF, SitePoint's in-house framework, but yes the RecordSet implements ArrayIterator and a Record (used to create a domain object) is a representation of a single row of data. This is not a foreign concept, I'm surprised people find it so interesting
pickle wrote:Doesn't this use hella more resources than simply iterating through a MySQL result & treating each row like an array? Guess I'm kind of mirroring ~PCSpectra's original question.
Probably slightly more resources since you're creating objects yes, but less than filling an array with records (which I think PCSpectra was getting at). Either way, I'm not worried about the miniscule overhead between creating objects and creating arrays. Objects have more benefits when they start getting passed around and/or need more fine grained ways of accessing/mutating data.
PCSpectra wrote:Chris can you demonstrate with a breif example?
The concept is simple, but it wouldn't be all that brief for me to code a simple example here
I'll leave you to figure it out based on the manual http://php.net/spl
Re: Resultset or PHP array?
Posted: Sat Feb 14, 2009 1:14 pm
by alex.barylski
The concept is simple, but it wouldn't be all that brief for me to code a simple example here I'll leave you to figure it out based on the manual
I'm pretty sure I get the concept, in fact I implemented something like this in my own PDO wrapper library:
http://code.google.com/p/pdowrapper/sou ... .cache.php
This class would be used when you want to reduce memory footprint on large datasets like a mailing list, whereas I would use the array alternative in 99% of cases simple because I would prefer to have data in an array which is easily converted into an object.
Cheers,
Alex