Take a look at SQL::Abstract ... it provides easy ways to generate SQL from form or other data. | [reply] |
This is a hosted server and SQL::Abstract module is not available. Is there another way?
| [reply] |
There sure is another way to install SQL::Abstract.
Check Out:
grep
|
Mynd you, mønk bites Kan be pretti nasti... |
| [reply] |
| [reply] |
| [reply]
[d/l] |
- Can we see some code?
- Have you tried asking your system administrator to install SQL::Abstract for you?
| [reply] |
You say there are "six available search fields", so let's consider a realistic example -- a set of parameters like: firstname, lastname, street, city, state, zip, phone. In order to run a dynamic search on any combination of values in those parameters, you could do something like this:
my @param_names = qw(firstname lastname street city state zip phone);
# if column names in the table are different from param names in the f
+orm,
# you'll need a second array for the column names:
my @table_names = qw(fname lname street city st zip phone);
my @query_values;
my @where_clauses;
for my $i ( 0..$#param_names ) {
$_ = param( $param_name[$i] );
s/^\s+//; # remove any leading and trailing whitespace
s/\s+$//;
if (length()) { # anything left?
push @query_values, $_; # hold onto the value
push @where_clauses, "$table_names[$i] = ?";
# use a placeholder in the sql statement
}
}
if ( @where_clauses ) {
my $sql = "select fname,lname,street,id,and_so_on from the_table w
+here ".
join( " and ", @where_clauses );
my $sth = $dbh->prepare( $sql );
$sth->execute( @query_values );
# do what you want with the query results (fetch_whatever)
# ...
}
else {
# no parameters were filled in, so what should you do?
# - tell the client to try again, or
# - query for and deliver the full table contents?
}
That will handle searches for exact matches to specific fields. If you want to support more flexible matches, you could provide a button for the client to pick "equals" vs. "like" for each parameter, and adjust the where_clause condition and query_value string accordingly (e.g., if the "like" button was chosen, use "like" instead of "=" in the where_clause string, and add "%" at the beginning and end of the query_value string).
You might need to do other things with the param value strings before moving them into the query_value array (e.g. uri_unescape), in order for the query to work as intended. | [reply]
[d/l] |
Not sure what your database is as you did not tell us, but it it does allow stored procedures then the most efficient way is to create a stored procedure containing a structure of IFs and a separate SELECT for each combination. That way the server can prepare and store execution plans for each of the cases. You of course do not have to write all those selects by hand, you can generate them
IF (@Name is NULL)
IF (@Address is NULL)
IF (@Email is NULL)
SELECT ...
FROM TheTable
ELSE
SELECT ...
FROM TheTable
WHERE Email like @Email + '%'
ELSE
...
If the SELECTs are more complex or involve complex views then you can save the database server a lot of work regenerating the execution plan.
| [reply]
[d/l] |