Search function not working properly (collation problem)

[dennisysh]

When i type certain product name into the search box and click search, it always shows a message like this: There is no product that matches the search criteria.
Doesnt matter what I type, the result is still the same.
website: http://www.theperfumeparadise.com

Anybody knows how to fix it, please advise.

Thanks.
Dennis

[DrByte]

Hmmm .. searching for "women" brings back results.

[dennisysh]

Hi DrByte. Do you know how to fix it?

[DrByte]

I'm guessing your database collation is set to something that's not case-insensitive, which is preventing the search from working properly. What is your collation set to on all your tables?

[dennisysh]

I am not sure. How to check collation? I dont make any changes on the programming. everything is just set to default.

[DrByte]

Use phpMyAdmin and look at your list of database tables. What collation are they set to?

[dennisysh]

I see that most of the list in the table callation is set to utf8_bin. Is this the correct information? Please see the attachment

[DrByte]

What version of Zen Cart are you using?
How did you install it? (Did you download it yourself and run the install yourself? (where did you download it from?), or did you use a one-click install from your hosting company, or what?)

[DrByte]

Upon further testing, it's clear that your utf8_bin collation is what's causing it, because it's not case-insensitive. You need to change the searchable fields to use a case-insenstive collation. In your case that would be utf8_general_ci instead of utf8_bin.
To do that, run the following by going to Admin->Tools->Install SQL Patches, and copy/paste the following into the box there and click Send:

alter table products change products_model products_model varchar(32) collate utf8_general_ci;
alter table products_description change products_description products_description text collate utf8_general_ci;
alter table products_description change products_name products_name varchar(64) collate utf8_general_ci;
alter table meta_tags_products_description change metatags_keywords metatags_keywords text collate utf8_general_ci;
alter table meta_tags_products_description change metatags_description metatags_description text collate utf8_general_ci;

[dennisysh]

Hi DrByte.
Many thanks for your help. It is working now.



And, I used one-click install from my hosting company.