And I already have min/max price range working properly (just not active in my search form)..
Thanks!
Hi, I'm trying to incorporate price ranges within my search results, similar to the brand drop-down box that I used from these threads:
http://www.pricetapestry.com/node/1749
based on: http://www.pricetapestry.com/node/212
In other words, within the search results, I'm looking for a new dropdown box to narrow the products based on 0-50, 51-100, etc.
Having added the comment about min/max price ranges, they aren't working properly :) - the results aren't within the price range, so something has been disconnected with my search.php. I've been pretty good at making changes using forum suggestions, but never applied the min/max at the beginning
Here are search.php snippets that were changed for the brand filter:
/* HERE */
if ($_GET["brandFilter"])
{
$where .= "AND brand='".database_safe($_GET["brandFilter"])."' ";
}
if ($_GET["categoryFilter"])
{
$where .= "AND category='".database_safe($_GET["categoryFilter"])."' ";
}
$sql = "SELECT *, IF(currency='GBP',(MIN(price) * 1.9684),MIN(price)) AS minPrice, MAX( price ) AS maxPrice, IF(currency='GBP',(saleprice * 1.9684),saleprice) AS display_saleprice, COUNT( id ) AS numMerchants, MATCH (name,description,brand) AGAINST ('".database_safe($match)."' IN BOOLEAN MODE) AS relevance FROM `".$config_databaseTablePrefix."products` WHERE ".$where." GROUP BY name";
and...
if ($parts[0] == "category")
{
$sql = "SELECT DISTINCT brand FROM `".$config_databaseTablePrefix."products` WHERE ".$where." ORDER BY brand";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
$brands[$row["brand"]] = $row["brand"];
}
}
print "<form method='GET' action='".$config_baseHREF."search.php' style='display: inline; margin: 0;'>";
widget_selectArray("brandFilter",$brands,$_GET["brandFilter"],"");
print "<input type='hidden' name='q' value='".$q."' />";
print "<input type='submit' value='brand' />";
print "</form><br/>";
}Any help much appreciated!
Thanks
T.
Hi,
Could you email me your search.php as an attachment and i'll take a look for you - should be reasonably straight forward to add a drop-down once the min/max price mods are working correctly..
Cheers,
David.
Hi David, just looking for a little guidance on this one.. I have isolated the fields that add a new filter, just not sure how to hard-code ranges for min/max.
Thanks!
T.
Hi,
If you want to hard code a link with a particular price range, just add the minPrice and maxPrice fields to the URL; for example:
http://www.example.com/search.php?q=category:Widgets:&minPrice=10.00&maxPrice=50.00...so you could code a link as follows
<a href='http://www.example.com/search.php?q=category:Widgets:&minPrice=10.00&maxPrice=50.00'>Widgets from £10 to £50</a>However, doing this in a single dropdown is not going to be so straight forward without a bit of JavaScript; so just thinking out loud, what might be a better solution is to define a new parameter that can specify both min and max price in one value; and then split this up at the top of search.php. Where you currently have:
$minPrice = ($_GET["minPrice"]?sprintf("%.2f",$_GET["minPrice"]):"");
$maxPrice = ($_GET["maxPrice"]?sprintf("%.2f",$_GET["maxPrice"]):"");Change this as follows:
if ($_GET["priceFilter"])
{
$parts = explode("|",$_GET["priceFilter"]);
$_GET["minPrice"] = $parts[0];
$_GET["maxPrice"] = $parts[1];
}
$minPrice = ($_GET["minPrice"]?sprintf("%.2f",$_GET["minPrice"]):"");
$maxPrice = ($_GET["maxPrice"]?sprintf("%.2f",$_GET["maxPrice"]):"");This modification will mean that you can specify the price range in a single parameter - priceFilter, separating min and max price with the pipe "|" character, for example:
http://www.example.com/search.php?q=category:Widgets:&priceFilter=10.00|50.00...so this is what you can use in the drop-down, in exactly the same way as brandFilter or categoryFilter....
Hope this helps!
Cheers,
David.
Hi David, seems to work quite well (just what I needed, thanks!), but I've hit a little snag..
The minPrice & maxPrice don't seem to accept the currency conversion that is already defigned in $sql - I tried inserting it in this line: $minPrice = ($_GET["minPrice"]?sprintf("%.2f",$_GET["minPrice"]):"");
With no luck..
My search.php contains this line:
$sql = "SELECT * , IF(currency='GBP',(MIN(price) * 1.9684),MIN(price)) AS minPrice, MAX( price ) AS maxPrice, IF(currency='GBP',(saleprice * 1.9684),saleprice) AS display_saleprice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE ".$where." GROUP BY name";Any help or guidance much appreciated!
Thanks
Hi,
Do you mean that if minPrice and maxPrice are present in the URL; then you need that to apply to the currency converted price?
I think this would need to be done in the WHERE clause in a similar way to the main SELECT part of the query; converting price if currency='GBP' before testing it against the price range (bear in mind that minPrice in the SQL is not related to $minPrice in the script)...
Is it going to be the case that all products will be in the same currency for a particular query?
Cheers,
David.
Oh, yes, I did think it would pick up minPrice from the SQL...didn't realize they weren't the same.
Yes, I need to apply to currency converted price if minPrice & maxPrice are in the URL. Since I use 2 currencies, they would both need to be in the base, which is USD.
I tried adding it to the $_GET string, but didn't work (or I did it wrong, more like!).
Thanks
T.
Hi, any suggestions where I would add the extra currency conversion?
Theoretically, drawing from the SQL minPrice variable, I would need something like this:
$minPrice = ($_GET["IF(currency='GBP',(MIN(price) * 1.9684),MIN(price)) AS minPrice"]?sprintf("%.2f",$_GET["IF(currency='GBP',(MIN(price) * 1.9684),MIN(price)) AS minPrice"]):"");(which doesn't work)
Any help much appreciated!
Thanks
T.
Hi Teresa,
I'm a little confused by this....
If the products in the database are in a different currency; would the user not expect the price range to apply to the currency that is being displayed; rather than a converted value...
Apologies for not quite understanding this properly...
Cheers,
David.
Hi David, no prob, I may not be explaining this correctly.
I receive feeds in 2 currencies, GBP & USD. Because my main visitors are in the US, my PT site converts everything to USD using Price * currency rate (currency is a field I added to my db). All of the of the prices displayed on the site are in USD, and visitors don't see the original GBP price at all.
So when someone will do a price search for items under 100, it will be for 0-100 USD.
However, my current search.php returns everything under 100 USD, as well as everything under 100 GBP (which is actually 151 USD). So it is including items above the maxPrice. In reality, based on the exchange rate, 0-100 should list GBP items that are no greater than 66 GBP (which equals 100 USD).
I'm just not sure where to place this currency conversion for minPrice\maxPrice - this is the only place I convert prices:
$sql = "SELECT * , IF(currency='GBP',(MIN(price) * 1.9684),MIN(price)) AS minPrice, MAX( price ) AS maxPrice, IF(currency='GBP',(saleprice * 1.9684),saleprice) AS display_saleprice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE search_name LIKE '".database_safe($parts[1])."%' GROUP BY name";
Hope this helps! This is where I'm stuck :)
Thanks
T.
Hi Teresa,
At some point, the min/max price must come into the WHERE clause; so it may just be a case of changing "price" to "minPrice" or "maxPrice" as required. Could you perhaps send me your latest search.php (zipped if possible) so that I know what you're currently working with - this will help me work out how best to handle this...
Cheers,
David.
Hi,
Ooops - sorry, I deleted your original post as I thought it was a duplicate! Could you post the original question again - sorry for the inconvenience;
Cheers,
David.