Jump to content

Allow category names to show on search results


Dr. Gonzo

Recommended Posts

Hello all,

I think I found a temporary workaround to an original issue where I have inventory items tied to non-inventory items for reference purpose. The temporary workaround for my predicament is to create all the inventory items (for now creating inventory items as sets in some cases), then add them under their respective categories (categories are non-inventory items names) so that shoppers can know that item is for their model of non inventory item. Unfortunately my temporary workaround cannot use the search function since the category name does not show up under the search results. Any way for me to include the category name (or have the search feature query category meta data) so that those shows up under my search results? I have the box unchecked in the back end to hide empty categories and this still does nothing.

 

I'm still hoping the next revision of the cart can allow us to configure non-inventory items under our actual inventory products with a quantity field for that inventory item used in said non-inventory device, and perhaps a way for the cart to also reference that non inventory item throughout the checkout and order process.

Link to comment
Share on other sites

Preferrably, I would like the basic search to hit on it since some novice web users will not fiddle with advance searching: they simply want to type in their model that needs a part and if it comes up empty handed they move on. If the search would query the meta data under the category field by default then I could have more freedom to specify variants of a product name based on user logs to ensure our customers can find what they are looking for.

Link to comment
Share on other sites

Would this approach work? --

Forego the "search" altogether. Lead the customer to drill down through the categories to find the (non-inventory product that is represented by a) correct sub-category, then list all the inventory that is assigned to that sub-category.

For example:

Parts Search for...
===================
Bicycles:
 Schwinn:
  Electric:
   S600

Inventory Listing (for S600)
=================
Electric Battery -- ZPM100
Worm Gear Driveshaft -- CQ-800
etc.

Use the category navigation (some skins have a side box that says, "Shop by Category") to start the "search".

Again, the customer is not really "searching", but rather drilling down through a tree of categories to get a list of the parts you sell for a particular (non-inventory) item.

Link to comment
Share on other sites

I had set that up already with the categories. Was hoping to have a tool to capture the speed surfers who, if cannot locate what they are looking for within the first few seconds of being on your site, simply move on to the next. When I had logs to view the browsing traffic on another site I noticed some shoppers that would type in a product name, search result not found, and they left. And we had the product they wanted had they used the categories to drill down it just did not show up on a search since the syntax of their query did not match the product name exactly. I was debating removing the search but this would be such a helpful tool for our customers.

I would even settle for having the search return values from the meta keywords under a product so I can specify all variants of a product name but man that would be tedious work.

Link to comment
Share on other sites

Quote

I would even settle for having the search return values from the meta keywords under a product so I can specify all variants of a product name but man that would be tedious work.

This is what we do. If you can use this general query form to modify your existing meta data, it's not really so time consuming:

UPDATE tablename SET tablefield = replace(tablefield,"findstring","replacestring");

For instance, we found customers search for such and such dolls, rather than doll, So we now have doll,dolls, in the metadata. The same thing with carter,carter's,carters.

 

Link to comment
Share on other sites

  • 3 weeks later...

Ok finally got around to messing with search again. I notice that if I add something into the meta keywords under the search engines tab, it does not generate that product as a hit on the search results. It DOES however return search if a hit is found for the name of the product. Am I doing something wrong?

Link to comment
Share on other sites

By default, CC6 creates a key on the Inventory table called a FULLTEXT key. This key is what is used to quickly and efficiently search through an aggregation of the 'product_code', 'description', and 'name' of that record in the table.

By default, CC6 uses the FULLTEXT key to make is search. Under certain circumstances, CC6 will switch the search mode to that of looking for whole words in the description or in the name.

To include a separate table (Category) in the search will require abandoning the current search approaches -- not that keeping them is in your interest.

Link to comment
Share on other sites

Ok I at least got the code to search the seo_meta_keywords field in the dbase for the product using the thread Dirty Butter provided (thanks again BTW). Now how I might be able to mod the sql query to poll the same field but under the category table if easily?

 

I see this code in the catalogue.class.php file:

$q2 = "SELECT I.* FROM ".$GLOBALS['config']->get('config', 'dbprefix')."CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM ".$GLOBALS['config']->get('config', 'dbprefix')."CubeCart_pricing_group $group_id GROUP BY product_id) as G ON G.product_id = I.product_id $joinString WHERE I.product_id IN (SELECT product_id FROM `".$GLOBALS['config']->get('config', 'dbprefix')."CubeCart_category_index` as CI INNER JOIN ".$GLOBALS['config']->get('config', 'dbprefix')."CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 ".$whereString.$rlike;

 

And I can see how it is configured to look in the inventory table for everything however if somehow I can add an additional query $q2. =  and something. Maybe that will be my fix?

 

Link to comment
Share on other sites

  • 2 weeks later...

The data type for this info as stored in the database is "text". According to the MySQL docs, TEXT can hold 2^16 bytes. Using UTF-8, each character may be one, two, or three bytes long.

Now, there may be other limits. There is a connector between PHP and MySQL that may have a limit on the actual amount of data that may pass through in one query packet. The size of that packet is reasonably large, but may not be THAT large. It is adjustable, I would think.

 

Link to comment
Share on other sites

I checked the last few of my listings and found one with 287 characters in the seo_meta_keywords. I was successful in finding that item just with the last term in the list of keywords. I may well have others with more characters, but I don't know an easy way to find my longest list.

I played with a long keyword list and was still able to search for the last term at a character count of 1,751. Is that long enough for your needs?

Link to comment
Share on other sites

In order for my workaround of not being able to search and display category name (or seo_meta_keywords from categories) I would need that field to hold a substantial amount of data. Due to the universal nature of quite a few of our products, each product may very well have thousands of keywords populated in the seo_meta_keywords field: think 5,000-10,000 keywords with an average character count of 10 each . I just wanted to ensure that I did not do all this data entry only to find out that there is an issue. If this proves to be an issue, I am right back at my original dilemma with the search and trying to get it to read category table fields to return as search results.

Link to comment
Share on other sites

OK, I'll play some more and get the count up to 100,000 characters.

It's not looking good. I tried creating a Word document so I could copy the same keywords over and over and get a character count. I used 117,000+ characters, I copied the list to Notepad and then copied that into the keywords for one item. It did NOT find the last term. And I'm wondering about the possibility of timing out if it were trying to search through a huge number of records, all with that size, anyway.

Link to comment
Share on other sites

I would make my comment that should 10K words (that CubeCart could, if the admin settings were set that way) be included in the document sent to my mobile, it having a miserly data plan, that gives no benefit to me (as these words are not shown on the page), I would question your upbringing.

Link to comment
Share on other sites

As bsmither brought up a good point using a vastly large meta list is just not practical, It would appear that I need to go back to my original plan using dbase query to have the search look into the category table for data to return :(

Ah unless... Anyone use semperfi's configurable search plugin? That one has a custom dbase field to query for products but that begs the question once again: wonder what the maximum char count that field will have.

 

https://www.cubecart.com/extensions/plugins/ajax-configurable-search-box

Link to comment
Share on other sites

In reviewing this conversation, I understand you wish for the Search to include the following database columns:

Product: Name, Code, Description (which is currently the 'fulltext' table index)
Product: maybe seo_meta_title, seo_meta_description, seo_meta_keywords
Category: Name, Description, (maybe seo_meta_title, seo_meta_description, seo_meta_keywords)

 

Link to comment
Share on other sites

For you, and you alone, this possible solution. Please try this:

In admin, Maintenance, Query Database tab, take note if there is any mention of a Prefix just above the Query box.

In the query box, enter the following, prepending the name of the table with the prefix if necessary:
ALTER TABLE `CubeCart_category` ADD FULLTEXT INDEX `fulltext` (`cat_name`, `cat_desc`);

Then edit the following file, in /classes/catalogue.class.php, near line 1553, find:
$joinString = (isset($joins) && is_array($joins)) ? implode(' JOIN ', $joins) : '';
if (!empty($joinString)) $joinString = ' JOIN '.$joinString;

$indexes = $GLOBALS['db']->getFulltextIndex('CubeCart_inventory', 'I');

On the blank after, add:
$indexes = array_merge($indexes,$GLOBALS['db']->getFulltextIndex('CubeCart_category', 'C'));

Next, near line 1590, find the line THAT STARTS WITH:
$query = sprintf("SELECT I.*, %2\$s AS Relevance FROM %1\$sCubeCart_inventory AS I LEFT JOIN (SELECT

Change that line by adding the new part while keeping all the rest as is:
$query = sprintf("SELECT I.*, %2\$s AS Relevance FROM %1\$sCubeCart_category AS C, %1\$sCubeCart_inventory AS I LEFT JOIN (SELECT

I SAY AGAIN, do not lose the rest of the line! You are simply adding some new content to the existing line, NOT REPLACING anything!

Next, near line 1593, find the line THAT STARTS WITH:
$q2 = sprintf("SELECT COUNT(I.product_id) as count, %2\$s AS Relevance FROM %1\$sCubeCart_inventory AS I LEFT JOIN (SELECT

Change that line by adding the new part while keeping all the rest as is:
$q2 = sprintf("SELECT COUNT(I.product_id) as count, %2\$s AS Relevance FROM %1\$sCubeCart_category AS C, %1\$sCubeCart_inventory AS I LEFT JOIN (SELECT

I SAY AGAIN, do not lose the rest of the line! You are simply adding some new content to the existing line, NOT REPLACING anything!

This may return more than what actually counts, as I developed this on an installation that has, as follows, this single product:

ZPM100 Battery :
Primary Category: Batteries
Secondary Category: Bicycles/Schwinn/Electric/S600

I have not tested this to see if any other product is returned when the search is on "S600", for example.

This also does not (yet) solve the condition where CubeCart attempts a different search algorithm if the first (using FULLTEXT indexes) fails to produce.

Link to comment
Share on other sites

I'm assuming that with this code :

ALTER TABLE `CubeCart_category` ADD FULLTEXT INDEX `fulltext` (`cat_name`, `cat_desc`);

I'm assuming that I can alter 'cat_desc' to have 'seo_meta_keywords' and have the index search for that field under the Cubecart_category table? If I add meta keywords to this field I can add 1 or 2 variants of the category name for search purposes and not inundate the end user with a massive file. If I add the variants to the 'cat_desc' field, the end user will see all the variants of the category name and look kinda tacky when the end user sees the page.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...