Gossamer Forum
Home : Products : DBMan : Customization :

Records with biggest difference between two fields

Quote Reply
Records with biggest difference between two fields
Hi!

I'm trying to solve a wish from our users. Our database contains two fields - "price of spare part from manufacturer" and "price of spare part from others". The goal is to be able to see where you can get spare parts at the best price and how much you save. Our users now want a list of e.g. the 5 records with the biggest difference between price from manufacturer and others.

So I need a routine to check each record, compare these two fields, and then list the 5 records with the biggest difference between these two fields.

I've searched through this forum and Karen's site at hypermart, but I didn't find anything similar to this wish.

I appreciate all the time you spend helping others and I hope someone can help me out here.

Thank you.

Regards

Batdilla

http://baatplassen.no
Quote Reply
Re: [oktrg500] Records with biggest difference between two fields In reply to
You can certainly create a new variable that is the difference between 2 fields, the question is where in the code to do it?

I would guess that this part of db.cgi is where you would create the new variable;

Code:
# Normal searches.
$key_match = 0;
foreach $field (@search_fields) {
$_ = $values[$field]; # Reg function works on $_.
$in{'ma'} ?
($key_match = ($key_match or &{$regexp_func[$field]})) :
(&{$regexp_func[$field]} or next LINE);
}

and this part is where you would specify to sort by the new variable;

Code:
# Did we find a match? We only add the hit to the @hits array if we need it. We can
# skip it if we are not sorting and it's not in our first < > last range.
if ($key_match || (!($in{'keyword'}) && !($in{'ma'}))) {
if (exists $in{'sb'}) {
$sortby{(($#hits+1) / ($#db_cols+1))} = $values[$in{'sb'}];
push (@hits, @values);
}
else {
(($numhits >= $first) and ($numhits <= $last)) and push (@hits, @values);
}
$numhits++; # But we always count it!
}
}
close DB;

I'll think about it more.
Quote Reply
Re: [joematt] Records with biggest difference between two fields In reply to
Hi,

Great, I'll watch this thread closely for suggestions. Thank you.

Regards

Batdilla

http://baatplassen.no