Hello
My site has prices for items in packs. The quantity per pack can vary and so a more expensive pack (but with a larger qty) could represent best value.
The standard prices screen currently displays pack prices in ascending price order. I've added a calculated field to the prices screen showing the unit price i.e. price / qty per pack. It would be nice for my users to be able to sort on this field in the same way that they can sort on different fields after the product search. Would this be hard to incorporate?
Thank you
Alastair
Hi David
Yes, I added a field called "quantity" to the products table and I get data in to it from the feeds. On the "prices" screen I calculate the quantity per pack and display it opposite each product, and this is the field that it would be nice to offer a search on.
Thank you.
PS This level of support is excellent. Keep up the good work
Alastair
Hi Alastair,
Is "quantity" every likely to be unset (i.e. 0); in which case should be assumed to be 1?
Cheers,
David.
Hello David
Yes, it is possible that some merchants do not provide a quantity field. I could add something to includes/admin to set it to "1" if it is ""?
Alastair
Hi,
I've just been thinking about this, and i'm not sure about the performance implications of doing the maths within the actual search.
Instead, I think it would be much better to add a new field to your "products" table, called "value"; and then calculate this as "price / quantity" (where quantity == 1 if 0) during import.
It sounds like you're familiar with modification of the import record handler within includes/admin.php, but let me know if you need help with this part.
With that in place, look for the following code near the top of search.php:
$orderByDefault = array();
$orderByDefault["rating"] = "rating DESC";
$orderByDefault["priceAsc"] = "minPrice ASC";
$orderByDefault["priceDesc"] = "minPrice DESC";
$orderByFullText = array();
$orderByFullText["relevance"] = "relevance DESC";
$orderByFullText["rating"] = "rating DESC";
$orderByFullText["priceAsc"] = "minPrice ASC";
$orderByFullText["priceDesc"] = "minPrice DESC";...and change this as follows (adding a new element to each of the arrays)
$orderByDefault = array();
$orderByDefault["rating"] = "rating DESC";
$orderByDefault["priceAsc"] = "minPrice ASC";
$orderByDefault["priceDesc"] = "minPrice DESC";
$orderByDefault["value"] = "value ASC";
$orderByFullText = array();
$orderByFullText["relevance"] = "relevance DESC";
$orderByFullText["rating"] = "rating DESC";
$orderByFullText["priceAsc"] = "minPrice ASC";
$orderByFullText["priceDesc"] = "minPrice DESC";
$orderByFullText["value"] = "value ASC";At this point, it's worth testing before continuing; so go to any search result page, and modify the URL to add ?sort=value on the end and check the new sort method.
Finally, you want to add this option to the list of sort orders that the user can select. To do this, look for the following code, again in search.php at around line 122:
$sortPriceDesc = ($sort=="priceDesc"?"<strong>".translate("High to Low")."</strong>":"<a href='".$sortHREF."priceDesc'>".translate("High to Low")."</a>");
$banner["h3"] = translate("Order by").": ".$sortRelevance.$sortRating." | ".translate("Price").": ".$sortPriceAsc.", ".$sortPriceDesc;...and change this as follows:
$sortPriceDesc = ($sort=="priceDesc"?"<strong>".translate("High to Low")."</strong>":"<a href='".$sortHREF."priceDesc'>".translate("High to Low")."</a>");
$sortValue = ($sort=="value"?"<strong>".translate("Value")."</strong>":"<a href='".$sortHREF."value'>".translate("Value")."</a>");
$banner["h3"] = translate("Order by").": ".$sortRelevance.$sortRating.$sortValue" | ".translate("Price").": ".$sortPriceAsc.", ".$sortPriceDesc;That should get you close...
Cheers,
David.
David
Looking good so far.. now I just need to get the list of sort orders that a user can select onto the product/prices screen (since it has more relevance here than on the product search screen itself). The prices screen is in ascending price order by default (with no options at present)
Thank you
Hi Alastair,
Do you mean that you want to enable sorting of the prices table as shown on the product page, not the normal search results? It should be reasonably straight forward (subject to gotchas!) but can you confirm what sort orders you want.... I presume you're looking for Price (Ascending / Descending) and "Value" - ascending only or both....?
Cheers,
David.
You have got it exactly! Sorting the prices table on the product page with
Price (Ascending / Descending) and "Value" (price per) ascending only
Thank you
Hi Alastair,
Where the sort order of the prices on the product page has been changed, do you still want the main product to be the cheapest, or do you want it to match the one at the top of the prices list?
At the moment they all come out of the same query, so if you want the main product to remain as the cheapest it's a bit more work. Having said that, thinking out loud, if someone clicks to sort by "Value"; then perhaps you DO want the featured product to be the best value one...
Does that makes sense?
Cheers,
David.
Hello David
You're right again, I hadn't thought of it but it would be really useful to change the featured product to the best value one. If that is too difficult (for me to do!) I'd be happy with just sorting the displayed list
Thank you
Alastair
Hi Alastair,
That's good - it's much easier for it to affect the featured product as well because it all comes out of the same query. I'll work through this in 2 steps; firstly to add the code to perform the sort based on the sort parameter in the URL, then once this is working we can add the links to the page....
In products.php, look for this line, near the top of the script:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($q)."' ORDER BY price LIMIT ".$config_resultsPerPage;Simply replace this with the following code:
$orderBy["priceAsc"] = "price ASC";
$orderBy["priceDesc"] = "price DESC";
$orderBy["value"] = "price_per ASC";
$sort = ($orderBy[$_GET["sort"]]?$_GET["sort"]:"priceAsc");
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($q)."' ORDER BY ".$orderBy[$sort]." LIMIT ".$config_resultsPerPage;Now, to test this, first go to any product URL. The default sort order should be price ascending. To test the value sort, add the following to the end of the URL:
?sort=valueThe page should reload to show the products sorted by the price_per field...
Cheers,
David.
Hello David
Thank you. This works, the page reloads to show all products sorted by the price_per field and the featured product is now the one that represents base value i.e. the lowest price per unit
Alastair
Excellent! Without your DB i'm not able to test it, so glad that worked!
Now, to add the sort links to the products page; copying exactly the same format as the sort links on the search results (so that the user know where to expect to find them), look for the following code in products.php
$banner["h2"] = translate("Price search results for")." <strong>".htmlentities($q,ENT_QUOTES,$config_charset)."</strong> ";...and add this code immediately AFTER the above line:
if ($config_useRewrite)
{
$sortHREF = $config_baseHREF."product/".tapestry_hyphenate($q).".html?sort=";
}
else
{
$sortHREF = $config_baseHREF."products.php?q=".urlencode($q)."&sort=";
}
$sortValue = ($sort=="value"?"<strong>Value</strong>":"<a href='".$sortHREF."value'>Value</a>");
$sortPriceAsc = ($sort=="priceAsc"?"<strong>".translate("Low to High")."</strong>":"<a href='".$sortHREF."priceAsc'>".translate("Low to High")."</a>");
$sortPriceDesc = ($sort=="priceDesc"?"<strong>".translate("High to Low")."</strong>":"<a href='".$sortHREF."priceDesc'>".translate("High to Low")."</a>");
$banner["h3"] = "Order by: ".$sortRelevance." | Price: ".$sortPriceAsc.", ".$sortPriceDesc;Fingers crossed..!
Cheers,
David.
David
This works a treat thank you. I'm sure other people will find it useful as well. Many thanks
Alastair
Hello Alastair,
Is the quantity per pack a new field that you have added to your database? If so, it should be reasonably straight forward to bring in a new field from the search query (calculated just like you have done on your product page) that can be sorted against. If so, can you let me know what it is called; and if not - is it a separate field in your feeds that you could register and import by following the instructions in the following thread:
http://www.pricetapestry.com/node/313
Cheers,
David.