Solved

PHP - Range Search

Posted on 2008-10-08
5
299 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

I found this questions asking how to do this in many different forums, so I will describe here how to implement a solution using PHP and AJAX. The logical flow for the problem should be: Write an event handler for the first drop down box to get …
Browsers only know CSS so your awesome SASS code needs to be translated into normal CSS. Here I'll try to explain what you should aim for in order to take full advantage of SASS.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now