You are here:  » Search tricks

Active Forum Topics


Search tricks

Submitted by kenan on Tue, 2018-05-08 18:22 in

Hi David,
When a user searches 'iPhone X', the search results also show the 'iPhone X cases'
Is it possible to use the minus sign to eliminate results containing certain words like 'iPhone X -case'.
Ok, users can use the filtering section, but I think this feature is more useful.

It's just like google search tricks.
a plus symbol (+) in front of words to force Pricetapestry to include,
maybe using quotes to search for an exact phrase,
...

Thanks.

Submitted by support on Wed, 2018-05-09 10:47

Hello Kenan,

Most of the tricks you describe are supported by MySQL's FULLTEXT index in BOOLEAN MODE:

https://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html

To support this, edit search.php and look for the following code at line 257:

  $words = explode(" ",$parts[0]);

...and REPLACE with:

  $parts[0] = $_GET["q"];
  $words = explode(" ",$parts[0]);

And then the following code beginning at (now) line 283:

  $where = "MATCH ".$matchFields." AGAINST ('".database_safe($parts[0])."')";
  $sql = "SELECT SQL_CALC_FOUND_ROWS id,COUNT(id) AS numMerchants,MIN(price) as minPrice, MATCH ".$matchFields." AGAINST ('".database_safe($parts[0])."') AS relevance FROM `".$config_databaseTablePrefix."products` WHERE ".$where.$priceWhere." GROUP BY search_name";

...and REPLACE with:

  $where = "MATCH ".$matchFields." AGAINST ('".database_safe($parts[0])."' IN BOOLEAN MODE)";
  $sql = "SELECT SQL_CALC_FOUND_ROWS id,COUNT(id) AS numMerchants,MIN(price) as minPrice, MATCH ".$matchFields." AGAINST ('".database_safe($parts[0])."' IN BOOLEAN MODE) AS relevance FROM `".$config_databaseTablePrefix."products` WHERE ".$where.$priceWhere." GROUP BY search_name";

MySQL's FULLTEXT index has a minimum word length setting which is 4 by default and this is considered by the script making a basic LIKE query if any keyword is less than 4 characters (as would be the case in your "iPhone X Cases" example).

If you have root access to your server (or would be able to ask your host to make the changes for you) this can be changed to 1 in the MySQL configuration file which is normally /etc/my.cnf or /etc/mysql/my.cnf.

The setting is ft_min_word_len which needs to be in the [mysqld] section of the file. First check if is already there and change to 1 or add the setting e.g.

[mysqld]
...other stuff...
ft_min_word_len = 1

After making the changes don't forget to restart MySQL / Apache etc. and then run a full import to have the new FULLTEXT index built with the new minimum word length setting. Finally, edit search.php and look for the following code at line 246:

  if (strlen($word) <= 3 || in_array(strtolower($word),$stopwords))

...and REPLACE with:

  if (FALSE)

...to use FULLTEXT in BOOLEAN MODE for all normal queries.

Cheers,
David.
--
PriceTapestry.com

Submitted by kenan on Sat, 2018-05-12 21:56

Hello David,
Thanks for your quick reply.
I checked everything twice maybe more, but it did not work.

PHP Version 5.6.30
MySQL Version 5.5.5-10.1.32-MariaDB

and /etc/my.cnf

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[mysqld]
ft_min_word_len = 1

Submitted by support on Mon, 2018-05-14 09:02

Hi,

My apologies, the final replacement following reduction of ft_min_word_len to 1 at line 246 should be FALSE rather than TRUE:

  if (FALSE)

(corrected above)

However there is a final change you might want to make as the search form is pre-populated with the normalised version of $_GET["q"] so your trick characters would be stripped - to pre-populate with the exact value being using in the FULLTEXT query, edit html/searchform.php and look for the following code at line 21:

  <input required='required' type='text' name='q' value='<?php print ((isset($q) && !isset($parts[1]) && !isset($product["products"]))?$q:""); ?>'>

...and REPLACE with:

  <input required='required' type='text' name='q' value='<?php print ((isset($q) && !isset($parts[1]) && !isset($product["products"]))?$_GET["q"]:""); ?>'>

Cheers,
David.
--
PriceTapestry.com

Submitted by kenan on Tue, 2018-05-15 18:41

Thank you very much David, it's working now.
You are awesome!