Re: Postgres access using PHP3 - Mailing list pgsql-novice
From | Andrew McMillan |
---|---|
Subject | Re: Postgres access using PHP3 |
Date | |
Msg-id | 3A697729.40328E5B@catalyst.net.nz Whole thread Raw |
In response to | Postgres access using PHP3 (John Poltorak <jp@eyup.org>) |
List | pgsql-novice |
John Poltorak wrote: > > I'm trying out PHP3 for accesing Postgres through a Web interface > and am having a problem coming up with the correct code... > > I'd like to retreive a list of values for a key field and then > provide this to the user in the form of a drop down selection list, > but I can't work out how to get the values into an array. > > I must be missing something as I can't a way of doing it... > > Any sample code snippets would be much appreciated. Here's what I use - it's a generalised function for returning a bunch of codes from a database tabel called 'codetable' - here's the PSQL first: CREATE TABLE codetable ( table_id TEXT, code TEXT, seq INT4, description TEXT, misc TEXT, PRIMARY KEY ( table_id, code ) ); CREATE INDEX codetable_sk1 ON codetable ( table_id, seq, code ); I find that in every database I have a bunch of things that I want validated against a standard set of codes in this way, but I don't really want to use up the namespace of my database by creating a table for each such code. Hence this approach of a generic table for codes in general. The dissociation of 'sequence' from 'code' is important, as is the magic 'misc' field to contain 'whatever else is necessary... If I can't do it with this table then it generally means I should be creating a real table for the data. Having this table of codes also simplifies maintenance - I have one script that I can generically use to maintain these values, without having to write (and maintain) something to maintain codes in a whole lot of separate tables. Now the PHP (I use PHP4, but this worked under PHP3 as well, I seem to recall). Note that this function actually will give you a radio set if you set the 4th parameter to "radio" - that's pretty fragile because it doesn't validate that parameter. The other trick this does, is that it lets you pass in an existing value (as $current) and that will be selected. function get_code_list( $table_id, $current="", $misc="", $tag="option", $varname="" ) { global $dbconn; $rid = pg_Exec( $dbconn, "SELECT * FROM codetable WHERE table_id = '$table_id' ORDER BY table_id, seq, code"); $rows = pg_NumRows( $rid ); $lookup_code_list = ""; if ( $tag <> "option" ) { $prestuff = "input type="; $selected = " checked"; } else $selected = " selected"; for ( $i=0; $i < $rows; $i++ ) { $lookup_code = pg_Fetch_Object( $rid, $i ); $lookup_code_list .= "<$prestuff$tag value=\"$lookup_code->code\""; if ( "$varname" <> "" ) $lookup_code_list .= " name=$varname"; if ( "$lookup_code->code" == "$current" ) $lookup_code_list .= $selected; $lookup_code_list .= ">"; $lookup_code_list .= "$lookup_code->description"; if ( "$misc" <> "" && "$lookup_code->misc" <> "") $lookup_code_list .= " - $lookup_code->misc"; if ( "$tag" == "option" ) $lookup_code_list .= "</$tag>"; else $lookup_code_list .= " \n"; } return $lookup_code_list; } Obviously I could have my database connection pased into the routine, but I prefer to leave it global in all of my scripts. The main limitation (at present) is that it doesn't handle more than one value for $current - this could be useful for a multi-select, for example. I leave that enhancement as an exercise for the student :-) Now, some examples of how I use it :-) echo "<tr><th class=rows>Author Type</th>\n<td>"; show_help("author.author_type"); $author_types = get_code_list( "author-type", "$new_author_type"); echo "<select name=new_author_type>$author_types</select></td></tr>\n"; echo "<tr><th class=rows>Status</th>\n<td>"; show_help("author.status"); $author_statuses = get_code_list( "author-status", "$new_author_status"); echo "<select name=new_author_status>$author_statuses</select></td></tr>\n"; or, showing off the radio-set way of doing it :-) Note that you have to supply both $tag and $varname in this case, to get reasonable HTML out of it. if ( $roles[Admin] ) { $status_list = get_code_list( "user-status", "$usr->status", "", "radio", "UserStatus" ); ?> <tr> <th> </th> <th align=right><font Size="2">User Status:</th> <td><font Size="2"><?php echo $status_list; ?></td> </tr> <?php } // end of 'if Admin... ' Hope this is all useful to you. Cheers, Andrew. -- _____________________________________________________________________ Andrew McMillan, e-mail: Andrew@catalyst.net.nz Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267
pgsql-novice by date: