Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Search with Special Characters in swedish get 0 results


chirag nagar

Recommended Posts

Hi,

 

I have been running an OsC (v2.2 RC2a)

I am using swedish and english language.

My question is when there is any swedish special character like å,ä,ö,Å,Ä,Ö in search box i got 0 result.

For example my selected language is swedish. if i write 'låsbricka' in search box and press search button, i got 0 result.

In url i got following

catalog/advanced_search_result.php?search_in_description=1&keywords=l%E5sbricka.

I have note that å becomes %E5 in url.

I am using Security Pro and ultimate_seo_urls5.

I have made correction to Security Pro to exclude search file like below in application_top.php

$security_pro->addExclusion( 'advanced_search_result.php' );

But i cannot get correct result.

 

I would be grateful for any help and suggestions.

Link to comment
Share on other sites

What character set is your site and database? Is xE5 an å in that encoding? If your pages and database are UTF-8, someone appears to be turning non-ASCII letters in the URL Query String into Latin-1, which will result in a search failure. Of course, in that case the å ought to be a multibyte UTF-8 character in the first place, and be translated into %nn%nn in the Query String. If everything is Latin-1 from end-to-end, we have to look elsewhere. Perhaps the %E5 is fine and the problem is later on, after the %E5 has been converted back into an å. Maybe some kind of "sanitizer" is corrupting the å before it gets to the SQL statement? Maybe any non-ASCII character is tossed out? You might check with the authors of Security Pro and Ultimate SEO URLs5 whether there are any limitations in handling non-ASCII text. Be sure to understand what character encoding is used on your site, and tell them.

Link to comment
Share on other sites

You might try temporarily disabling/removing Security Pro and Ultimate SEO URLs5 to see if one of them is causing the problem. If it is, find which one it is and contact the support forum for it.

Link to comment
Share on other sites

Mr.phil Thanks for your quick response.

 

I have used iso-8859-1 for swedish language for site and latin1_swedish_ci for database.

 

Any further instruction?

 

You need to use UTF-8 for your language and database coallition must be set to utf8_general_ci.

 

+ You can try this (I have used it in my client`s websites) edit:

 

in

admin/includes/functions/database.php

and

/includes/functions/database.php

do the folowing

 

Find:

if ($$link) mysql_select_db($database);

Replace with:

//if ($$link) mysql_select_db($database);
    if ($$link) {
    mysql_select_db($database);
    $encode = 'utf8';
    mysql_query("set names '".$encode."'");
    mysql_query("set character_set_client='".$encode."'");
    mysql_query("set character_set_results='".$encode."'");
    mysql_query("set character_set_server='".$encode."'");
    mysql_query("set character_set_database='".$encode."'");
    mysql_query("set character_set_connection='".$encode."'");
    mysql_query("set session collation_connection='".$encode."_general_ci'");
    mysql_query("set collation_connection='".$encode."_general_ci'");
    mysql_query("set collation_server='".$encode."_general_ci'");
    header('Content-type: text/html; charset=utf8');
}

 

 

Hope this helps.

 

Boriss

Link to comment
Share on other sites

You might try temporarily disabling/removing Security Pro and Ultimate SEO URLs5 to see if one of them is causing the problem. If it is, find which one it is and contact the support forum for it.

 

Though i have disabled Security Pro and Ultimate SEO URLs5, can't get any result.

Any further help?

Thanks and Regards

 

Soeb Shaikh

Link to comment
Share on other sites

Though i have disabled Security Pro and Ultimate SEO URLs5, can't get any result.

Any further help?

 

Did you tryed to use my suggestion?

 

p.s. for any case try to save - advanced_search_result.php in uft-8 encoding, save it, close it and open to check if this is really saved in ustf-8.

Link to comment
Share on other sites

Did you tryed to use my suggestion?

 

p.s. for any case try to save - advanced_search_result.php in uft-8 encoding, save it, close it and open to check if this is really saved in ustf-8.

 

Not yet, but actually i found the cause.

 

For example i am searching products with name which contain 'å'.

 

i have print search query and got following

"select distinct p.products_model, pd.products_name, m.manufacturers_name, p.products_quantity, p.products_image, p.products_weight, p.products_ordered, m.manufacturers_id, p.products_quantity, p.products_featured, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, NULL as specials_new_products_price, NULL as final_price from products p left join manufacturers m using(manufacturers_id), products_description pd, categories c, products_to_categories p2c where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '7' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%å%' or p.products_model like '%å%' or m.manufacturers_name like '%å%' or pd.products_description like '%å%') ) and find_in_set('0', products_hide_from_groups) = 0 and find_in_set('0', categories_hide_from_groups) = 0 order by pd.products_name"

 

but in database there is no entry with å in products_description table.

In database å is replaced by 'å'.Therefore query returns 0 result.

 

if i print static query like below i got correct result.

 

"select distinct p.products_model, pd.products_name, m.manufacturers_name, p.products_quantity, p.products_image, p.products_weight, p.products_ordered, m.manufacturers_id, p.products_quantity, p.products_featured, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, NULL as specials_new_products_price, NULL as final_price from products p left join manufacturers m using(manufacturers_id), products_description pd, categories c, products_to_categories p2c where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '7' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%å%' or p.products_model like '%å%' or m.manufacturers_name like '%å%' or pd.products_description like '%å%') ) and find_in_set('0', products_hide_from_groups) = 0 and find_in_set('0', categories_hide_from_groups) = 0 order by pd.products_name";

 

My further question is how to enter special character 'å' in database.

Link to comment
Share on other sites

Steps to performe to get it work:

 

1) Make changes in:

admin/includes/functions/database.php

and

/includes/functions/database.php

do the folowing

 

Find:

if ($$link) mysql_select_db($database);

 

Replace with:

//if ($$link) mysql_select_db($database);
		 if ($$link) {
		 mysql_select_db($database);
		 $encode = 'utf8';
		 mysql_query("set names '".$encode."'");
		 mysql_query("set character_set_client='".$encode."'");
		 mysql_query("set character_set_results='".$encode."'");
		 mysql_query("set character_set_server='".$encode."'");
		 mysql_query("set character_set_database='".$encode."'");
		 mysql_query("set character_set_connection='".$encode."'");
		 mysql_query("set session collation_connection='".$encode."_general_ci'");
		 mysql_query("set collation_connection='".$encode."_general_ci'");
		 mysql_query("set collation_server='".$encode."_general_ci'");
		 header('Content-type: text/html; charset=utf8');
}

 

2) Go to your languge files:

includes/languages/english.php

includes/languages/___anylanguge___.php

 

Chage charset:

 

from what you have to : utf-8

 

3) Go to your phpmysqladmin (If you don`t trust me try to make chnages to TABLE products_description COLUMN products_name)

Change the all TABLES and all COLUMNS (!!! Important - COLUMNS this is need to be done!!!! otherwise it will not work)

Chage encoding from latin1_swedish_ci (or what ever you have there) to utf8_general_ci

 

4) Login to your shops admin panel and create or chage existing product:

 

Chage the name of it let`s say to this one:

 

Rollock låsbricka

 

or

 

Āboļu pīrādziņš (apple pie :D )

 

5) Then you can retur to your phpmyadmin and search for the new product you have added. if the name you entered in shop`s admin panel when you have been adding the product with the name in the table, if they area the same with no HTML characters in there then you can process with testing your shop (steps 6 and 7).

 

6) Go to your shop find the product, check it for special characters if all looks like the name u entered in shops admin Products Name then it`s ok.

 

7) Then try the search option in the shop and search for the products name you entered.

 

8) If all is working then you secceed and can now starting with channging the chartset in all your database where is any text saved.

 

 

Hope this helps and have a successfull editing.

p.s. I`m using this solution on my clients shops.

 

Boriss

Link to comment
Share on other sites

 

but in database there is no entry with å in products_description table.

In database å is replaced by 'å'.Therefore query returns 0 result.

 

 

in your back-office, did you used an editor (like ckeditor) for your product description ?

Link to comment
Share on other sites

in your back-office, did you used an editor (like ckeditor) for your product description ?

 

That`s dose not mather use the ckeditor or not... "been there done that..." ;)

Link to comment
Share on other sites

simple cleanup to change character :

ALTER TABLE `oscommerce`.`action_recorder` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`address_book` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`address_format` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`administrators` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`banners` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`banners_history` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`categories` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`categories_description` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`configuration` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`configuration_group` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`counter` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`counter_history` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`countries` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`currencies` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`customers` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`customers_basket` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`customers_basket_attributes` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`customers_info` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`geo_zones` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`languages` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`manufacturers` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`manufacturers_info` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`newsletters` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`orders` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`orders_products` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`orders_products_attributes` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`orders_products_download` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`orders_status` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`orders_status_history` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`orders_total` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`products` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`products_attributes` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`products_attributes_download` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`products_description` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`products_images` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`products_notifications` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`products_options` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`products_options_values` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`products_options_values_to_products_options` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`products_to_categories` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`reviews` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`reviews_description` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`sec_directory_whitelist` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`sessions` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`specials` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`tax_class` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`tax_rates` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`whos_online` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`zones` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `oscommerce`.`zones_to_geo_zones` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

 

replace oscommerce with the name of your database.

Link to comment
Share on other sites

Finally solved.

Thank you very much to all of the repliers.

 

OK, can you post a summary of what you found the problem to be, and how you ended up fixing it? That would be useful for people coming after you and reading this thread. I'm rather puzzled by why a character like å would be converted into & aring; in the database -- I don't recall ever hearing about such a thing before. It should certainly be independent of whether you're using Latin-1 or UTF-8 encoding.

Link to comment
Share on other sites

Hello @ all.

 

1. @@foxp2 - Thank you, your post with altering is really helpful.

2. @@MrPhil - you have two options, first is my suggestion (if site will be multilingual), but the second (if the site will be only in Swedish) solution from "chirag nagar" (I had a talk to him in chat) fix, he altered tables with latin1_sweden_ci this helped him.

Link to comment
Share on other sites

latin1_swedish_ci is the default for MySQL. So things broke when he changed them to UTF-8? latin-1 is fine for Swedish and Western European languages. If you think you ever might want to support text written in other scripts (Central/Eastern European, Greek, Cyrillic, etc.) then you would want to go to UTF-8.

Link to comment
Share on other sites

latin1_swedish_ci is the default for MySQL. So things broke when he changed them to UTF-8? latin-1 is fine for Swedish and Western European languages. If you think you ever might want to support text written in other scripts (Central/Eastern European, Greek, Cyrillic, etc.) then you would want to go to UTF-8.

 

Sorry my mistake it was - utf8_swedish_ci he swithed to.

Link to comment
Share on other sites

OK, can you post a summary of what you found the problem to be, and how you ended up fixing it? That would be useful for people coming after you and reading this thread. I'm rather puzzled by why a character like å would be converted into & aring; in the database -- I don't recall ever hearing about such a thing before. It should certainly be independent of whether you're using Latin-1 or UTF-8 encoding.

 

Hi All,

 

I have done only 1 change in TABLE product_description.

I have changed products_description columns Collation to utf8_swedish_ci from latin1_swedish_ci and resave all the products which contain special characters from admin panel.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...