Skip site navigation (1) Skip section navigation (2)

noobie-ish question - selecting into a string

From: Ben Riddell <ben(at)thewhitebear(dot)com>
To: sfpug(at)postgresql(dot)org
Subject: noobie-ish question - selecting into a string
Date: 2005-04-25 20:11:20
Message-ID: 6.1.2.0.2.20050425124015.1034db70@mail.thewhitebear.com (view raw or flat)
Thread:
Lists: sfpug
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)

Responses

sfpug by date

Next:From: Josh BerkusDate: 2005-04-25 22:43:32
Subject: Re: noobie-ish question - selecting into a string
Previous:From: Josh BerkusDate: 2005-04-25 17:59:28
Subject: Re: ACM paper?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group