[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
_/ _/ _/_/_/_/ _/ _/_/_/ _/ _/