Kryptronic Software Support Forum

haydn · 11-12-2004 11:23:38

The search facility doesn't appear to work correctly. I am listing CDs on my site, and want people to be able to enter into the search box, say, Mozart and Karajan, to find only pieces by Mozart (one field), conducted by Karajan (in another field). At present it finds nothing under the "all of the words" option. If we try under "any of the words", clearly it finds all Mozart and all Karajan but I want to limit the find to only CDs that meet both criteria. Using "exact phrase" defeats the purpose, as the two names are in different fields (in the same record set).

webmaster · 11-12-2004 13:54:25

The problem you're having is that you're using:

Mozart and Karajan

With an all of the words search. CCP is looking for 'Mozart', 'and' and 'Karajan'. If you searched for:

Mozart Karajan

It would return what you want. If you would like to strip words like AND and OR from the search string, make the following edit:

In ./cgi-bin/library/modules/ste_prod.pl in the routine 'ste_prod_disp' look for:

Code:


$fd_searchstr = $q->param('searchstr');
$fd_searchstr = database_dblquote($fd_searchstr);

$fd_searchstr_encoded = vars_urlencode($fd_searchstr);

And change to:

Code:


$fd_searchstr = $q->param('searchstr');
$fd_searchstr =~ s/ OR //gis;
$fd_searchstr =~ s/ AND //gis;
$fd_searchstr = database_dblquote($fd_searchstr);

$fd_searchstr_encoded = vars_urlencode($fd_searchstr);

haydn · 11-18-2004 12:15:43

Thanks for your reply, but I was inputting only the 2 strings, without any linking words. I want my clients to be able to put in Mozart (a string that might appear in the product_desclong field) and Karajan (a string that might appear in the product_name field) and find only those CDs of music by Mozart that are conducted by Karajan. I thought that the "all of these words" command should do this, but it's not finding anything at all.

Hope you can help with this. :-)

James

abermusic · 11-18-2004 12:39:58

One more question, please. How do I get the results of any search to show onscreen in the same onscreen layout as my basic product listings? At present they show in a series of blocks, and I would prefer them as a basic table laid out across the screen. Thanks

hellraiser · 11-22-2004 13:21:29

hi,
I have an additional qusetion on the topic of search refinement.....

can you chop off a plural noun in the search. like, if someone searches our site for "vases", nothing appears, but if they search for "vase" all vases appear?????

thanks

as for search refinement for a paticular category, im sure a custom mod can be placed on the advanced search page that will allow one to search within a certain category. With nicks sql logic, and all this should be pretty easy, however I could (and probably am) wrong. anyway, if anyone has refined their search functions, please post as Im sure this is a importaint feature for e commerce.

webmaster · 11-24-2004 13:48:27

To chop off the 's' at the end of any word in the search string, simply do this:

Code:


$fd_searchstr = $q->param('searchstr');
$fd_searchstr =~ s/ OR //gis;
$fd_searchstr =~ s/ AND //gis;
$fd_searchstr =~ s/s / /gis;
$fd_searchstr =~ s/s$//gis;
$fd_searchstr = database_dblquote($fd_searchstr);

$fd_searchstr_encoded = vars_urlencode($fd_searchstr);

This should be safe as it will still match partial words in all cases. Ie: Somebody searches for 'oceans eleven', the search string is transformed into 'ocean eleven' but still will match as 'oceans' contains 'ocean'.

dtwg · 11-26-2004 17:49:06

It seems an "all words" search will not work on multiple words if the words are only in the product_keywords field -- eg, if you have "dog food" only in the products.csv product_keywords field (and not in the description or such), searching all words on "dog food" will not find any results. If you have "dog food" in the description, an all words search will find it.

Woof :p

webmaster · 12-07-2004 10:49:20

This doesn't seem right. The program will generate a WHERE clause like this for ALL querries:

Code:


WHERE (product_id LIKE %dog% AND product_id LIKE %food%)
OR (product_name LIKE %dog% AND product_name LIKE %food%)
OR (product_number LIKE %dog% AND product_number LIKE %food%)
OR (product_keywords LIKE %dog% AND product_keywords LIKE %food%)
OR (product_descshort LIKE %dog% AND product_descshort LIKE %food%)
OR (product_desclong LIKE %dog% AND product_desclong LIKE %food%)

phunter121 · 05-01-2005 06:56:27

I have the same problem as the originator of this string. It looks like the "all of the words" search criteria only works with the product_descshort field on the product.csv file.

If I search on "stop barking", it produces the SQL below. On the face of it, it looks fine, but it definately only works if the product_descshort field contains the words that are being search for. If I use the "exact Words" criteria, the correct results are produced.

Perhaps someone else could try this to see if this is a bug in the code or perhaps it is a config issue ?

Code:

SELECT product_id,product_name,product_number,product_pricestatus,product_regprice,product_saleprice,product_volprice,product_voltext,product_recurprice,product_recurstatus,product_usecatdisc,product_taxcountry,product_useinv,product_inv,product_imglg,product_imgsm,product_descshort,product_desclong,product_xopt FROM product 
WHERE
 (product_id CLIKE '%stop%' AND product_id CLIKE '%barking%') 
OR (product_name CLIKE '%stop%' AND product_name CLIKE '%barking%') 
OR (product_number CLIKE '%stop%' AND product_number CLIKE '%barking%') 
OR (product_keywords CLIKE '%stop%' AND product_keywords CLIKE '%barking%') 
OR (product_descshort CLIKE '%stop%' AND product_descshort CLIKE '%barking%') 
OR (product_desclong CLIKE '%stop%' AND product_desclong CLIKE '%barking%') LIMIT 0, 5

dtwg · 05-01-2005 07:14:41

I kind of remember having a similar problem myself when the product database is in csv mode. When I change to MySQL, it clears up.

Is your product db in csv mode?

Dave

webmaster · 05-03-2005 09:37:22

Perhaps there is an issue in the Perl DBD::AnyData and SQL::Statement modules - it could be flaky on complex SQL like this. Can you confirm whether this is an issue in MySQL as well?

phunter121 · 05-11-2005 15:04:12

Yes, I am using .csv file and I suspect there is a problem interpreting the complex SQL generate when using the "all of the words" search option.

The current SQL searches on 6 fields, but I made a small amendment to ste_prod.pl so that it only searches on 3 fields (namely: product_name, product_keywords & product_desclong). this works fine and to be honest suits my needs.

To make the change, find the following line in ste_prod.pl:

Code:

my @fields = (product_id,product_name,product_number,product_keywords,product_descshort,product_desclong);

AND REPLACE IT WITH:

Code:

my @fields = (product_name,product_keywords,product_desclong);

You could also try adding 4 or 5 field and see what happens

Kryptronic Software Support Forum

#1 11-12-2004 11:23:38

Search Site Facilities

#2 11-12-2004 13:54:25

Re: Search Site Facilities

Code:

Code:

#3 11-18-2004 12:15:43

Re: Search Site Facilities

#4 11-18-2004 12:39:58

Re: Search Site Facilities

#5 11-22-2004 13:21:29

Re: Search Site Facilities

#6 11-24-2004 13:48:27

Re: Search Site Facilities

Code:

#7 11-26-2004 17:49:06

Re: Search Site Facilities

#8 12-07-2004 10:49:20

Re: Search Site Facilities

Code:

#9 05-01-2005 06:56:27

Re: Search Site Facilities

Code:

#10 05-01-2005 07:14:41

Re: Search Site Facilities

#11 05-03-2005 09:37:22

Re: Search Site Facilities

#12 05-11-2005 15:04:12

Re: Search Site Facilities

Code:

Code:

Board footer