?
Solved

PHP - Range Search

Posted on 2008-10-08
5
Medium Priority
?
309 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

JavaScript has plenty of pieces of code people often just copy/paste from somewhere but never quite fully understand. Self-Executing functions are just one good example that I'll try to demystify here.
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

752 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