Solved

PHP - Range Search

Posted on 2008-10-08
5
306 Views
Last Modified: 2013-12-12
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
0
Comment
Question by:usaevo7
  • 3
  • 2
5 Comments
 
LVL 25

Expert Comment

by:Marcus Bointon
ID: 22677035
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.
0
 

Author Comment

by:usaevo7
ID: 22678362
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."
0
 
LVL 25

Expert Comment

by:Marcus Bointon
ID: 22678743
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".
0
 

Author Comment

by:usaevo7
ID: 22687830
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
0
 

Accepted Solution

by:
usaevo7 earned 0 total points
ID: 22949893
The correction I needed to make was with the data type in the database.  Thanks
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question