Wojciech Pietron [EMAIL PROTECTED] wrote:
> Hi,
>
> there is a CGI form that enables users obtain information from library.
> We have a set of books (1st table), their authors (2nd table), readers'
> opinions about the book (3rd table), errata of the book (4rd table).
>
> Typical queries:
> - show records with a title 'xxx',
> - show records whose author is 'yyy',
> - show records whose opinions include a word 'zzz',
> - show records whose errata include a word 'qqqq' and author
> starts with 'b'.
>
> A single record should include all the information attached with a book.
> The results may contain many records.
>
> The problems are:
> 1. Is it possible to prepare generic, unchangable SQL query, that will be
> able to meet user's request just by binding its parameters with values he typed?
> What are the best methods of creating dynamic SQL statements based on user
> input? Anyone tried HTML::Template? Any other tools?
I don't know HTML::Template so I can't comment on whether that
can handle creating dynamic html - I'm pretty sure that it
*can* handle it though. Here's the way I do it - create the
different parts of your WHERE clause based on the fields you
get from the form:
sub add_to_where {
my $sql = shift;
my $values_arrayref = shift;
my $column_name = shift;
my $column_value = shift;
if ($sql eq "") {
$sql = "WHERE $column_name = ?\n";
push @$values_arrayref, $column_value;
}
else {
$sql = " AND $column_name = ?\n";
push @$values_arrayref, $column_value;
}
return $sql;
} ### end sub add_to_where
my $sql = {
SELECT name, address, zip, phone
FROM some_table
};
### Assuming you've loaded your form fields into $ variables ###
my @values = ();
my $values_arrayref = [EMAIL PROTECTED];
if ($name) {
$sql = add_to_where($sql, $values_arrayref, "name", $name);
}
if ($phone) {
$sql = add_to_where($sql, $values_arrayref, "phone", $phone);
}
--------------------------------------------
This is completely untested - I hope I haven't butchered it too
much, but it should at least give you the basic idea.
>
> 2. What is the best way to get the data from tables? Should it be done with
> one query that fetches all rows, with many duplicated data? Or maybe one should
> try to do many simpler queries that fetches only distinct data?
It totally depends on what you are trying to accomplish. Since you
haven't included the columns for any of the tables, I can only
guess that the tables have keys that relate them - you *should*
be able to retrieve rows from all the tables in one select, but
I can't confirm that without seeing the columns and the keys for
each table.
These questions really have almost nothing to do with DBI - you
might better be served on a list where you can ask general SQL
questions. If you can do a query with the database client, then
you can do it with DBI.
--
Hardy Merrill
Red Hat, Inc.
