Hello David,
Hope you are well. Apologies for bombarding you with questions at the moment!
I'm trying to display the merchant name in my search results using %DB_merchant% in the search results html.
Superficially this works fine - perfectly for those products where there is only one merchant or when the cheapest merchant for a given product is first alphabetically... but when there is more than one merchant and the cheapest is not the first alphabetically, the first merchant's name is shown.
Is there some way to get round this so that the name of the cheapest merchant is shown?
Thanks as always for your brilliant support.
Lis
Thanks David.
I've just copied the code in - at the moment I'm not getting anything displaying for the merchant name when the number of merchants is >1. Any ideas what could be happening?
Cheers,
Lisette
Sorry Lis, there was a global declaration missing - have a go the following as the replacement:
$each = $pto_html_search_each;
global $pto_config_databaseTablePrefix;
if ($row->numMerchants > 1)
{
$sql = "SELECT merchant FROM `".$pto_config_databaseTablePrefix."products` WHERE name='".$wpdb->escape($row->name)."' AND price='".$row->minPrice."'";
$wpdb->query($sql);
$merchants = array();
foreach($wpdb->last_result as $v)
{
$merchants[] = $v->merchant;
}
$row->merchant = implode(",",$merchants);
}
Cheers,
David.
--
PriceTapestry.com
Hello David,
Sorry to bump this post but is it possible for the merchant logo to come up instead of the text when %DB_merchant% is added to the search result html? I have the merchant logo coming up on the product page.
Thanks, Jason.
Hi Jason,
Sure - in pto_search.php look for the following code around line 586:
$each = str_replace("%DB_".$field."%",$row->$field,$each);
...and REPLACE with:
global $pto_config_externalPath;
global $pto_config_externalBaseHREF;
if (
($field=="merchant")
&&
(file_exists($pto_config_externalPath."logos/".$row->merchant))
)
{
$replace = "<img border='0' src='".$pto_config_externalBaseHREF."logos/".str_replace(" ","%20",$row->merchant)."' />";
$each = str_replace("%DB_".$field."%",$replace,$each);
}
else
{
$each = str_replace("%DB_".$field."%",$row->$field,$each);
}
Cheers,
David.
--
PriceTapestry.com
Hello David
one last question i have
i try to display at search results for each product merchant with %DB_merchant%, but doesn't work
because i have done many changes to WordPress plugin, i also tried and replaced all files with the originals but still doesn't show merchant
Hello George,
`merchant` isn't included in the search results re-query since the main search query uses summary fields (e.g. MIN(price) as minPrice). It can be added simply as follows; by looking for the following code at line 357 of pto_search.php:
$sql2 = "SELECT id,name,normalised_name,image_url,description,price,rating FROM `".$pto_config_databaseTablePrefix."products` WHERE id IN (".$in.")";
...and REPLACE with:
$sql2 = "SELECT id,name,normalised_name,image_url,description,price,rating,merchant FROM `".$pto_config_databaseTablePrefix."products` WHERE id IN (".$in.")";
Then look for the following code at line 382:
$pto_searchResults[$k]->rating = $rows3[$product->id]->rating;
...and REPLACE with:
$pto_searchResults[$k]->rating = $rows3[$product->id]->rating;
$pto_searchResults[$k]->merchant = $rows3[$product->id]->merchant;
That would be fine if you're working with single merchants, however if you are displaying compared prices and you need the merchant field to be that associated with the lowest price then it has to be re-queried for every result. To do this, as an alternative to the second modification above, REPLACE the original code with:
$pto_searchResults[$k]->rating = $rows3[$product->id]->rating;
$sql3 = "SELECT merchant FROM `".$pto_config_databaseTablePrefix."products` WHERE name = '".$wpdb->escape($pto_searchResults[$k]->name)."' ORDER BY price LIMIT 1";
$wpdb->query($sql3);
$pto_searchResults[$k]->merchant = $wpdb->last_result[0]->merchant;
Cheers,
David.
--
PriceTapestry.com
thanks works fine now!
also to make it work with related products and short codes i edited pto_product.php
line 248 changed from
$sql2 = "SELECT id,name,normalised_name,image_url,description,price,rating,MIN(price) AS minPrice, COUNT(id) AS numMerchants FROM `".$pto_config_databaseTablePrefix."products` WHERE name IN (".$in.") GROUP BY name";
to
$sql2 = "SELECT id,name,normalised_name,image_url,description,price,rating,merchant,MIN(price) AS minPrice, COUNT(id) AS numMerchants FROM `".$pto_config_databaseTablePrefix."products` WHERE name IN (".$in.") GROUP BY name";
and
after $pto_searchResults[$k]->rating = $rows3[$product->name]->rating;
added $pto_searchResults[$k]->merchant = $rows3[$product->name]->merchant;
thanks again
Hello Lis,
You can do this - but as you have found since the search results are generated using what's called a "summary" SQL query the non-summary fields are selected from an undefined record; so it is necessary to re-query for the cheapest merchant where numMerchants > 1.
In pto_search.php, look for the following code at line 445:
$each = $pto_html_search_each;
...and REPLACE with:
$each = $pto_html_search_each;
if ($row->numMerchants > 1)
{
$sql = "SELECT merchant FROM `".$pto_config_databaseTablePrefix."products` WHERE name='".$wpdb->escape($row->name)."' AND price='".$row->minPrice."'";
$wpdb->query($sql);
$merchants = array();
foreach($wpdb->last_result as $v)
{
$merchants[] = $v->merchant;
}
$row->merchant = implode(",",$merchants);
}
I've coded the above so that if you have equal cheapest merchants then they all get a mention as "Merchant1,Merchant2,etc" but if that is not desirable, replace the SQL line with:
$sql = "SELECT merchant FROM `".$pto_config_databaseTablePrefix."products` WHERE name='".$wpdb->escape($row->name)."' ORDER BY price LIMIT 1";
...and that will ensure that the output of %DB_merchant% is the name of the merchant that appears first in the price comparison table...
Hope this helps!
Cheers,
David.
--
PriceTapestry.com