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);
}