Solved

PHP - Range Search

Posted on 2008-10-08
5
296 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:Squinky
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:Squinky
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Importing and exporting data Magento 1.x ? 4 35
PHP Sum Column in Table 3 27
Problem to page 4 21
sql sentence 2 12
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.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

708 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

12 Experts available now in Live!

Get 1:1 Help Now