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 .= " \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