Hi all!

I have been reading numerous posts on how to setup a "greater than OR equal to" search, but these are all too specific for my needs.
What I need is a MOD (better: addition) to the db.cgi script that lets me specify say: "date-gte" for a search, and still keep the original "date-gt" search working. (I am using the "date-gt" extensively already, and don't want to rewrite all of the existing html and Perl.)

Can anybody offer me some help, my Perl is not good enough to understand what is happening in the sub query.

Thanks in advance,
Richard.

Hm. Just off the cuff - in sub query, you have a foreach loop below the line:
else {# Otherwise this is a regular search, and we only want records

Then you have three "if"-blocks which parse input at store the numbers of fields to be searched in the arrays @search_gt_fields, @search_lt_fields, or @search_fields.

It seems to me that you could simply add another "if" block:

This effectively checks whether you have ">=" at the beginning of a search field, and, if so, marks that database field as subject to both "greater than" and "equal" searches, without affecting the other routines.
I haven't tested this, and I'm not sure whether the output would be as desired, but it kind of seems logical.

Cheers,
kellner

Kellner :-)
Hm, yes, that does seem to make sense!
I'm starting to understand the way the search works in Query.

And then also add somthin like:

if ($in{"$column-gte"} !~ /^\s*$/)

so I can have a greater than OR equal field:
INPUT TYPE="text" NAME="date-gte"

I'll check it out, to see if this is the way to go. Of course I also need to add for LessThanOrEqual, and for the other field types as well ....
I'll let you know, thanks already!

Richard.

Glad my idea helped.
If you want to do this thing with a lot of fields, or perhaps want to have a standard "greater than or equal" form where search on *each* field will be a "greater than or equal search", then you could do this differently: Add a hidden input field to the form:
<input type="hidden" name="greater_than_or_equal" value="1">
And then, in sub query further below where you have the if-blocks previously mentioned:
This, I believe, would do a "greater than or equal" search on all fields in the input form where search terms were entered.
Again, the code is untested, but hopefully helpful ...

cheers,

kellner

Well, this bit did it for me:

push (@search_gt_fields, $i);
push (@search_fields, $i);$i++;

then "the coin fell" (as we say over here ... :)

I need to have the choice to use a search field as "GreaterThan" or "GreaterThanEqual" for EVERY field (= text input box), and also ">=" and "<=" and ">" and "<" working.
Especially for date fields it is not convenient to only have the "-gt" and "-lt" options. Searching for all entries in october means you need to find out the number of days in September .... :-(

I'll try to hack this into db.cgi tomorrow, and post the changes for comments.
This would make a useful thing for others too I guess.

Thanks again!
Richard

Hi, back again.

I ran into some difficulties trying to adapt it in the way you proposed. There is a reference later on in the sub Query (where the actual search is done) that made me change my approach:
$term = $in{"$db_cols[$field]-gt"

This meant I couldnt just change a few lines, and had to change more than I hoped. So, I got all my courage together and eventually got it to work with these changes:

Added "@search_gte-fields" and "@search_lt_fields" to declaration at start of Query;
Added after
# that match everything the user specified for.
foreach $column (@db_cols) {"

these lines:

if ($in{$column} =~ /^\>=(.+)$/) { ($db_sort{$column} eq 'date') and (&date_to_unix($1) or return "Invalid date format: '$1'");
push (@search_gte_fields, $i); $in{"$column-gte"} = $1; $i++; next; }

Also added:
if ($in{"$column-gte"} !~ /^\s*$/) { ($db_sort{$column} eq 'date') and (&date_to_unix($in{"$column-gte"}) or return qq|Invalid date format: '$in{"$column-gte"}'|);
push(@search_gte_fields, $i);}

and similar for -lte before the same lines referring to $column-gt and $column-lt.

And to finish it all off, I added this to where the actual searching is done (once for -gte and one for -lte):

foreach $field (@search_gte_fields) {
$term = $in{"$db_cols[$field]-gte"};
if ($db_sort{$db_cols[$field]} eq "date") {
in{'ma'} ?
($key_match = ($key_match or (&date_to_unix($values[$field])) >= &date_to_unix($term))) :
(&date_to_unix($values[$field]) >= (&date_to_unix($term)) or next LINE);
}

elsif ($db_sort{$db_cols[$field]} eq 'alpha') {
$in{'ma'} ?
($key_match = ($key_match or ($values[$field] >= $term))) :
((lc($values[$field]) ge lc($term)) or next LINE);
}
else {;
$in{'ma'} ?
($key_match = ($key_match or ($values[$field] >= $term))) :
(($values[$field] >= $term) or next LINE);
}
}


I am not quite sure about the last series of lines, but it all seems to work perfectly. Maybe the bugs will hit as soon as I have it all installed on the customers' server .... ?

Any tips or remarks so far?
Full code of new sub Query is here (cant get the code formatted here):
www.looping.nl/Transport/Sub_Query.txt

Regards,
Richard.

Well, the line you quote is not a problem; I had already taken care of that:

foreach $column (@db_cols) {($db_sort{$column} eq 'date') and (&date_to_unix($in{$column}) or return "Invalid date format: $in{$column}");
$in{"$column-gt"} = $in{$column};# Note this line!
...
}

So when you have a search in the field "date", and you want greater than or equal, then the line with "Note this line!" next to it first declares $in{'date-gt'} to be the same as $in{'date'}, and then pushes the field name into @search_fields_gt. (You might need the double quotes around $column-gt, though, I had forgotten those in my code.)

So when you later have $term = $in{"$db_cols[$field]-gt"} - which is in fact the same as $term = $in{"$column-gt"}, then that value will be available.

What difficulties did you actually run into?






kellner

With the changes I was getting results as if all records matched. As long as I put in a valid date all records would show up.
I was thinking this was because we were adding new search-parameters
$in{"$column-lt"} = $1;
while evaluating them.

BTW: i put the changes after:
if ($in{"$column-gte"} !~ /^\s*$/) {
.... etc
because that's what I needed most.

I am happy it works now, but your method is more 'graceful' so maybe I can give it another shot?

Richard.

Sorry for being stubborn, but I'd like to find out what is not wrong with my code, irrespective of whether yours works :-)
For clarification - if you want to use ">=" to signal GTE-searches, and use *exactly* the following code, it shouldn' return GTE on all columns, because the code tests on the presence of ">=" first.
I think this is the best solution if you only want to use GTE on some fields in your form, the second code I have you, with the foreach-loop, was designed for the specific purpose that you want to use it on *all* fields of a form.

if ($in{$column} =~ /^\>=(.+)$/) {
($db_sort{$column} eq 'date') and (&date_to_unix($1) or return "Invalid date format: $1");
$in{"$column-gt"} = $1;
$in{$column} = $1;
push (@search_gt_fields, $i);
push (@search_fields, $i);
$i++;next;
}

Please let me know whether *this* code produces the error you described.

If you also want to call a gte-search using "$column-gte" as a field-name, you could put this line before the above code:
if ($in{"$column-gte"}) { $in{$column} = qq|>=$in{"$column-gte"}|;}



kellner