I have a search form where user can search for products within their specified price range. Wherein I have 2 input boxes 1 is for the starting range and the other is for the ending range.
I have 3 products stores in my database table. With prices 2300.0000, 1200.0000, 1000.5000 respectively.
Im having a problem with selecting values within the inputted price range. The problem is that, when I input values like
100 starting range to 1100 ending range. The products with 1200.0000 and 1000.5000 is being displayed instead of only the 1000.5000 product.0 starting range to 1100 ending range or 1000.50 ending range. The products with 1200.0000 and 1000.5000 is being displayed instead of only the 1000.5000 product.1100 starting range. Only the product with 2300.0000 is being displayed instead of both 2300.0000 and 1200.0000. But when the starting values is less than or equal 500 all the 3 products is being displayed.This is the code i am using.
function db_prepare_input($string)
{
if (is_string($string)) {
return trim(stripslashes($string));
} elseif (is_array($string)) {
reset($string);
while (list($key, $value) = each($string)) {
$string[$key] = db_prepare_input($value);
}
return mysql_real_escape_string($string);
} else {
return mysql_real_escape_string($string);
}
}
$pricefrom_key = db_prepare_input(number_format($_GET['pricefrom'], 4, '.', ''));
$priceto_key = db_prepare_input(number_format($_GET['priceto'], 4, '.', ''));
// Price Range
if (!empty($_GET['pricefrom']) && !empty($_GET['priceto'])) {
$price_range = " having price >= ".$pricefrom_key." and price <= ".$priceto_key;
}
elseif(empty($_GET['pricefrom']) && !empty($_GET['priceto']))
{
$price_range = " having price >= 0 and price <= ".$priceto_key;
}
elseif(!empty($_GET['pricefrom']) && empty($_GET['priceto']))
{
$price_range = " having price >= ".$pricefrom_key;
}
elseif(!empty($_GET['pricefrom']) && !empty($_GET['priceto']))
{
$price_range = "";
}
$catglobal_sql = "select p.blog_id, p.global_category_id, p.products_id, p.products_currency, p.products_type, p.products_name, p.products_description, p.products_quantity, p.products_model, p.products_image, p.products_price, p.products_date_added, p.products_last_modified, p.products_date_available, p.products_weight, p.products_status, p.products_tax_class_id, p.manufacturers_id, p.products_ordered, p.specials_new_products_price, p.specials_date_added, p.specials_last_modified, p.expires_date, p.date_status_change, p.status, p.display_product, case when (p.specials_new_products_price > 0 or p.specials_new_products_price != 0000-00-00 and p.expires_date > Now() and p.status != 0) then p.specials_new_products_price else p.products_price end price from ".TABLE_GLOBAL_PRODUCTS." p INNER JOIN ".TABLE_STORES." s ON s.blog_id = p.blog_id where MATCH (p.products_name,p.products_description) AGAINST ('%".$search_key."%') OR p.products_name like '%".$search_key."%' and s.countries_id = '168' ".$search_cat." and p.display_product = '1' and p.products_status = '1' ".$price_range." order by p.products_date_added DESC, p.products_name";
I tried, adding die($catglobal_sql); and the result is,
select p.blog_id, p.global_category_id, p.products_id, p.products_currency, p.products_type, p.products_name, p.products_description, p.products_quantity, p.products_model, p.products_image, p.products_price, p.products_date_added, p.products_last_modified, p.products_date_available, p.products_weight, p.products_status, p.products_tax_class_id, p.manufacturers_id, p.products_ordered, p.specials_new_products_price, p.specials_date_added, p.specials_last_modified, p.expires_date, p.date_status_change, p.status, p.display_product, case when (p.specials_new_products_price > 0 or p.specials_new_products_price != 0000-00-00 and p.expires_date > Now() and p.status != 0) then p.specials_new_products_price else p.products_price end price from wp_global_products_table p INNER JOIN wp_blogs s ON s.blog_id = p.blog_id where MATCH (p.products_name,p.products_description) AGAINST ('%cotton%') OR p.products_name like '%cotton%' and s.countries_id = '168' and p.products_currency = 'php' and p.display_product = '1' and p.products_status = '1' and p.products_type = 'a' having price >= 0 and price <= 1200.0000 order by p.products_date_added DESC, p.products_name
What seems to be the problem??
I have found the problem.. Its on the case.. i have modified it to case when p.specials_new_products_price >= 0.0000 and p.expires_date > Now() and p.status != 0 then p.specials_new_products_price else p.products_price end price
What exactly are you doing with this line..
$price_range = " having price >= ".$pricefrom_key." and price <= ".$priceto_key;
Shouldn't you just set the $price_range i.e.:
if (!empty($_GET['pricefrom']) && !empty($_GET['priceto']))
{
if($price_range >= $pricefrom_key && $price_range <= $priceto_key)
{
echo "you are in the pricerange from" .$pricefrom_key." to ".$priceto_key;
}
//etc......
}
Edit: since some did not get my answer, this was just to point into the right direction. It does not matter if us use "echo" statement or SQL query, you still need to specify the range properly. The statement:
$price_range = " having price >= ".$pricefrom_key." and price <= ".$priceto_key;
.. pretty much says: price range = some text . number . some text . number the < = > operators are in " " which means TEXT and they will be ignored. I hope this makes sense.