#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2008
    Posts
    244
    Rep Power
    12

    Query Greater Than Less than


    I am doin a query in PHP....the user enters the data from an html page.

    I have it working, but want to expand the query.
    Right now I can search by Greater than a specific amount
    I want to modify the query to say greater than X and less than Y

    Any thoughts on how I can change this to get >110 and <5000

    Do I need to make another user input for < > in the html page.
    But then how to I change the query below?

    THanks
    Code:
    $varPropertyType = @$_GET["Choice"];
    $varPropertyFactor = @$_GET["PriceFactor"];
    $varPriceArea = @$_GET["PropertyValue"];
    
        $sql_str = "SELECT * FROM DISPLAY_RESIDENTIAL_CHARS WHERE Dwelling_Type = '$varPropertyType' AND FF_GBA $varPropertyFactor $varPriceArea";
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2008
    Posts
    244
    Rep Power
    12
    Help on the syntax:

    BEFORE:
    Code:
    $sql_str = "SELECT * FROM DISPLAY_RESIDENTIAL_CHARS WHERE Dwelling_Type = '$varPropertyType' AND FF_GBA $varPropertyFactor $varPriceArea";
    AFTER:
    Code:
    $sql_str = "SELECT * FROM DISPLAY_RESIDENTIAL_CHARS WHERE (Dwelling_Type = '$varPropertyType' AND FF_GBA $varPropertyFactor $varPriceArea) AND (Dwelling_Type = '$varPropertyType' AND FF_GBA $varPropertyFactor2 $varPriceArea2)";
  4. #3
  5. Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2008
    Location
    North Carolina
    Posts
    2,674
    Rep Power
    2674
    That's just asking for SQL injection....
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2008
    Posts
    244
    Rep Power
    12
    I am gettin errors with this:.

    What the heck is SQL injection?

    I have the synstax incorrect...can anyone see whats wrong?

    $sql_str = "SELECT * FROM DISPLAY_RESIDENTIAL_CHARS WHERE ((Dwelling_Type = '$varPropertyType' AND FF_GBA $varPropertyFactor $varPriceArea) AND (Dwelling_Type = '$varPropertyType' AND FF_GBA $varPropertyFactor2 $varPriceArea2))";
  8. #5
  9. Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2008
    Location
    North Carolina
    Posts
    2,674
    Rep Power
    2674
    PHP Code:
    <?
    $varPropertyType    
    $_GET["choice"];
    //$varPropertyFactorX    = $_GET["PriceFactorX"];
    //$varPropertyFactorY    = $_GET["PriceFactorY"];
    // These are no longer needed if you have "Price between X and Y fields" on the form.
    $varPriceAreaX        = (is_numeric($_GET['PropertyValueX']) && $_GET["PropertyValueX"] > 0) ? $_GET["PropertyValueX"] : 0;
    $varPriceAreaY        = (is_numeric($_GET['PropertyValueY']) && $_GET["PropertyValueY"] > 0) ? $_GET["PropertyValueY"] : 0;

    $sql_str "
        SELECT
            *
        FROM
            DISPLAY_RESIDENTIAL_CHARS
        WHERE
            Dwelling_Type = '" 
    mysql_real_escape_string($varPropertyType) . "'
                AND
            FF_GBA > '" 
    mysql_real_escape_string($varPriceAreaX) . "'
                AND
            FF_GBA < '" 
    mysql_real_escape_string($varPriceAreaY) . "'
    "
    ;
    ?>
    Last edited by simshaun; February 10th, 2009 at 12:42 PM.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2008
    Posts
    244
    Rep Power
    12
    what I am doin is having dropdown boxes in the form that allow the user to select > < >= <= = etc...

    I then pass the varaibels to PHP page...
    So I cannto hard code like the exampel you showed me.

    How can I make the > < signs variables with proper syntax

    And what is "mysql_real_escape_string"

    THANKS

    WHERE
    Dwelling_Type = '" . mysql_real_escape_string($varPropertyType) . "'
    AND
    FF_GBA > '" . mysql_real_escape_string($varPriceAreaX) . "'
    AND
    FF_GBA < '" . mysql_real_escape_string($varPriceAreaY) . "'
    ";
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2008
    Posts
    244
    Rep Power
    12
    tried to put your ewxample in (hardcoded)

    I get this: I know that I can get to my server... I have this in the PHP..

    The code works with just > or < so I know I can get to my database. I Get the error when I try and get < >

    $myServer = "LO-9.ORG\MG,9999";
    $myUser = "GISr";
    $myPass = "GIS";
    $myDB = "PDSDB";

    $conn = new COM ("ADODB.Connection")or die("Cannot start ADO");
    $connStr = "PROVIDER=SQLOLEDB;SERVER=".$myServer.";UID=".$myUser.";PWD=".$myPass.";DATABASE=".$myDB;
    $conn->open($connStr);


    ERROR:
    Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in C:\ms4w\apps\GeoMOOSE\htdocs\php\PropertyQuery.php on line 138

    THIS WORKS:
    $sql_str = "SELECT * FROM DISPLAY_RESIDENTIAL_CHARS WHERE Dwelling_Type = '$varPropertyType' AND FF_GBA $varPropertyFactor $varPriceArea";
    Last edited by jaykappy; February 10th, 2009 at 01:02 PM.
  14. #8
  15. Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2008
    Location
    North Carolina
    Posts
    2,674
    Rep Power
    2674
    I assumed you were using MySQL.
    Since you aren't, you can't use mysql_escape_string.

    Kinda busy at work. Gimme a bit and I'll help you out.
    Last edited by simshaun; February 10th, 2009 at 02:02 PM.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2008
    Posts
    244
    Rep Power
    12
    Yea I am using SQL Server

    I am trying to get something liek this..where I am usign varaivles for the <, >, <=, >= signs

    //$sql_str = "SELECT * FROM DISPLAY_RESIDENTIAL_CHARS WHERE Dwelling_Type = '$varPropertyType' AND FF_GBA $varPropertyFactor $varPriceArea AND FF_GBA $varPropertyFactor2 $varPriceArea2";

    Thanks

    THIS WORKS: BUT IS SINGLE > or <
    $sql_str = "SELECT * FROM DISPLAY_RESIDENTIAL_CHARS WHERE Dwelling_Type = '$varPropertyType' AND FF_GBA $varPropertyFactor $varPriceArea";
  18. #10
  19. data transfer technician
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2006
    Location
    Halifax, NS
    Posts
    564
    Rep Power
    134
    Instead of using the $operator var in the $sql string, why not use the variable as a switch to choose the proper $sql template? That way you can be certain that the query you are running is the correct one. It's a bit more verbose but it might be a bit safer.

    e.g.

    php Code:
    switch ($varPropertyFactor) {
      case 'less than':
        $sql="SELECT * FROM DISPLAY_RESIDENTIAL_CHARS WHERE Dwelling_Type = '$varPropertyType' AND FF_GBA < $varPriceArea";
        break;
      case 'greater than':
        $sql="SELECT * FROM DISPLAY_RESIDENTIAL_CHARS WHERE Dwelling_Type = '$varPropertyType' AND FF_GBA > $varPriceArea";
        break;
      case 'between':
        $sql="SELECT * FROM DISPLAY_RESIDENTIAL_CHARS WHERE Dwelling_Type = '$varPropertyType' AND (FF_GBA BETWEEN $varPriceArea AND $varPriceArea2)";
        break;
      default: //equal to
         $sql="SELECT * FROM DISPLAY_RESIDENTIAL_CHARS WHERE Dwelling_Type = '$varPropertyType' AND FF_GBA = $varPriceArea";
        break;
    }


    There are 4 other conditions, (<=, >=, <>, between not including endpoints). I'm not sure if BETWEEN is ANSI SQL so you may need to use your language's equivalent or just use (field>=$x AND field<=$y)
    planning takes time... not planning takes more time.
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2008
    Posts
    244
    Rep Power
    12
    THanks for your response...I can sort of see what you are saying but am confused how I am goin to take the variables from the HTML page, passing them to the PHP page and then fitting in your code....


    Not sure how to fire the code example you have...

    Sorry for the stupid question....a bit green

    THanks
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2008
    Posts
    244
    Rep Power
    12
    I puit this in my code and get this error

    Fatal error: Uncaught exception 'com_exception' with message '<b>Source:</b> Provider<br/><b>Description:</b> Type mismatch.' in C:\ms4w\apps\GeoMOOSE\htdocs\php\PropertyQuery.php:160 Stack trace: #0 C:\ms4w\apps\GeoMOOSE\htdocs\php\PropertyQuery.php(160): com->execute(NULL) #1 {main} thrown in C:\ms4w\apps\GeoMOOSE\htdocs\php\PropertyQuery.php on line 160

    LINE 160:
    $photo_rs = $conn->execute($sql_str);


    PHP:
    switch ($varPropertyFactor)
    {
    case 'LessThan':
    echo "Less Than";
    $sql="SELECT * FROM DISPLAY_RESIDENTIAL_CHARS WHERE Dwelling_Type = '$varPropertyType' AND FF_GBA < $varPriceArea";
    break;
    case 'GreaterThan':
    echo "Greater Than";
    $sql="SELECT * FROM DISPLAY_RESIDENTIAL_CHARS WHERE Dwelling_Type = '$varPropertyType' AND FF_GBA > $varPriceArea";
    break;
    case 'between':
    echo "Between";
    $sql="SELECT * FROM DISPLAY_RESIDENTIAL_CHARS WHERE Dwelling_Type = '$varPropertyType' AND (FF_GBA BETWEEN $varPriceArea AND $varPriceArea2)";
    break; default:
    //equal to $sql="SELECT * FROM DISPLAY_RESIDENTIAL_CHARS WHERE Dwelling_Type = '$varPropertyType' AND FF_GBA = $varPriceArea";
    break;
    }



    HTML

    <p><b>Please Choose Property Type:</b><br>
    <select name="Choice" id="Choice">
    <option value=""></option>
    <option value="Commercial">Commercial</option>
    <option value="Residential">Residential</option>
    <option value="Single Family">Single Family</option>
    <option value="Townhome">Townhome</option>
    <option value="Twinhome/Zero Lot">Twinhome/Zero Lot</option>
    </select> <br>

    <p><b>Please Choose Price Factor:</b><br>
    <select name="PriceFactor" id="PriceFactor">
    <option value=""></option>
    <option value="GreaterThan">Greater Than</option>
    <option value="LessThan">Less Than</option>
    <option value="Between">Between</option>
    <option value="=">Equal To</option>
    <option value=">=">Greater Than Equal To</option>
    <option value="<=">Less Than Equal To</option>
    </select>
    <p><b>Please Choose Area:</b><br>
    <select name="PriceArea" id="PriceArea">
    <option value=""></option>
    <option value="100">100</option>
    <option value="500">500</option>
    <option value="750">750</option>
    <option value="1010">1010</option>
    <option value="1751">1751</option>
    </select>
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2008
    Posts
    244
    Rep Power
    12
    I am getting to the SQL code now although it seems that I keep getting pushed to the default "Equal To"

    My HTML has the value as greaterthan and the Case is the same...why would it go to the Default?


    HTML
    <p><b>Please Choose Price Factor:</b><br>
    <select name="PriceFactor" id="PriceFactor">
    <option value=""></option>
    <option value="greaterthan>">Greater Than</option>
    <option value="lessthan">Less Than</option>
    <option value="between">Between</option>
    <option value=">=">Greater Than Equal To</option>
    <option value="<=">Less Than Equal To</option>
    </select>


    PHP
    switch ($varPropertyFactor)
    {
    case 'lessthan':
    $sql_str="SELECT * FROM DISPLAY_RESIDENTIAL_CHARS WHERE Dwelling_Type = '$varPropertyType' AND FF_GBA < '$varPriceArea'";
    break;
    case 'greaterthan':
    $sql_str="SELECT * FROM DISPLAY_RESIDENTIAL_CHARS WHERE Dwelling_Type = '$varPropertyType' AND FF_GBA > '$varPriceArea'";
    break;
    case 'between':
    $sql_str="SELECT * FROM DISPLAY_RESIDENTIAL_CHARS WHERE Dwelling_Type = '$varPropertyType' AND (FF_GBA BETWEEN $varPriceArea AND $varPriceArea2)";
    break;
    default: //equal to
    echo "Equal TO";
    $sql_str="SELECT * FROM DISPLAY_RESIDENTIAL_CHARS WHERE Dwelling_Type = '$varPropertyType' AND FF_GBA = $varPriceArea";
    break;
    }
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2008
    Posts
    244
    Rep Power
    12
    GOT IT...

    THANK YOU VERY MUCH...

    VERY APPRECIATED!!!!!!!


    HTML CODE:

    Code:
    	<p><b>Please Choose Property Type:</b><br>			   
    	<select name="Choice" id="Choice">
        		<option value=""></option>
        		<option value="Commercial">Commercial</option>
        		<option value="Residential">Residential</option>
        		<option value="Single Family">Single Family</option>
        		<option value="Townhome">Townhome</option>
        		<option value="Twinhome/Zero Lot">Twinhome/Zero Lot</option>
    	</select>	<br>
    	<p><b>Please Choose Price Factor:</b><br>			   
    	<select name="PriceFactor" id="PriceFactor">
        		<option value=""></option>
        		<option value="greaterthan">Greater Than</option>
        		<option value="lessthan">Less Than</option>
        		<option value="between">Between</option>            
    			<option value=">=">Greater Than Equal To</option>
        		<option value="<=">Less Than Equal To</option>
    	</select>
    	<p><b>Please Choose Area:</b><br>			   
    	<select name="PriceArea" id="PriceArea">
        		<option value=""></option>
        		<option value="100">100</option>
        		<option value="500">500</option>
        		<option value="750">750</option>
        		<option value="1010">1010</option>
        		<option value="1751">1751</option>
    	</select>
    
    	<p><b>Please Choose Area2:</b><br>			   
    	<select name="PriceArea2" id="PriceArea2">
        		<option value=""></option>
        		<option value="100">100</option>
        		<option value="500">500</option>
        		<option value="750">750</option>
        		<option value="1010">1010</option>
        		<option value="1751">1751</option>
    	</select>
    
    <script type="text/javascript">
    function writecookie() 
    	{ 
        var varPropertyType = ""
    	var varPropertyType = document.getElementById("Choice").value;	
        var varPriceFactor = ""
    	var varPriceFactor = document.getElementById("PriceFactor").value;
        var varPriceFactor2 = ""
    	var varPriceFactor2 = document.getElementById("PriceFactor2").value;
        var varPropertyValue = ""
    	var varPropertyValue = document.getElementById("PropertyValue").value;
        var varPriceArea = ""
    	var varPriceArea = document.getElementById("PriceArea").value;
        var varPriceArea2 = ""
    	var varPriceArea2 = document.getElementById("PriceArea2").value;
    	
    	//alert(varPropertyType);
    	
    	if (varPropertyType == "" || varPriceFactor == "" || varPropertyValue == "" || varPriceArea == "")
    		{
    			alert('You must Choose values from all four dropdowns');
    		}
    	else
    		{
                	var today = new Date(); 
    				var the_date = new Date("December 31, 2099"); 
    				var the_cookie_date = the_date.toGMTString();	 
    				var the_cookie = "PropertyType=" + varPropertyType;
    				var the_cookie2 = "PriceFactor=" + varPriceFactor;
    				var the_cookie3 = "PriceFactor2=" + varPriceFactor2;  
    				var the_cookie4 = "PropertyValue=" + varPropertyValue;
    				var the_cookie5 = "FF_GBA=" + varPriceArea;				
    				var the_cookie6 = "FF_GBA2=" + varPriceArea2;				
    				
    				var the_cookie = the_cookie + ";expires=" + the_cookie_date; 
    				document.cookie=the_cookie 
    				var the_cookie2 = the_cookie2 + ";expires=" + the_cookie_date; 
    				document.cookie=the_cookie2 
    				var the_cookie3 = the_cookie3 + ";expires=" + the_cookie_date; 
    				document.cookie=the_cookie3 
    				var the_cookie4 = the_cookie4 + ";expires=" + the_cookie_date; 
    				document.cookie=the_cookie4 
    				var the_cookie5 = the_cookie5 + ";expires=" + the_cookie_date; 
    				document.cookie=the_cookie5
    				var the_cookie6 = the_cookie6 + ";expires=" + the_cookie_date; 
    				document.cookie=the_cookie6
    					
    				location = './php/PropertyQuery.php'; 
    				
    	
    				//alert(the_cookie)
    				//alert(the_cookie2)
    				//alert(the_cookie3)
    				//alert(the_cookie4)
    				//alert(the_cookie5)
    				//alert('You are looking for a property that is ' + varPropertyType  + ' and is ' + varPriceFactor + ' ' + varPropertyValue);
       	}	
    
    }
    </script>

    PHP CODE:

    Code:
    echo $varPropertyFactor;
    echo "<br>";
    
    switch ($varPropertyFactor)
    {
    case 'lessthan':
    	$sql_str="SELECT * FROM DISPLAY_RESIDENTIAL_CHARS WHERE Dwelling_Type = '$varPropertyType' AND FF_GBA < '$varPriceArea'";
    	echo $sql_str;
    break;
    case 'greaterthan':
    	$sql_str="SELECT * FROM DISPLAY_RESIDENTIAL_CHARS WHERE Dwelling_Type = '$varPropertyType' AND FF_GBA > '$varPriceArea'";
    	echo $sql_str;
    break;
    case 'between':
    	$sql_str="SELECT * FROM DISPLAY_RESIDENTIAL_CHARS WHERE Dwelling_Type = '$varPropertyType' AND (FF_GBA BETWEEN $varPriceArea AND $varPriceArea2)";
    	echo $sql_str;
    break;
    default: //equal to
    	$sql_str="SELECT * FROM DISPLAY_RESIDENTIAL_CHARS WHERE Dwelling_Type = '$varPropertyType' AND FF_GBA = $varPriceArea";
    	echo $sql_str;
    break;
    }

IMN logo majestic logo threadwatch logo seochat tools logo