Price Filtering

[liquidd]

Hey all,

 

I am fairly new to PHP coming off of ASP. I truly like the language and don't think I'll ever go back to ASP.

 

I am having an issue with filtering price from a form variable so maybe someone can tell me how I'm screwing this up.

 

I have a search page that has 3 criterias for search - category 'cat', price 'price' and location 'area'. I am using Dreamweaver 8 for this project. I can create the query for both cat and area for the double filter, but when i get to price it removes all the records from view.

 

I removed the 2 working queries and am now focusing on the 'price' query. My SQL looks something like this:

 

SELECT Pcategory, Pprice, Paddress

FROM propertydata

WHERE ((Pprice = colname))

ORDER BY Pprice ASC

 

VARIABLE:

Name: colname

Type: Text

Default Value: -1

Runtime Value: $_POST['price']

 

Executing the search page returns all records with "$0" for a price (about 70) and thats all. No matter which price choice i use.

 

My form page that passes the variable is set up like this:

 

<select name="price" id="price">

                        <option value="BETWEEN 0 AND 100000">Under $100,000</option>

                        <option value="BETWEEN 100000 AND 200000">$100,000 - $200,000</option>

                        <option value="BETWEEN 200000 AND 300000" selected>$200,000 - $300,000</option>

                        <option value="BETWEEN 300000 AND 400000">$300,000 - $400,000</option>

                        <option value="BETWEEN 400000 AND 500000">$400,000 - $500,000</option>

...and so on.

 

And i if add another query to this equation like 'cat' nothing shows up...

 

If you need actual code let me know and i'll post. Can anyone tell my why this is coming up like this? I appreciate the help.

[rcorlew]

Yeah, post a small amount of code if you will, I think the prob is you are not using a variable to set the Pprice amount.

[liquidd]

Thanks for the quick reply..

 

This is what i have:

 

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

 

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

 

  switch ($theType) {

    case "text":

      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

      break;   

    case "long":

    case "int":

      $theValue = ($theValue != "") ? intval($theValue) : "NULL";

      break;

    case "double":

      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";

      break;

    case "date":

      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

      break;

    case "defined":

      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

      break;

  }

  return $theValue;

}

}

 

$colname_rssearch = "-1";

if (isset($_POST['price'])) {

  $colname_rssearch = $_POST['price'];

}

mysql_select_db($database_propertydata, $propertydata);

$query_rssearch = sprintf("SELECT Pcategory, Pprice, Paddress FROM propertydata WHERE ((Plist_price = %s)) ORDER BY Plist_price ASC", GetSQLValueString($colname_rssearch, "text"));

$rssearch = mysql_query($query_rssearch, $propertydata) or die(mysql_error());

$row_rssearch = mysql_fetch_assoc($rssearch);

$totalRows_rssearch = mysql_num_rows($rssearch);

 

Thanks. I appreciate the help.

[kalivos]

Try braking it down further. Do both return the expected results?

$val = GetSQLValueString($colname_rssearch, "text");
$query_rssearch = "SELECT Pcategory, Pprice, Paddress FROM propertydata WHERE Plist_price = ".$val." ORDER BY Plist_price ASC";

echo "Val: ".$val;
echo "Query: ".$query_rssearch;

[liquidd]

I think after dissecting it deeper it may be in my SQL query not so much in my code because all my code tests for the other filters work fine.

 

I use:

 

WHERE ((Pprice = '100000'))

 

and it works great when i press test. it shows all records with that amount.

 

When i use (this is what I'm trying to achieve):

 

WHERE ((Plist_price = 'BETWEEN 0 AND 100000'))

 

this shows all records just at priced at "0"

 

I'm beginning to think it its the SQL query that is messing me up. Meaning that my SQL query passing from the form variable is wrong. Any thoughts?

[kalivos]

I could be showing my n00bness here, but I've never seen 'Between' used that way. I have however seen it done like this...

 

WHERE (Plist_price >= '0' AND Plist_price <='100000')

[effigy]

Plist_price = 'BETWEEN 0 AND 100000'

...should be...

Plist_price BETWEEN 0 AND 100000

...because the first is a literal string, when it should be the BETWEEN MySQL operator.

[liquidd]

Effigy - You are right. That does work in the PHPmyadmin query box, but somehow not in the Dreamweaver query box. 

 

Kalivos, the way you illustrated works, but eventually i will have to pass this query though a form and i am trying to make it easier. However in thinking i could try that way in the form and see the results if all else fails.

 

Thanks everyone.