You are here:  » Merchant name in search results


Merchant name in search results

Submitted by lisbali12 on Sat, 2012-10-13 14:50 in

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

Submitted by support on Sun, 2012-10-14 12:03

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

Submitted by lisbali12 on Sun, 2012-10-14 13:59

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

Submitted by support on Mon, 2012-10-15 08:09

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

Submitted by lisbali12 on Mon, 2012-10-15 10:07

Thanks David, that's perfect.

Submitted by wiseonline on Sat, 2013-05-25 13:54

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.

Submitted by support on Sat, 2013-05-25 17:46

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

Submitted by wiseonline on Sun, 2013-05-26 00:56

Perfect, Thanks!

Submitted by george-p on Tue, 2014-10-21 19:18

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

Submitted by support on Wed, 2014-10-22 08:19

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

Submitted by george-p on Wed, 2014-10-22 14:53

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