Form Variable Filtering

[liquidd]

Hey all,

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.

[Piranha]

It is hard to evaluate with untested code that you have here since it might contain a spelling error or something like that. But I'll give it a try anyway.

In the select statement you don't use the variable $colname, but that should probably return an error instead of returning everything with the price 0. It might be a problem, but probably not. Anyway it is easy to check.

What data type do you have in the Pprice column? If it is int or any other numeric data type it should be ok, otherwise it would be a good idea to use that instead.

Have you tried to insert the price directly in the query? Like this:

SELECT Pcategory, Pprice, Paddress
FROM propertydata
WHERE ((Pprice = 4))
ORDER BY Pprice ASC

If that works it is probably something with the variable or PHP coding. If it doesn't work the problem is with the database.

[liquidd]

Thanks for the quick response. My data type is int(12).
I can pull the price directly as you illustrated with no problems. This works great in ASP so i know i'm missing something small.
Here is my PHP code if that help.

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);

I appreciate the help A LOT.

[Piranha]

I can't see any obvious error. I suggest that you try to debug by echoing $_POST['price'], $colname_rssearch and $query_rssearch. Then you will at least know where you have the correct value and where the error is.

[liquidd]

Ok, i will do that and post results. Thanks

[liquidd]

I don't know if this helps, but I just now narrowed something down. Please tell me the correct SQL query to use for this.

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 from variable is wrong. Any thoughts?

[Piranha]

The ' signs that you use makes everything into one variable. So the second query above tries to get a column where the value is "BETWEEN 0 AND 100000" literally. If you check the data type you can use numeric values without ' signs without problems for your database. The where part of the query should then be:

WHERE Plist_price BETWEEN 0 AND 100000

[liquidd]

You are absolutely right. I tested it and it worked great. We'll see if i can put this together with the form.

Thanks.