Drupal 6: Loop through a db_query result more than once

Chris's picture
Comments (5)
Post a new comment
Chris
17 January, 2012 - 15:11

Drupal 6 has a lot of database API commands, although one common task that continues to come up is how to get results out of a query more than once, without repeating the query. I've not seen this documented elsewhere so I thought it was worth noting.

Why bother?

Excellent question. Shouldn't I have written a better query? Our need was to go through the resultset from the db_query and work out the highest and lowest values, then, perform some complex maths on each row that depended on knowing the highest and lowest values.

One possibility was to perform an extra two queries, getting high and low values, but this turned out to be slow. Another would be to order the original query from lowest to highest and just cherry-pick the start and end values, although the ORDER BY on the query turned out to be slow enough to be cumbersome.

It's as easy as...

Assuming we have a $result as the result of a db_query(), we just need to $result->data_seek(0):

  1. $highest = 0;
  2. $lowest = NULL;
  3.  
  4. // Determine high and low values from the query.
  5. while ($record = db_fetch_object($result)) {
  6.   if ($record->count > $highest) {
  7.     $highest = $record->count;
  8.   }
  9.   if ($lowest === NULL || $record->count < $lowest) {
  10.     $lowest = $record->count;
  11.   }
  12. }
  13.  
  14. // Reset the pointer to the first row in the result set.
  15. $result->data_seek(0);
  16.  
  17. while ($record = db_fetch_object($result)) {
  18.   // do complicated calculations to achieve world domination.
  19. }

Nice!

5

Comments

Island Usurper's picture
Island Usurper17 January, 2012 - 18:08

It sounds like you really need an index on your data set. Without knowing what your query or your data set looks like, I can't say for sure that it's going to work. But, with an index, ORDER BY, MIN(), and MAX() all work much faster. SQL really ought to be faster than PHP at this kind of thing.

Also, you can get the lowest and highest in the same query:

SELECT MIN(count) AS lowest, MAX(count) AS highest FROM foo;

Chris's picture
Chris17 January, 2012 - 20:14
All very good points, and in the vast majority of cases this is true, and only one iteration over the result set is needed. However, there are a limited number of very complicated queries on tables that cannot be indexed, or where indexes would be inappropriate or impossible, where it makes more sense to use PHP. Of course, it's up to the reader to make the right decision here, but this was previously unknown to me, so I thought I would make it more accessible to others!
Anonymous's picture
Anonymous18 January, 2012 - 02:32

Why not just store the data into an array and then iterate over that array?

Larry Garfield's picture
Larry Garfield18 January, 2012 - 06:33

I discussed an array-based method for this a while back:

http://www.garfieldtech.com/blog/php-group-by-with-arrays

The advantage there being that you could do forms of grouping and clustering you cannot do in straight SQL.

Chris's picture
Chris18 January, 2012 - 09:14

Array-based storage is certainly useful for custom sorting like you say. Thanks for the link.

However, with a large dataset, you would effectively double the memory requirement, so performance does need to be measured.

If it's a straight choice between dumping everything in an array, or resetting the mysql pointer, I would go for the method in the article above, as it's both faster and less memory-intensive.

Add new comment

Tags: 
Drupal
Drupal Development
PHP
Web Development
Drupal Planet

New job vacancies button