I am trying to modify the search part of the script to accept ranges for a numeric value. I found a mod that allows me to add a drop down list with pre-built ranges (i.e. 0-499, 500-999, 1,000+), but I need to be able to add 2 fields where users can enter minimum and/or maximum values. Any ideas out there?

That's much easier to do.

First, you're going to need to create a subroutine to create a search form which is different from your add/modify form.

Copy sub html_record_form. Paste it back into the file, just below the current subroutine. You will now have two identical subroutines.

Change the name of the newly-pasted subroutine to sub html_search_form.

In sub html_view_search, change &html_record_form(); to &html_search_form();. In sub html_view_failure, change &html_record_form(%in); to &html_search_form(%in);.

Back up in your new sub html_search_form, find the field that you want to search for ranges on. I'm going to give you an example, using a field called Number. Wherever you see Number, change it to the name of your field.

That's it!

JPD

OK, I understand JPD's use of two seperate text fields to input your min & max range.
However is it possible to contain a fixed range within a single drop-down list? The original poster (rayhurst) says he has done this but how? There is nothing on the DBMan Modifications page...

Best regards, Brian

It's listed under the FAQs. You can pick it up at http://www.jpdeni.com/.../Mods/PriceRange.txt.

The mod is for price ranges, but I'm sure you'll be able to work out how to adapt it to whatever you need.

JPD

Got it!

Thanks JP. What a fantastic program this is!!

Brian

It is a great program, isn't it? (I can say that because I didn't write it. )

Glad I could help.

JPD
http://www.jpdeni.com/dbman/

Can this be modified to "greater than or equal to" and "less than or equal to"....?


...OK ...found it.

In sub query, after local ($sortby);

if ($in{'ID-gt'}) {
-- $in{'ID-gt'} ;
}

if ($in{'ID-lt'}) {
++$in{'ID-lt'} ;
}

Perhaps a range search mod/tutorial could be put in the Resource Centre.

OK, got this working when there is only one drop down list with ranges. However, if I use two the search range fields, results screw up. The screw up is between the two fields in question.

What I have done:
1] Edited the html.pl file accordingly to add the 2 drop-down lists
2] edited db.cgi as follows:

-----
#Added by BC for using price ranges in the search form 15/6/00
if ($in{'Price'}) {
unless ($in{'Price'} eq "*") {
($in{'Price-gt'},$in{'Price-lt'}) = split (/-/,$in{'Price'});
$in{'Price'} = '';
}
}

if ($in{'cc'}) {
unless ($in{'cc'} eq "*") {
($in{'cc-gt'},$in{'cc-lt'}) = split (/-/,$in{'cc'});
$in{'cc'} = '';
}
}
#end of BC add
-----

cc stands for 'cubic capacity' ie. the database is for used cars.

When I search by price range, the displayed results are bold for the cc field and visa versa if searching by cc. I have checked the obvious - making sure html fields have not been mixed e.g. Price = $rec{'cc'} !!

Am I missing something??

Best regards, Brian

I don't know what could be causing that.

Is there any way I could get access to your database in action? There's some things I'd like to try that might help. (These things are too hard to explain, or I'd give you the instructions and you could do it yourself. )

If you don't want to post the URL to your database on the forum, you can send it to me in a private message. Just click on my name in the post.

JPD
http://www.jpdeni.com/dbman/

Hi JP

Did you get my personal post? I think it went to goddess@nospam.com

Best regards, Brian

That's not a real email address.

But, yes, I did get a message from you, which I answered.

JPD
http://www.jpdeni.com/dbman/

Hi JP

I have not received any reply?? Could you please re-post (brian@omegadm.co.uk), or post to the list if relevent.

Best regards, Brian

I don't have it any more. It was a private message within the forum. Take a look when you're on the main forum page and see if you have any private messages waiting. Or just click the "Check Private" link at the top of any page within the forum.

JPD
http://www.jpdeni.com/dbman/

OK back to the list...

Your suggested change appears to have also solved the mix up in search results when using two drop-down list (not sure why!). Anyhow, what should now be in the db.cgi?

I have:
-----
if ($in{'Price'}) {
unless ($in{'Price'} eq "*") {
($in{'Price-gt'},$in{'Price-lt'}) = split (/-/,$in{'Price'});
$in{'Price'} = '';
}
}
---

should "*" be replaced with "---" ??

Best regards, Brian

You can just change it to



JPD
http://www.jpdeni.com/dbman/

Hi JPD et al. Still continuing with this! Within the range, I am using decimals e.g.

<select name="cc">
<option value="---">Any size
<option value="0-1.000">Up to 1 litre
<option value="1.001-1.600">1.0 - 1.6 litres
<option value="1.601-2.000">1.6 - 2.0 litres
<option value="2.001">2 litres and above
</select>

However, cars who's cc is on the boundary e.g. 1.0, 1.6, 2.0 are NOT returned in the list of results. This can be viewed at:
http://www.omegadm.co.uk/scripts/dbman/cdgdb.pl?db=cdg&uid=default&view_search=1

There are 25 1.0 litre cars!

Best regards, Brian

If you'll notice on my price range mod, I have options such as

<OPTION value="249999-500001">\$250,000 - \$500,000

Since DBMan searches for "greater than" and "less than," but not "greater than or equal to" or "less than or equal to," you need to make an adjustment in your select field.

I'm not exactly sure what you want. Do you want the 1.0 litre to be returned when the user selects

<option value="0-1.000">Up to 1 litre

or when the user selects

<option value="1.001-1.600">1.0 - 1.6 litres

or both?

JPD
http://www.jpdeni.com/dbman/

Got it! I now have:

<select name="cc">
<option value="---">Any size
<option value="0-1.0001">Up to 1 litre
<option value="1.0001-1.6001">1.0 - 1.6 litres
<option value="1.6002-2.0001">1.6 - 2.0 litres
<option value="2.0002">Above 2 litres
</select>

My previous example had a 'hole' at the boundaries.

Cheers JPD.