Re: Creating and processing forms that access a database

[mikeh@minivend.com]

Quoting Reuven M. Lerner (reuven@lerner.co.il):
> ******    message to minivend-users from "Reuven M. Lerner" <reuven@lerner.co.il>     ******
> 
> Well, Kyle Cook's suggestion definitely helped with submitting a
> simple HTML form and processing it with a MiniVend template.
> 
> But I'm still having a heckuva time trying to interpolate form values
> into an SQL query.  I have a feeling that this is because I haven't
> yet figured out MiniVend interpolation.
> 
> Here is one attempt:
> 
>     [perl interpolate=1]
> 
>        my $id = [value test_select];
> 
>        my $array = [sql type=array]
> 		   SELECT name FROM population
> 		   WHERE id = $id[/sql];
> 
>        my $outputstring = '';
>        my $i;
> 
>        foreach $row (@$array) {
> 	   my $id = $row->[0];
> 	   my $name = $row->[1];
> 	   $outputstring .= qq{<option value="$id">$name\n};
>        }
> 
>        $outputstring;
>     [/perl]
> 
> This created a query that was just fine, aside from that fact that $id
> was assigned the scalar value '[value test_select]'.

MiniVend's SQL stuff is not very good, I will freely admit it. Part
of the problem is that Safe doesn't work with SQL because of its
object creation.

Much better SQL access methods are coming in MiniVend 3.15....but until
now, this should work:

[calc]
    $array = [sql type=array query="
                       SELECT name FROM population
                       WHERE id = [value test_select]
                "]
            [/sql];
    .... the rest of your code...
[/calc]

except I notice that you are only selecting one column and
looking for two in your code...sure it isn't supposed to be
select id,name?

Time to start plugging the new MVASP and Perl object model. So
I think I will take your example a ways...

It has a SQL access method but unfortunately DBI does not work with Safe
so the catalog needs to be in AllowGlobal mode....

[mvasp population]
<SELECT NAME=id>
<%
    my $dbh = $SQL{population}
        or return HTML "Database not shared.";
	my $query = <<EOF;
SELECT id,name from population where id = $Values->{test_select}
EOF
    my $sth = $dbh->prepare($query)
        or return HTML "Couldn't open database.";
    $sth->execute();
    my $array = $sth->fetchall_arrayref();
    for(@$array) {
        HTML qq{<OPTION VALUE="$array->[0]"> $array->[1]};
    }
%>

For the non-SQL, there is a search object for the same
example:

[mvasp]
<%
		$search_spec = {
							ml => 1000,
							se => $Values->{test_select},
							sf => 'population',
							rf => ['id', 'name'],
							fi => 'population.asc',
						};
		my $array = $TextSearch->array($spec);
		for(@$array) {
			HTML qq{<OPTION VALUE="$array->[0]"> $array->[1]};
		}
%>

> I tried to use MiniVend's [arg] tags, but those didn't seem to do
> much, either:
> 
     [perl interpolate=1]
 
        my $array = [sql type=array]
           [arg][value test_select][/arg]
           SELECT name FROM population
           WHERE id = %s[/sql];
 
     ... and so forth ...
 
Add an interpolate=1 to the [sql ... ] tag and that should work
too.

-- 
Mike Heins                          http://www.minivend.com/  ___ 
                                    Internet Robotics        |_ _|____
                                    131 Willow Lane, Floor 2  | ||  _ \
It's a little-known fact            Oxford, OH  45056         | || |_) |
that the Y1K problem caused         <mikeh@minivend.com>     |___|  _ <
the Dark Ages. -- unknown           513.523.7621 FAX 7501        |_| \_\