You are here:  » Searching category names as well as products


Searching category names as well as products

Submitted by mally on Thu, 2008-10-30 08:51 in

Hello David

I've noticed some of my website vistors are seaching for category names rather than product names in the normal search box. This is bringing back no results.

Is there a way I can get the categories searched at the same time?

Thanks

Mally

Submitted by support on Thu, 2008-10-30 10:02

Hi Mally,

The category field can easily be added to the search query. If you email me your latest search.php (I think I have it but just to make sure) i'll add it in for you...

Cheers,
David.

Submitted by paul30 on Sat, 2008-11-01 19:32

Thats a great mod! - Can we have it posted assuming one uses a clean (stock) search.php file?

Thanks a lot!
Pasha

Submitted by support on Mon, 2008-11-03 10:31

Hi Pasha,

From the distribution version of search.php, there WHERE clause needs to be changed first for the full text search mode, on lines 79 and 81 replace:

WHERE MATCH name AGAINST ('".database_safe($parts[0])."')

with:

WHERE MATCH name AGAINST ('".database_safe($parts[0])."') OR category='".database_safe($parts[0])."'

...and for the basic search mode, change line 94 from:

$where = implode(" AND ",$wheres);

to:

$where = implode(" AND ",$wheres)." OR category='".database_safe($parts[0])."'";

Do bear in mind that this will have performance implications as the database can no longer perform the query using only indexed fields - so only recommended for niche sites!
Cheers,
David.

Submitted by transparencia on Tue, 2010-11-02 14:29

Hi David!

I think that this MOD is actually the anwser to multi-level categories.

But regarding the category field, isn't it an indexed field?

Taking in mind that most merchants send the categories field like this PC -> Hardware -> RAM memory and Price Tapestry transforms it to PC Hardware RAM Memory and that there are almost no merchants with the same categories (this being the problem), my idea is the following:

The search query becomes searched in both fields, the product name and the category (like Pasha suggested). The category search returns any categories that have all the keywords that are in the search query. So for example, if the user searches External Hard-drive, the categories Electronics PC Hard-Drive External or Hardware External Hard Drive are a match. Then after the search, the products are returned normally (if found) but the results from the category query (if any found) are returned in top of the product search in ONE link only, like this:

Show all products from the "External Hard-Drive" category.

So if we click the link to "External Hard-Drive" it will list all the products from the categories Electronics PC Hard-Drive External and Hardware External Hard Drive and others that have the keywords "External Hard-drive".

If we wanted we could create a multi-level category map for all the relevant categories by simply searching and copying the search URL and manually link it in a page with the proper title. And if the queries are very expensive, a cache could be created specially for this!

Concluding, this would abolish the need for a multi-level category database and other complex modifications.

Tell me what do you think, David and how hard is this MOD?

Regards,
Pedro

Submitted by transparencia on Wed, 2010-11-03 14:56

David, can you help me with this code?

Search categories MOD:

<?php
//Line 143 of search.php 11/09A - User has inserted the text in the search box and pressed enter.
//A search is made for that string on all the categories (before of the actual product name search).
$sql2 "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE category = ('".database_safe($parts[1])."') GROUP by name";
//If the result of the search in the categories is not null then the search results will automatically be all the products that belong to those categories. Otherwise, return a normal product search.
if (database_querySelect($sql2,$rows))
$where "category = ('".database_safe($parts[1])."')";
?>

Search categories through URL MOD. Example search.php?catq=External+Hard+Drive:

<?php
//Line 6 of search.php 11/09A
//get the category query
$catq = (isset($_GET["catq"])?tapestry_normalise($_GET["catq"],":\."):"");
//Pass it to $q
if ($catq)
  
$q $catq;
?>

For some reason, the products continue to display normally even if there is a category with that name and for the second mod it just doesn't show anthing.

Submitted by support on Wed, 2010-11-03 15:04

Hi,

Note that there are 2 different search cases for a normal query, if all words
are greater than 3 characters the full text search is used (MATCH, AGAINST..
etc.) otherwise the basic search is used, and it looks like your first mod
is only applied against the latter case where the full text index isn't
used. If you're not sure, email me your
modified search.php and i'll check it out for
you.

Regarding the second mod, in place of:

  $q = $catq;

...you can re-write it to the normal built in category search as follows:

  $q = "category:".$catq;

Hope this helps!

Cheers,
David.
--
PriceTapestry.com

Submitted by transparencia on Wed, 2010-11-03 15:52

Wow, I've never knew about the category: search! That's exactly what I want!

It looks great except the fact that it only searches for the exact category name. For example if I have a "Game Merchandise" category and write category:merchandise, it returns nothing. But if I write category:game merchandise it returns all the products in that category.

I want it to return any products from any categories containing any combination of the all the words (word AND word). For example, category:merchandise returns products from the categories Game Merchandise, Band Merchandise, Apple Merchandise, etc..., other example category:external hard drive returns all products from categories PC Hard Drives External, Storage External Hard drive.

Could it be changed to do that?

Submitted by support on Wed, 2010-11-03 16:01

Hi,

Sure - in search.php you'll find this code at line 88:

$where .= " ".$field." = '".database_safe($parts[$i])."' ";

...REPLACE that with:

if ($field=="category")
{
  $words = explode(" ",$parts[1]);
  $ands = array();
  foreach($words as $word)
  {
    $ands[] = "category LIKE '%".database_safe($word)."%'";
  }
  $where .= " (".implode(" AND ",$ands).") ";
}
else
{
  $where .= " ".$field." = '".database_safe($parts[$i])."' ";
}

The original code is generic for category/merchant/brand so using
the IF condition will make sure that it doesn't mess those up...

Cheers,
David.
--
PriceTapestry.com

Submitted by transparencia on Sun, 2010-11-21 23:21

Hi and thanks, David!!

One more thing, is it possible that, in the case the category:search doesn't return any items, the search results could be a normal website search (without the category:)?

Regards,
Pedro

Submitted by transparencia on Mon, 2010-11-22 01:59

Better yet, is there a way to unite both search results. Meaning, category: Iphone and Iphone will return results with Iphone on the product title and/or at the same time belonging to a category that has the word Iphone.

If this is to be possible, could there be a way to first show the results that have Iphone in the title has these are more relevant.

Submitted by support on Mon, 2010-11-22 08:20

Hi,

If you'd like to email me your search.php I'll have
a look at the options for combining category / regular search for you...

Cheers,
David.
--
PriceTapestry.com

Submitted by mediadream on Mon, 2011-04-25 08:13

Any information about this last topic?
Any example of how to solve this?

Submitted by support on Mon, 2011-04-25 11:02

Hi Mediadream,

It is actually straight forward to include the category in the search. There are a couple of changes required and a new index needs to be created as follows. Firstly, edit search.php and look for the following section beginning at line 273:

          if ($config_searchDescription)
          {
            $matchFields = "name,description";
          }
          else
          {
            $matchFields = "name";
          }

...and REPLACE with:

          if ($config_searchDescription)
          {
            $matchFields = "name,description,category";
          }
          else
          {
            $matchFields = "name,category";
          }

Next, look for the following code beginning at line 296:

            $where .= "search_name LIKE '%".database_safe($word)."%'";

...and REPLACE with:

            $where .= "search_name LIKE '%".database_safe($word)."%'";
            $where .= " OR category LIKE '%".database_safe($word)."%'";

Finally, the new full text index must be created as follows. Use the following dbmod.php script, to be run from your main Price Tapestry installation folder:

<?php
  set_time_limit
(0);
  require(
"includes/common.php");
  if (
$config_searchDescription)
  {
  
$sql "ALTER TABLE `".$config_databaseTablePrefix."products`
            ADD FULLTEXT INDEX name_description_category (name,description,category)"
;
  }
  else
  {
  
$sql "ALTER TABLE `".$config_databaseTablePrefix."products`
            ADD FULLTEXT INDEX name_category (name,category)"
;
  }
  
database_queryModify($sql,$result);
  print 
"Done.";
?>

Hope this helps!

Cheers,
David.
--
PriceTapestry.com

Submitted by gregor on Fri, 2011-09-02 14:40

Hi David, with the latest Category search mod (with index) will there still be a performance impact? Would this be suitable for a large database?

Thanks,
Gregor

Submitted by support on Fri, 2011-09-02 15:20

Hi Gregor,

Full text indexes are created at import time so the number of fields included in the index shouldn't have any significant impact on performance regards of the database size.

Cheers,
David.
--
PriceTapestry.com

Submitted by quokka on Sat, 2011-10-01 20:35

Hi..
For info..
Query didn't work on my hosting account..
This one did:

CREATE FULLTEXT INDEX name_description_category ON pt_products (name,description,category)

Submitted by support on Sun, 2011-10-02 08:48

Hi bas,

The above was exemplified based on additional field names so if cat2, cat3 etc. haven't been created the SQL would have failed. Version you identified will of course work fine...

Cheers,
David.
--
PriceTapestry.com

Submitted by wesse249 on Sat, 2015-11-21 11:06

Hello David,

I also want the category added to the searchquery. I use your latest version.

I above code then working?

Thanks Jan Roel

Submitted by support on Mon, 2015-11-23 10:01

Hello Jan,

Yes, all still valid - I've updated the line numbers in this comment for 15/09A.

Bear in mind that this is for normal Category support only, I am currently looking at the best way to incorporate category names from the Category Hierarchy into search - i'll update this thread once I've worked out the best solution in that case as a couple of users have requested this...

Cheers,
David.
--
PriceTapestry.com

Submitted by wesse249 on Tue, 2015-12-01 21:06

Hello David,

When i make the changes and upload it. Nothing is found anymore.

Jan Roel

Submitted by support on Thu, 2015-12-03 10:56

Hello Jan,

I've updated the dbmod.php script from this comment as it sounds like the indexes were not created - re-run the dbmod.php script with the updated version and that should be all it is...

Cheers,
David.
--
PriceTapestry.com

Submitted by wesse249 on Tue, 2016-01-12 11:09

Hello David,

How can i search also search the category hierarchie categories?

With above changes the script is only searching the normal categories. Or is this wrong?

Thank You.

Jan Roel

Submitted by support on Tue, 2016-01-12 12:03

Hello Jan,

I just thought of a nice method of implementing this (search to include category hierarchy sub-category of the product) which is to set the `category` field at import time to that of the sub-category name from the category hierarchy, and with that in place, the above changes will then work without any further modification to search both product and sub-category name.

To implement this, edit includes/admin.php and look for the following code at line 122:

  global $admin_importCategoryHierarchyMappings;

...and REPLACE with:

  global $admin_importCategoryHierarchyMappings;
  global $admin_importCategoryHierarchyNames;

And then the following code at line 416:

  $importRecord["categoryid"] = $admin_importCategoryHierarchyMappings["=".$importRecord["category"]];

...and REPLACE with:

  $importRecord["categoryid"] = $admin_importCategoryHierarchyMappings["=".$importRecord["category"]];
  $importRecord["category"] = $admin_importCategoryHierarchyNames[$importRecord["categoryid"]];

And then the following code at line 440:

  $importRecord["categoryid"] = $v;

...and REPLACE with:

  $importRecord["categoryid"] = $v;
  $importRecord["category"] = $admin_importCategoryHierarchyNames[$importRecord["categoryid"]];

Then the following code at line 549:

  global $admin_importCategoryHierarchyMappings;

...and REPLACE with:

    global $admin_importCategoryHierarchyMappings;
    global $admin_importCategoryHierarchyNames;

And finally the following code at line 609:

  $alternates = explode("\n",$category["alternates"]);

...and REPLACE with:

  $alternates = explode("\n",$category["alternates"]);
  $admin_importCategoryHierarchyNames[$category["id"]] = $category["name"];

Don't forget to do a full re-import for the changes to take effect.

And note that with this in place, there is no longer any need for the mod to update category value to sub category name on the product page from this comment...

(edit: updated for 16/10A)

Cheers,
David.
--
PriceTapestry.com

Submitted by wesse249 on Tue, 2016-01-12 13:29

Hello David,

I changed the code. But de categories aren't found.

For example when you go to this page: {link saved} you see the subcategory test2.

When i search for this in the searchbar nothing is found?

Greetings Jan Roel

Submitted by support on Tue, 2016-01-12 14:14

Hello Jan,

I've double-checked the code through on my test server - did you run a full import before testing as the setting of pt_products.category from categories_hierarchy.name is only applied at import time...

If still no joy, if you could email me your modified includes/admin.php I'll check it out further for you...

Cheers,
David.
--
PriceTapestry.com

Submitted by wesse249 on Wed, 2017-11-01 10:40

Hello David,

Does this mod still works for the new version?

Greetings Jan Roel

Submitted by support on Wed, 2017-11-01 12:11

Hello Jan,

Sure - i've updated the line numbering in comment #23902 for 16/10A...

Cheers,
David.
--
PriceTapestry.com