Thread: Postgres access using PHP3

Postgres access using PHP3

From

John Poltorak

Date:

19 January 2001, 13:37:26


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.

--
John

Re: Postgres access using PHP3

From

Andy Holman

Date:

19 January 2001, 19:14:08

John,
Not sure exactly what you mean. Do you know the
column names to the table in the db or is that
what you are trying to get?  Or do you mean you
are just trying to print out the results of a
query into a drop down list?

Let me know and I will give you some code.

--Andy

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.
>
> --
> John

Re: Postgres access using PHP3

From

John Poltorak

Date:

19 January 2001, 19:54:53

On Fri, Jan 19, 2001 at 04:13:21PM -0500, Andy Holman wrote:
> John,
> Not sure exactly what you mean. Do you know the
> column names to the table in the db or is that
> what you are trying to get?  Or do you mean you
> are just trying to print out the results of a
> query into a drop down list?
>
> Let me know and I will give you some code.

Hi Andy,

I'm going to attach some sample PHP code which hopefully illustrates
what I mean...

The the first selection offers a list of monthnames. What I want to
do instead is provide a list of values from a table ie such as towns.
The selected town will then be used for the WHERE clause in a subsequent
SELECT.

I hope you understand what I'm getting at.


> --Andy
>
>
>
> 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.
> >
> > --
> > John

--
John

Attachment

M:\repository\$$$.CDs\CORE_PHP\EXAMPLES\18-11.php3

Re: Postgres access using PHP3

From

Andy Holman

Date:

19 January 2001, 20:13:06

John,
What you will need to do is create a table that has the
town names in it.
Example:

CREATE TABLE towns
(
town_id serial,
townName text,
PRIMARY KEY(town_id, townName)
);

SO... your query would look something like

SELECT townName FROM towns;

I never used the pgsql functions in php so you will
have to look those up on php.net, I normally use
phpLib which allows me to write for many different DB
types.

But I think it would look something like:

$query = "SELECT townName FROM towns";
$result = pg_exec($connectid, $query);

echo "<SELECT NAME=\"towns\">";
while($row = pg_fetch_array($result))
{
    echo "<option
value=\"".$row["townName"]."\">".$row["townName"]."</option>";
}
echo "</SELECT>";

The above, (keep in mind my pgsql functions may be off), but that should
print out your list of townNames in a option list.

Hope this helps.

--Andy


John Poltorak wrote:
>
> On Fri, Jan 19, 2001 at 04:13:21PM -0500, Andy Holman wrote:
> > John,
> > Not sure exactly what you mean. Do you know the
> > column names to the table in the db or is that
> > what you are trying to get?  Or do you mean you
> > are just trying to print out the results of a
> > query into a drop down list?
> >
> > Let me know and I will give you some code.
>
> Hi Andy,
>
> I'm going to attach some sample PHP code which hopefully illustrates
> what I mean...
>
> The the first selection offers a list of monthnames. What I want to
> do instead is provide a list of values from a table ie such as towns.
> The selected town will then be used for the WHERE clause in a subsequent
> SELECT.
>
> I hope you understand what I'm getting at.
>
> > --Andy
> >
> >
> >
> > 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.
> > >
> > > --
> > > John
>
> --
> John
>
>   ------------------------------------------------------------------------
>
>    M:\repository\$$$.CDs\CORE_PHP\EXAMPLES\18-11.php3Name: M:\repository\$$$.CDs\CORE_PHP\EXAMPLES\18-11.php3
>                                                      Type: Plain Text (text/plain)

Re: Postgres access using PHP3

From

Andrew McMillan

Date:

20 January 2001, 09:33:19

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