[ic] Re: UNSOLVED: Database range-lookup question, CommonAdjust?

[interchange-users@icdevgroup.org (Ron Phipps)]

> From: Interchange user
> 
> On Thursday 05 September 2002 09:30 pm, you wrote:
> 
> > Interchange user wrote:
> > >This may be a MySQL question; I'm not sure, but someone could save
me
> > >countless hours of searching and frustration if they could answer
this
> for
> > > me:
> > >
> > >I have a custom shipping routine that currently does a lookup into
a
> MySQL
> > >database table and gets a price for an exact weight.
> > >
> > >I want to make a change so that the exact weight of the items can
be
> used
> > > to make a range lookup in the table, for example:
> > >
> > >weight	zone1
> > >
> > >60	0.27
> > >100	0.41
> > >150	0.57
> > >200	0.72
> > >
> > >A weight of 1 to 60 costs 0.27, a weight of 61 to 100 costs 0.41,
and
> so
> > > on.
> > >
> > >Can Interchange's 'CommonAdjust' feature be used for this? If so,
how?
> >
> > I believe your are mixing apples and oranges as CommonAdjust is for
> > product pricing.  I believe what you want to do is already built in
with
> > the use of the shipping.asc file which allows pricing based on
quantity,
> > value, or weight...  I'd encourage you to take a look at that file,
I
> > believe that's where you want to start looking first...
> >
> > Barry
> 
> Barry,
> 
> I wouldn't want to use shipping.asc for that, because there are loads
of
> weight ranges, and they need to be in a db table.
> 
> Someone has got to have done this.

Perhaps you can change your table structure to look like this:

min_weight	max_weight	zone1
0		60		0.27
60		100		0.41
100		150		0.57

Then you could do a query like:

[tmp weight]79[/tmp]

[query sql="SELECT zone1 FROM shipping WHERE [scratch weight] >
min_weight AND [scratch weight] <= max_weight" type=list]
	The zone1 value for a weight of [scratch weight] is [sql-param
zone1]
[/query]

Or you may be able to get the value with your existing structure by
doing:

SELECT MAX(weight) AS max_weight , zone1 FROM shipping WHERE [scratch
weight] <= max_weight

Good luck,
-Ron