noobie-ish question - selecting into a string

Howdy,

I'm wading my way through some postgresql that's just a little beyond me
right now. (PG 7.3.4, PHP 4.3.4, Fedora Core 1)

Could someone tell me how to do this, or show me a better way?

I'm creating some dynamic forms, from db tables like this:

table1: chemical - longish (700+) list of chemicals, makes for a chunky
drop-down
chemical_id serial
chemical_name varchar

table2: chemical_type - 4 rows, each chemical has from 1-4 types
associated, so table 3 maps
chemical_type_id serial
chemical_type varchar

table3: chemical_chemical_type_map
chemical_id (fkeyed to chemical.chemical_id)
chemical_type_id (fkeyed to chemical_type.chemical_type_id)

I need [x] dropdowns, determined by user input on the previous page. The
option values are the serial chemical_id from chemical, and the visible
text is chemical.chemical_name.

The same chemical may be listed more than once (with different
chemical_id's in the chemical table as well - once as type 1, once as type
2, and maybe again as type 3 & 4 as well. I need to differentiate between
them in the form.

So what I need in this is a text string of the chemical_type_id values to
put into the dropdowns after the chemical names.

I guess I don't need to look at the chemical_type table, but it's there to
supply a full picture.

Is there a way to select the [1-4] values from chemical_type into the
result as a string? (Or is there an entirely different way to do this?)

$chemical_query = "
SELECT c.*
, [this would be where I'm stuck...] as chemical_type_list
FROM chemical c
, chemical_chemical_type_map cctm
WHERE c.chemical_id = cctm.chemical_id
ORDER BY c.chemical_id
";

$chemical_result = pg_query($chemical_query);

The resulting form would be generated like so:

for($i=1; $i<=$_REQUEST['x']; $i++) {
print ("Chemical $i: <select name=\"chemical_$i\">\n");
while($chem_array = pg_fetch_array($chemical_result) {
print ("<option
value=\"{$chem_array['chemical_id']}\">{$chem_array['chemical_name']}
({$chem_array['chemical_type_list']})</option>\n");
} // end while loop
pg_result_seek($chemical_result,0);
print ("</select><br><br>\n");
} // end for loop

Many, many thanks!

-Ben

-------------------------------
--------- Ben Riddell ---------
--- Freelance Web Developer ---
- ben at thewhitebear dot com -
----- www.thewhitebear.com ----
------- 510/332.2979 (c) ------
-------------------------------

As nightfall does not come at once, neither does oppression. In both
instances there is a twilight when everything remains seemingly unchanged.
And it is in such twilight that we all must be most aware of change in the
air - however slight - lest we become unwitting victims of the darkness.
-William O. Douglas, judge (1898-1980)