asked on

PHP - Range Search

Ive used a program called Web Form Generator (http://www.webformgenerator.com/) to construct a template webpage I have been customizing to my needs. The one function I am having problems with is the range search field outputting very strange results. It appears to be searching by the first character of the range irregardless of decimal position or value.
ie. a search range of 1 to 19 results in a majority of good results 1.15 or 13.2 but there are bad results like 120.3 and missing data thats being omitted.
It appears as if its limiting the search string to the first character if thats possible. A 0 (zero) search returns the correct results completely.
I would also like to mention this range function is being used for dates, dollar amounts, and numbers with fractions in decimal. The data is stored in mysql as a varchar for the three fields mentioned previously. I know thats not a good way of doing it but the data has bad characters like dollar signs and slashes for the dates so if theres a better way of doing please explain.

What I need to accomplish is accurate results however that may be accomplished without impacting the rest of the functionality of the site to much. Thanks for your assistance. (this is my first question on EE)

I believe this is the section of code I need to modify in order to improve the search results for ranges only.  Since there are other search options in the same form.
 
function getWhereClause($formdata){
        $this->filterdata = $formdata;
        $this->actPageNr = 1;
        reset($formdata);
        $str = "";
        while (list($key, $val) = each($formdata)) {
            if ($val != '') {
            	$pos = strrpos($key,'_');
                if ($pos>0) {
                    if (substr($key,$pos) == '_min') {
                        $str .= " AND ".substr($key,0,strlen($key)-4)." >= '$val' ";
                    }
                    if (substr($key,$pos) == '_max') {
                        $str .= " AND ".substr($key,0,strlen($key)-4)." <= '$val' ";
                    }
                    if (substr($key,$pos) == '_val') {
                        $baseFiled=substr($key,0,strlen($key)-4);
                        if (isset($formdata[$baseFiled.'_mode'])) {
                            $mode = $baseFiled.'_mode';
                        	if ($formdata[$mode] == 'EXACTLY') {
                        		$str .= " AND $baseFiled='$val' ";
                        	}
                        	elseif (($formdata[$mode] == 'LIKE')){
                        	    $str .= " AND $baseFiled LIKE '$val%' ";
                        	}
                        }
                        else {
                      	    $str .= " AND $baseFiled LIKE '$val%' ";
                        }
                    }
                    
 
                }
            	
            }
        }    
        return $this->whereStr=" WHERE 1 $str";
    }

Open in new window

more.bmp

Marcus Bointon

You need to get your context right: when compared as strings, 120 is less than 19. Try doing it without quoting the values:

" >= $val "

but note that that will ONLY work with numeric values, not strings (which will give you SQL errors), so you need to be a little careful - for example check is_numeric($val) before building a query that way.

usaevo7

ASKER

Removing the quotes prevented the page from loading at all. So I assume it has something to do with the precautions you mentioned. Is there a better way of performing this function that would end with the results Im looking for? Please explain "get your context right."

Marcus Bointon

Context refers to the meaning of the place in which values are used. For example this would be a string context:

$answer = 10;
$a = 'the answer is '. $answer;

and this is a numeric context:

$answer = $answer + 1;

So while 120 > 19, "120" < "19".

usaevo7

ASKER

Is there some way I should rewrite the question in order to make it clearer what Im looking to accomplish? Is there information I am not providing that would help in finding a solution? I really dont have the technical level to correctly modify the function above in order to get it working correctly.

Ive increased the point value slightly.
Thanks

ASKER CERTIFIED SOLUTION

usaevo7

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial