liquidd Posted April 25, 2007 Share Posted April 25, 2007 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. Link to comment https://forums.phpfreaks.com/topic/48642-price-filtering/ Share on other sites More sharing options...
rcorlew Posted April 25, 2007 Share Posted April 25, 2007 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. Link to comment https://forums.phpfreaks.com/topic/48642-price-filtering/#findComment-238178 Share on other sites More sharing options...
liquidd Posted April 25, 2007 Author Share Posted April 25, 2007 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. Link to comment https://forums.phpfreaks.com/topic/48642-price-filtering/#findComment-238182 Share on other sites More sharing options...
kalivos Posted April 25, 2007 Share Posted April 25, 2007 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; Link to comment https://forums.phpfreaks.com/topic/48642-price-filtering/#findComment-238277 Share on other sites More sharing options...
liquidd Posted April 25, 2007 Author Share Posted April 25, 2007 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? Link to comment https://forums.phpfreaks.com/topic/48642-price-filtering/#findComment-238294 Share on other sites More sharing options...
kalivos Posted April 25, 2007 Share Posted April 25, 2007 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') Link to comment https://forums.phpfreaks.com/topic/48642-price-filtering/#findComment-238298 Share on other sites More sharing options...
effigy Posted April 25, 2007 Share Posted April 25, 2007 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. Link to comment https://forums.phpfreaks.com/topic/48642-price-filtering/#findComment-238305 Share on other sites More sharing options...
liquidd Posted April 25, 2007 Author Share Posted April 25, 2007 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. Link to comment https://forums.phpfreaks.com/topic/48642-price-filtering/#findComment-238313 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.