Optimise Counting Query

[ben@lookstylish.com]

I have been using a nasty combination of php and mysql to generate a
narrow down by attribute bar as seen on the likes of shopping.com. For
example a user could select 4X Zoom to narrow down a selection of
digital cameras.

The current method I use is to loop though each one of the attributes
and their values and run a separate query of each attribute value:

for($i=0; $i < attribute_count ; $i++) {

for($j=0; $j < attribute_value _count; $j++) {

mysql_query("se lect count(*) from products, attributes where
<attribute conditions>");

}
}

The problem with this approach is that it is very slow. I would like to
be able to combine this code into one query. Is this possible?

Any ideas are appreciated, I have tried caching results with limited
success but would like a "clean" solution to this annoying problem.

[Pedro Graca]

Re: Optimise Counting Query

ben@lookstylish .com wrote:[color=blue]
> The current method I use is to loop though each one of the attributes
> and their values and run a separate query of each attribute value:[/color]

// initialize an empty array of conditions
$conditions = array();
[color=blue]
> for($i=0; $i < attribute_count ; $i++) {
>
> for($j=0; $j < attribute_value _count; $j++) {[/color]

// Move this out of the loop
// replacing it with filling the array
# mysql_query("se lect count(*) from products, attributes where
# <attribute conditions>");

$conditions[] = '(' . <attribute conditions> . ')';
[color=blue]
> }
> }[/color]

// Now explode the array
// and build a valid SQL query command
$sql = "select count(*) from products, attributes where ";
$sql .= implode(' OR ', $conditions);

mysql_query($sq l) or die('Error in query: ' . mysql_error());


--
Mail sent to my "From:" address is publicly readable at http://www.dodgeit.com/
== ** ## !! !! ## ** ==
TEXT-ONLY mail to the complete "Reply-To:" address ("My Name" <my@address>) may
bypass the spam filter. I will answer all pertinent mails from a valid address.