Re: Postgres access using PHP3

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 .= "&nbsp;\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>&nbsp;</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(at)catalyst(dot)net(dot)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