Mike MacKechnie Posted November 11, 2016 Share Posted November 11, 2016 Only just noticed this. We've been running a Cubecart store for several years now.... Scandavian characters such as ø don't display correctly in the orders area of the Admin panel. The display fine on the customer's screen, and in the Admin customer area: See the screenshots for more details. Anybody got any ideas on how to fix this? CubeCart Version 6.1.1 PHP Version 5.6.27 MySQL Version5. 6.34-log Browser user-agent Mozilla/5.0 (Windows NT 6.1; WOW64; rv:49.0) Gecko/20100101 Firefox/49.0 Server Software Apache the database table collation is ut8_unicode_ci. Quote Link to comment Share on other sites More sharing options...
bsmither Posted November 11, 2016 Share Posted November 11, 2016 I have edited a customer to have the character æ in its name. This character is showing correctly in my installation. It is also showing correctly in the database utility I use to view table contents directly. (Please view directly your database _customer table and _order_summary table to see if the character is displayed correctly.) Quote Link to comment Share on other sites More sharing options...
Mike MacKechnie Posted November 11, 2016 Author Share Posted November 11, 2016 Ok, in the _customer table it displays correctly. However in the _order_summary table it displays INcorrectly as Mr Bjørn Dalvang. both fields are utf8_unicode_ci collation. Quote Link to comment Share on other sites More sharing options...
bsmither Posted November 11, 2016 Share Posted November 11, 2016 The database utility should indicate the characterset (there is also the collation, but it is the characterset to check). Plus, starting with MySQL 4.1, each column can have a specific characterset. Quote Link to comment Share on other sites More sharing options...
Mike MacKechnie Posted November 12, 2016 Author Share Posted November 12, 2016 Here is an export of the order_summary table structure: -- -- Table structure for table `CubeCart_order_summary` -- CREATE TABLE IF NOT EXISTS `CubeCart_order_summary` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `cart_order_id` varchar(18) COLLATE utf8_unicode_ci NOT NULL, `customer_id` int(10) unsigned NOT NULL DEFAULT '0', `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `line1` varchar(150) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `line2` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL, `town` varchar(150) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `state` varchar(150) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `postcode` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `country` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, `name_d` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `line1_d` varchar(150) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `line2_d` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL, `town_d` varchar(150) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `state_d` varchar(150) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `postcode_d` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `country_d` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, `phone` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, `mobile` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, `subtotal` decimal(16,2) unsigned NOT NULL DEFAULT '0.00', `discount` decimal(16,2) unsigned NOT NULL DEFAULT '0.00', `total` decimal(16,2) unsigned NOT NULL DEFAULT '0.00', `total_tax` decimal(16,2) unsigned NOT NULL DEFAULT '0.00', `shipping` decimal(16,2) unsigned NOT NULL DEFAULT '0.00', `status` tinyint(1) unsigned NOT NULL DEFAULT '1', `sec_order_id` varchar(30) CHARACTER SET utf8 DEFAULT NULL, `ip_address` varchar(45) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `order_date` int(10) unsigned NOT NULL DEFAULT '0', `email` varchar(254) COLLATE utf8_unicode_ci DEFAULT NULL, `comments` text CHARACTER SET utf8, `ship_date` date DEFAULT NULL, `ship_method` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, `weight` decimal(16,3) NOT NULL DEFAULT '0.000', `ship_product` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, `gateway` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `currency` varchar(5) CHARACTER SET utf8 NOT NULL DEFAULT '', `customer_comments` text COLLATE utf8_unicode_ci, `extra_notes` text CHARACTER SET utf8 NOT NULL, `tax1_disp` varchar(128) CHARACTER SET utf8 DEFAULT NULL, `tax1_amt` decimal(30,2) NOT NULL DEFAULT '0.00', `tax2_disp` varchar(128) CHARACTER SET utf8 DEFAULT NULL, `tax2_amt` decimal(30,2) NOT NULL DEFAULT '0.00', `tax3_disp` varchar(128) CHARACTER SET utf8 DEFAULT NULL, `tax3_amt` decimal(30,2) NOT NULL DEFAULT '0.00', `offline_capture` blob, `ship_tracking` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, `company_name` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, `company_name_d` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, `basket` text CHARACTER SET utf8, `lang` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, `dashboard` tinyint(1) unsigned NOT NULL DEFAULT '0', `title` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, `first_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `last_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `title_d` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `first_name_d` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `last_name_d` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `discount_type` char(2) COLLATE utf8_unicode_ci DEFAULT NULL, `note_to_customer` text COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `cart_order_id_2` (`cart_order_id`), KEY `customer_id` (`customer_id`), KEY `status` (`status`), KEY `email` (`email`), KEY `order_date` (`order_date`), KEY `cart_order_id` (`cart_order_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=6950 ; Also SELECT * FROM information_schema.SCHEMATA S WHERE schema_name = "wymerukc_cubecart" gives DEFAULT_CHARACTER_SET_NAME latin1 DEFAULT_COLLATION_NAME latin1_swedish_ci Quote Link to comment Share on other sites More sharing options...
bsmither Posted November 12, 2016 Share Posted November 12, 2016 I do not see anything actually wrong with this. The customer shown has orders going back quite a ways. I wonder of this customer has chosen an address from his Address Book to populate the order's Billing and Shipping address blocks. Still, I would think that the transporting of the data would correct itself from the Address Book to the form, then to the Order Summary table. Please confirm the name in this customer's Address Book table. Are there any other non-utf8 spelled names in this table? Quote Link to comment Share on other sites More sharing options...
Mike MacKechnie Posted November 12, 2016 Author Share Posted November 12, 2016 Good stuff Brian, I think we're getting somewhere - I've found 486 customers in the address book with non-ASCII characters in one or more of the fields. For the record I used this SQL to find them: SELECT * FROM `CubeCart_addressbook` WHERE first_name <> CONVERT(first_name USING ASCII) OR last_name <> CONVERT(last_name USING ASCII) OR company_name <> CONVERT(company_name USING ASCII) OR line1 <> CONVERT(line1 USING ASCII) OR line2 <> CONVERT(line2 USING ASCII) OR town <> CONVERT(town USING ASCII) OR state <> CONVERT(state USING ASCII) OR postcode <> CONVERT(postcode USING ASCII) OR country <> CONVERT(country USING ASCII); Most of them are fine, but after manually inspecting them (what the hell, it's Saturday, and there's no football on) I have found 11 that contain unconverted characters. Now I remember that the original address book was imported from a very old MS Access database, so I think that may be where our problem is (the 11 bad rows were at the low end of the address id's). I'll just plough through the 11 rows and figure out how "Ariën", "Ãstorp", "Skåne ", "14230 SkogÃ¥s", "Günter", "José Artur", "øivind", "Tingbackevägen 72", "Hattyú u 17/A 3. 1.", "Bjørn", "Csordás" and "CONCEIÇÃO" are supposed to render . Quote Link to comment Share on other sites More sharing options...
bsmither Posted November 12, 2016 Share Posted November 12, 2016 Good find! Eleven rows is very reasonable. Perhaps even less if some of the customers are not active anymore. Quote Link to comment Share on other sites More sharing options...
Mike MacKechnie Posted November 13, 2016 Author Share Posted November 13, 2016 Yes I remember I did a data cleanse on the records in the _customer table way back when we moved over to CubeCart - I must have neglected to do the same for the _addressbook table. Problem solved. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.