[ic] Creating forms using SQL queries

Kevin Walsh interchange-users@interchange.redhat.com
Tue Jan 1 21:58:01 2002


> > > I'm trying to setup an order page where the customers can select which
> > > charity they would like a portion of their order donated to.
> > >
> > > The first thing I would like them to do is select which state the
> > > charity is located in.  This is what I am thinking:
> > >
> > > [loop list="[query sql="select distinct state from table where 1=1 order
> > > by state" type=list]"]
> > > <option>[sql-state]</option>
> > > [/loop]
> > >
> > > I'm sure there are some syntax errors in there.  My question is, is this
> > > the best way to do it?  Is there some way of accomplishing this without
> > > the query tag?
> > >
> > There is code on the checkout forms in the foundation demo
> > (pages/ord/checkout.html) that creates a list of states.  If you wanted to
> > do it with a query though, 'select distinct state from table order by
state'
> > would be sufficient, you don't need a where clause in this instance.
> >
> If that is a sutable way of going about it, what is the appropriate syntax?
>
There are several ways to create a <select> list.

Here are some of them:

-------------------------------

<select name="state">
    [loop
        option=state
        search=|
            st=db
            fi=states
            ml=1000
            tf=name
            rf=state,name
            sf=country
            se="[value country]"
    |]
    <option value="[loop-code]">[loop-param name]</option>
    [/loop]
</select>

-------------------------------

<select name="state">
    [query
        type=list
        option=state
        sql=|
            SELECT  state, name
            FROM    states
            WHERE   country = "[value country]"
            ORDER BY name
    |]
    <option value="[sql-code]">[sql-param name]</option>
    [/query]
</select>

-------------------------------

[perl tables="states"]
    my $query = qq{
        SELECT  state, name
        FROM    states
        WHERE   country = "$Values->{'country'}"
        ORDER BY name
    };
    my $dbh = $Sql{'states'} or die 'bad DB handle';
    my $resultset = $dbh->selectall_arrayref($query) or die 'Cannot perform
SELECT';
    my $buf = qq{<select name="state">\n};

    foreach (@$resultset){
        my $selected = ' selected' if $_->[0] eq $Values->{'state'};
        $buf .= qq{<option value="$_->[0]"$selected>$_->[1]</option>\n};
    }
    $buf .= qq{</select>\n};
    $buf
[/perl]

-------------------------------

No prizes for spotting the most efficient one.

Feel free to make your own ones up.

--
   _/   _/  _/_/_/_/  _/    _/  _/_/_/  _/    _/
  _/_/_/   _/_/      _/    _/    _/    _/_/  _/   K e v i n   W a l s h
 _/ _/    _/          _/ _/     _/    _/  _/_/    kevin@cursor.uk.com
_/   _/  _/_/_/_/      _/    _/_/_/  _/    _/