Filter individual fields on PHP form

[pabirds]

I have a form linked to a recordset.

On the form, I want to display the contents of field2 when field1 equals a value that I insert in the code.

For instance: A recordset has a field called Color and another field called Quantity.

On the form, next to the word "Red", I want to display the contents of the field "Quantity" when "Color" = "red"

I DO NOT want to use Repeat Region to list all of this as a table. Instead, I want to place the fields where I want them.

So I might have something like the following:

Red (34)      Blue (12)

Green (16)  Black (45)

Etc.

Is there a way to place a condition (Color = "Red") on an individual field without having to create a seperate recordset for every field (I have 67 fields on this form!)?

Can I drag a recordset field onto the text field and then add a filter to it?

[bregent]

Please show us your database design and SQL query you are using for the recordset.

[pabirds]

The database table in my example is called Hats and has two fields: Color and Quantity

The sql query is

SELECT * FROM Hats

[bregent]

OK, I don't really know PHP but an overall strategy could be to load the recordset into a 2 dimensional associative array.  Then you could could reference the row attribute (qty) by the 'color'.