Gossamer Forum
Home : Products : DBMan SQL : Discussion :

Validate Search

Quote Reply
Validate Search
Hi all....I'm using the Validated Mod with Dbsql....
the problem is that when you do a keyword search the results of the NON-Validated and Validated records are displayed...
below is the query script...can anyone see how to alter it to only diplay the validated records...any help would be appreciated...thanks...Rob




sub query {
# --------------------------------------------------------
# This routines does the actual search of the database.
#
my ($i, $column, @search_fields, @gt_fields, @lt_fields, $bool, $order, $sortby, $nh,
$where, $offset, $field, $field_q, $query, $userid_q, $sth, @tmp, @hits, $count, $comp);

(!$per_admin) and ($in{'Validated'} = "Yes");

# We pass in the type so we can check the userid. The type should either be 'mod', 'del', 'view'.
my ($type) = $_[0];

# Start by setting some options. Pick our boolean operator: OR or AND. Pick our sort order
# and our sort by preferences as well.
$in{'so'} ? ($order = $in{'so'}) : ($order = "ASC");
$in{'sb'} and ($sortby = "ORDER BY $in{'sb'} $order");
$in{'ma'} ? ($bool = "OR") : ($bool = "AND");
$in{'nh'} ? ($nh = $in{'nh'}) : ($nh = 1);
$in{'mh'} ? ($maxhits = $in{'mh'}) : ($maxhits = $db_max_hits);
$in{'ww'} ? ($comp = '=') : ($comp = 'LIKE');

# If this is a keyword search, we want to set every field in the database to the
# search term.
if ($in{'keyword'}) {
$in{'ma'} = "on"; $bool = 'OR';
foreach $column (@db_cols) {
next if ($db_lengths{$column} > 255); # Can't search on TEXT/BLOB fields.
next if ($in{'keyword'} !~ /^\d+$/ and $db_is_int{$column});
push (@search_fields, $column); # Search every column
$in{$column} = $in{'keyword'}; # Fill %in with keyword we are looking for.
}
}
# Otherwise, we only add fields that have information to search on.
else {
foreach $column (@db_cols) {
if ($in{$column} =~ /^\>(.+)$/) { ($db_sort{$column} eq 'date') and (&date_to_unix($1) or return "Invalid date format: '$1'");
push (@gt_fields, $column); $in{"$column-gt"} = $1; next; }
if ($in{$column} =~ /^\<(.+)$/) { ($db_sort{$column} eq 'date') and (&date_to_unix($1) or return "Invalid date format: '$1'");
push (@lt_fields, $column); $in{"$column-lt"} = $1; next; }
if ($in{$column} !~ /^\s*$/) { ($db_sort{$column} eq 'date') and (&date_to_unix($in{$column}) or return "Invalid date format: '$in{$column}'");
push (@search_fields, $column); next; }
if ($in{"$column-gt"} !~ /^\s*$/) { ($db_sort{$column} eq 'date') and (&date_to_unix($in{"$column-gt"}) or return "Invalid date format: '$in{$column}'");
push (@gt_fields, $column); }
if ($in{"$column-lt"} !~ /^\s*$/) { ($db_sort{$column} eq 'date') and (&date_to_unix($in{"$column-lt"}) or return "Invalid date format: '$in{$column}'");
push (@lt_fields, $column); }
}
}
if (! $db_msql) {
foreach (keys %db_indexed) {
if ($in{$_}) {
($sth, $count) = &query_index ($_, $in{$_}, $nh, $maxhits, $bool, $in{'ww'});
defined $sth or return ("no matching results");
$index_search = 1;
last;
}
}
}
if (! $index_search) {
if (!@search_fields and !@gt_fields and !@lt_fields) {
return ("no search terms specified."); # Make sure we are actually looking for
} # something.
# We quote each word for special characters, but the quote functions puts the enclosing
# ' around the word. Since we want to add % before and after, we have to remove those.

# MSQL: We use CLIKE instead of LIKE to do case insensitive searches. We also have to jump
# some hoops so that msql doesn't complain when comparing integer values.
foreach $field (@search_fields) {
# We are looking at integers.
if ($db_is_int{$field}) {
if ($in{$field} eq "*") { # Special case as we can't use LIKE on int, we match >= 0
$where .= qq!
$field >= 0 $bool!;
}
else {
$field_q = int($in{$field});
next unless ($field_q =~ /^\d+$/);
$where .= qq!
$field = $field_q $bool!;
}
}
else {
# We are looking at strings.
if ($in{$field} eq "*") {
$where .= qq!
$field $comp '%' $bool!;
}
else {

$field_q = $DBH->quote ($in{$field});
$field_q =~ s/^'(.*)'$/'%$1%'/ unless $in{'ww'}; # Remove quotes ' so we can do % matchings.
$where .= qq!
$field $comp $field_q $bool!;
}
}
}
foreach $field (@lt_fields) {
$db_is_int{$field} ?
($field_q = int($in{"$field-lt"})) :
($field_q = $DBH->quote($in{"$field-lt"}));
$where .= qq!
$field <= $field_q $bool!;
}
foreach $field (@gt_fields) {
$db_is_int{$field} ?
($field_q = int($in{"$field-gt"})) :
($field_q = $DBH->quote($in{"$field-gt"}));
$where .= qq!
$field >= $field_q $bool!;
}
# Chop off the trailing AND or OR.
chop ($where); chop ($where); chop ($where);



# If we are only allowed to mod/del/view our own records let's make
# sure we don't pull up anyone else's information.
if ((($type eq 'mod' and $auth_modify_own) or
($type eq 'del' and $auth_modify_own) or
($type eq 'view' and $auth_view_own)) and (!$per_admin)) {
$userid_q = $DBH->quote($db_userid);
$where .= qq! AND
$auth_user_field = $userid_q!;
}

$where and ($where = "WHERE $where");

# Now let's work out the next url if there are more hits to be had and
# if the database can use the LIMIT with offset function.
$offset = ($nh - 1) * $maxhits;

# Now let's pull up the relevant records.
# MSQL: Does not support LIMIT OFFSET, HITS, so we have to get all the hits and
# then throw away the ones we don't want.
$count = $offset + $maxhits;
$db_msql ?
($query = qq!
SELECT ! . join(",", @db_cols) . qq! FROM $db_table
$where
$sortby
!):
($query = qq!
SELECT ! . join(",", @db_cols) . qq! FROM $db_table
$where
$sortby
LIMIT $offset, $maxhits
!);
$sth = $DBH->prepare ($query) or &cgierr("Unable to query database. Reason: $DBI::errstr. Query: $query");
$sth->execute or &cgierr("Unable to query database. Reason: $DBI::errstr. Query: $query");

# If we can do a Next Hits link then let's find how many links there are. If
# this is mSQL, we have just calculated the total using $count above. If it
# is a db that supports count(*) then we can figure out the total using the
# following:
if (!$db_msql) {
while (@tmp = $sth->fetchrow_array) {
push (@hits, @tmp);
}
my $query2 = qq!
SELECT COUNT(*) FROM $db_table
$where
!;
$sth = $DBH->prepare ($query2) or &cgierr("Unable to query database. Reason: $DBI::errstr. Query: $query2");
$sth->execute or &cgierr("Unable to query database. Reason: $DBI::errstr. Query: $query2");
($count) = $sth->fetchrow_array;
}
else {
$count = 0;
while (@tmp = $sth->fetchrow_array) {
next if (($count++ < $offset) or ($count > $offset + $maxhits));
push (@hits, @tmp);
}
}
}
else {
while (@tmp = $sth->fetchrow_array) {
push (@hits, @tmp);
}
}

# Set a global $db_total_hits to be used in the html.
$db_total_hits = $count;

# If we have to many hits, let's build the next toolbar, and return only the hits we want.
if ($count > $maxhits) {
my ($next_url, $next_hit, $prev_hit, $left, $right, $upper, $lower, $first, $last, $numhits);

# Remove the nh= from the query string.
$next_url = $ENV{'QUERY_STRING'};
$next_url =~ s/\&nh=\d+//;
$next_hit = $nh + 1;
$prev_hit = $nh - 1;
$numhits = $count;

# Build the next hits toolbar. It seems really complicated as we have to do
# some number crunching to keep track of where we are on the toolbar, and so
# that the toolbar stays centred.

# First, set how many pages we have on the left and the right.
$left = $nh; $right = int($numhits/$maxhits) - $nh;
# Then work out what page number we can go above and below.
($left > 7) ? ($lower = $left - 7) : ($lower = 1);
($right > 7) ? ($upper = $nh + 7) : ($upper = int($numhits/$maxhits) + 1);
# Finally, adjust those page numbers if we are near an endpoint.
(7 - $nh >= 0) and ($upper = $upper + (8 - $nh));
($nh > ($numhits/$maxhits - 7)) and ($lower = $lower - ($nh - int($numhits/$maxhits - 7) - 1));
$db_next_hits = "";
# Then let's go through the pages and build the HTML.
($nh > 1) and ($db_next_hits .= qq~<a href="$db_script_url?$next_url&nh=$prev_hit">[<<]</a> ~);
for ($i = 1; $i <= int($numhits/$maxhits) + 1; $i++) {
if ($i < $lower) { $db_next_hits .= " ... "; $i = ($lower-1); next; }
if ($i > $upper) { $db_next_hits .= " ... "; last; }
($i == $nh) ?
($db_next_hits .= qq~$i ~) :
($db_next_hits .= qq~<a href="$db_script_url?$next_url&nh=$i">$i</a> ~);
if ($i * $maxhits == $count) { $nh == $i and $next_hit = $i; last; }
}
$db_next_hits .= qq~<a href="$db_script_url?$next_url&nh=$next_hit">[>>]</a> ~ unless ($next_hit == $i);
}

# Bold the results
if ($db_bold and $in{'view_records'}) {
for $i (0 .. (($#hits+1) / ($#db_cols+1)) - 1) {
$offset = $i * ($#db_cols+1);
foreach $field (@search_fields) {
$hits[$db_def{$field}[0] + $offset] =~ s,(<[^>]+>)|(\Q$in{$field}\E),defined($1) ? $1 : "<B>$2</B>",ge;
}
}
}

# Clean up!
$sth->finish;
return ("ok", @hits);
}


Quote Reply
Re: Validate Search In reply to
It is not within the query subroutine that would be the easiest change, but adding a hidden field in your search form:

Code:

<input type="hidden" name="Validate" value="Yes">


In terms of editing it from the back-end, the better routine to look at is the sub view_search routine in the db.cgi....you will need to add conditional statements for the Validate field also admin perm, so that you as admin can search ALL records and other users will only be able to find VALIDATED records.

Good luck!

Regards,

Eliot Lee