Kryptronic Software Support Forum

You are viewing this forum as a guest. Login to an existing account, or create a new account, to reply to topics and to create new topics.

#1 11-06-2019 07:31:07

west4
Member
From: UK
Registered: 04-16-2008
Posts: 645
Website

How to add a new data column to the site search

Hi,

I have some products with selection options that my customers search for but the selection options do not show up in search results e.g. Titan Badge has selection options of 20 different titles like 'Buddy, Attendance. Librarian etc. these titles are not searchable.

Is there a way to add a new section in the back end so I can add keywords that will be picked up by the site search?
I was thinking a new section in 'Inventory Items-Options' just above 'Custom Data' to add search words (Site Specific Search Keywords) with two sections 'Product Code/ID and Keywords' that will be included in the search table but are NOT also in the Meta-Keywords section.
How hard would this be to implement?

Cheers,

Bruce.

Last edited by west4 (11-06-2019 07:32:53)


I'd rather have a full bottle in front of me, than a full frontal labotomy.

Offline

 

#2 11-06-2019 09:05:48

webmaster
Administrator
From: York, PA
Registered: 04-20-2001
Posts: 19798
Website

Re: How to add a new data column to the site search

Use Raw DB Admin to add a new column to the ecom_prod table.  I suggest a CLOB/TEXT column so it will take a bunch of input.  Populate with data as needed.  Then edit the top of {private}/apps/ecom/ECOM_Prod/ECOM_Prod.php.  There you will see:

Code:

var $prodsearchfields_adv  = array('id', 'name', 'prodnum', 'catfeatgridname', 'keywords');
var $prodsearchfields_ajax = array('id', 'name', 'prodnum', 'catfeatgridname');

Add your new column to both arrays.  FYI: In version 9.1.0 the 'keywords' field is in both arrays. (pending release)


Nick Hendler

Offline

 

#3 11-06-2019 09:57:12

west4
Member
From: UK
Registered: 04-16-2008
Posts: 645
Website

Re: How to add a new data column to the site search

Hi Nick,

Thanks that has nailed it.

https://westfield4schools.co.uk/images/ScreenShot2019-11-06at145357.png

Cheers,
Bruce.

Last edited by west4 (11-06-2019 10:00:53)


I'd rather have a full bottle in front of me, than a full frontal labotomy.

Offline

 

#4 11-07-2019 10:45:26

west4
Member
From: UK
Registered: 04-16-2008
Posts: 645
Website

Re: How to add a new data column to the site search

Hi Nick,

How do I stop this being compulsory?

Cheers,
Bruce.


I'd rather have a full bottle in front of me, than a full frontal labotomy.

Offline

 

#5 11-08-2019 09:03:04

webmaster
Administrator
From: York, PA
Registered: 04-20-2001
Posts: 19798
Website

Re: How to add a new data column to the site search

Assuming your column name is 'customkeywords', and it was set up as a clob/text type field, execute these two statements:

UPDATE core_columndefs SET crequired where id='ecom_prod.customkeywords'

ALTER TABLE ecom_prod CHANGE COLUMN customkeywords customkeywords TEXT DEFAULT NULL


Nick Hendler

Offline

 

#6 11-11-2019 09:12:23

west4
Member
From: UK
Registered: 04-16-2008
Posts: 645
Website

Re: How to add a new data column to the site search

Hi Nick,

Code:

A problem was encountered while executing the SQL statement submitted. The error was reported as: The MySQLi extension encountered a problem submitting an SQL statement. MySQLi reported the error as: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'where id='ecom_prod.prod_keywords' ALTER TABLE ecom_prod CHANGE COLUMN prod_k' at line 1. The following SQL statement was submitted: UPDATE core_columndefs SET crequired where id='ecom_prod.prod_keywords' ALTER TABLE ecom_prod CHANGE COLUMN prod_keywords TEXT DEFAULT NULL

syntax error.

Bruce.


I'd rather have a full bottle in front of me, than a full frontal labotomy.

Offline

 

#7 11-11-2019 09:22:14

webmaster
Administrator
From: York, PA
Registered: 04-20-2001
Posts: 19798
Website

Re: How to add a new data column to the site search

Those are two different statements.  Execute:

UPDATE core_columndefs SET crequired=0 where id='ecom_prod.prod_keywords'

Then execute:

ALTER TABLE ecom_prod CHANGE COLUMN prod_keywords prod_keywords TEXT DEFAULT NULL


Nick Hendler

Offline

 

#8 11-12-2019 10:11:26

west4
Member
From: UK
Registered: 04-16-2008
Posts: 645
Website

Re: How to add a new data column to the site search

Hi Nick,

Code:

A problem was encountered while executing the SQL statement submitted.
 The error was reported as: The MySQLi extension encountered a problem submitting an SQL statement. MySQLi reported the error as: 
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'where id='ecom_prod.prod_keywords'' at line 1. 
The following SQL statement was submitted: UPDATE core_columndefs SET crequired where id='ecom_prod.prod_keywords'

But the second line will enter ok, but without changing the compulsory state.

Cheers,
Bruce.

Last edited by west4 (11-12-2019 10:13:17)


I'd rather have a full bottle in front of me, than a full frontal labotomy.

Offline

 

#9 11-12-2019 10:14:26

webmaster
Administrator
From: York, PA
Registered: 04-20-2001
Posts: 19798
Website

Re: How to add a new data column to the site search

I apologize for the syntax error.  The SQL should be:

UPDATE core_columndefs SET crequired=0 where id='ecom_prod.prod_keywords'

I updated the post above as well.


Nick Hendler

Offline

 

#10 11-12-2019 10:56:32

west4
Member
From: UK
Registered: 04-16-2008
Posts: 645
Website

Re: How to add a new data column to the site search

Nick,

Still an issue...

Code:

A problem was encountered while executing the SQL statement submitted. The error was reported as: The MySQLi extension encountered a problem submitting an SQL statement. MySQLi reported the error as: Unknown column 'id' in 'where clause'. The following SQL statement was submitted: UPDATE core_columndefs SET crequired=0 where id='ecom_prod.prod_keywords'

I'd rather have a full bottle in front of me, than a full frontal labotomy.

Offline

 

#11 11-12-2019 11:13:50

zanart
Member
From: bedford
Registered: 04-02-2008
Posts: 1706

Re: How to add a new data column to the site search

UPDATE core_columndefs SET crequired=0 WHERE cid='ecom_prod.prod_keywords'


think it should be cid not id

Last edited by zanart (11-12-2019 11:14:18)


Rob

Offline

 

#12 11-12-2019 11:22:58

west4
Member
From: UK
Registered: 04-16-2008
Posts: 645
Website

Re: How to add a new data column to the site search

Hi Nick,

Thanks, Nailed it.

Cheers,
Bruce


I'd rather have a full bottle in front of me, than a full frontal labotomy.

Offline

 

Board footer