utf8mb4 + emoji owes again.

[chibipaw]

Configuration:
AWS 4.14.42-52.37.amzn1.x86_64
Zen-Cart 1.5.5f
MariaDB 10.2.11 (RDS)
PHP v7.1.13

mc12345678 and Design75 had briefly touched about similar problem in recent past regarding chinese customers using chinese. That was more or less resolved by switching to utf8mb4, Chinese characters now work, and it is know that emoji will still break things.

Being many of my customers are young female, emoji use is starting to become a real problem. When ever a customer use emoji in their order comment field, the system throws an error and will result their order not showing any product at all. Telling customers not to use emoji is not going to be a realistic option either. (kids will be kids)

Is there a way I can make either php or zencart to filter out emoji in the comment fields?
Notice also authorized.net's automated comments on AUTH: and TransID: numbers aren't included either.

Here's the log and screen shot. Not what was written right after the customer wrote "Thank you!".

[04-Jun-2018 16:13:38 America/Los_Angeles] PHP Fatal error: 1366:Incorrect string value: '\xF0\x9F\x98\x8A' for column 'comments' at row 1 :: INSERT INTO orders_status_history (orders_id, orders_status_id, date_added, customer_notified, comments) VALUES ('16144', '1', now(), '1', 'Could I have two brand shopping bags included in my order. Thank you!ð') ==> (as called by) /home/shopping/includes/functions/functions_general.php on line 978 <== in /home/shopping/includes/classes/db/mysql/query_factory.php on line 171

[mc12345678]

What all was changed to try to support utf8mb4?

[chibipaw]

What all was changed to try to support utf8mb4?

That part of the shopping cart (customer order comment) is pretty much vanilla. The previous thread was about chin ease customers with chinese names. So changing all the entries on the DB to Ut8mb4 allowed the system to accept chinese characters. however, when someone uses emoji, Php (7.1) throws the error listed above.
I think as time moves forward, it's going a regular thing with, millennials and Gen Z types pretty much adapting emoji as part of their everyday vocabulary.

[mc12345678]

So not changes were made to the includes/configure.php file, the admin version of that, nor the includes/languages/YOUR_LANGUAGE.PHP (or the override of it?)

[chibipaw]

So not changes were made to the includes/configure.php file, the admin version of that, nor the includes/languages/YOUR_LANGUAGE.PHP (or the override of it?)

No configuration file has been changed other than running the script to change the database tables all to be utf8mb4. That seems to make chinese characters users happy.

However, this is in my includes/configure.php
define('DB_CHARSET', 'utf8'); // 'utf8' or 'latin1' are most common

So is [admin]/includes/configure.php
define('DB_CHARSET', 'utf8'); // 'utf8' or 'latin1' are most common


This is in my includes/languages/english.php I use english because this is a US website, but it just so happens that I have asian shoppers. All products and templates are in english.
// charset for web pages and emails
define('CHARSET', 'utf-8');

Should there be other things I should be looking for?

[mc12345678]

No configuration file has been changed other than running the script to change the database tables all to be utf8mb4. That seems to make chinese characters users happy.

However, this is in my includes/configure.php
define('DB_CHARSET', 'utf8'); // 'utf8' or 'latin1' are most common

So is [admin]/includes/configure.php
define('DB_CHARSET', 'utf8'); // 'utf8' or 'latin1' are most common


This is in my includes/languages/english.php I use english because this is a US website, but it just so happens that I have asian shoppers. All products and templates are in english.
// charset for web pages and emails
define('CHARSET', 'utf-8');

Should there be other things I should be looking for?

Since the issue is in transferring information to/from the database and that information needs to be utf8mb4, change both instances of DB_CHARSET in the configure.php files to utf8mb4 as:

define('DB_CHARSET', 'utf8mb4');

That should resolve the issue.

[chibipaw]

Since the issue is in transferring information to/from the database and that information needs to be utf8mb4, change both instances of DB_CHARSET in the configure.php files to utf8mb4 as:

define('DB_CHARSET', 'utf8mb4');

That should resolve the issue.

Alright, sound like a logical thing to do. I take that would apply to includes/languages/english.php too?
Thanks

[chibipaw]

Umm after changing the 2 configure.php files , and the english.php

I was able to reproduce the issue trying to check out a product.
the same failure happens when I try to insert an emoji into an order's comment as an admin.

Perhaps Php itself have to be set some how to handle utf8mb4?

Here's the log file note the funny character right before the words "emoji test":

America/Los_Angeles] Request URI: /shopping/index.php?main_page=checkout_process, IP address: 127.0.0.1
#1 trigger_error() called at [/home/shopping/includes/classes/db/mysql/query_factory.php:171]
#2 queryFactory->show_error() called at [/home/shopping/includes/classes/db/mysql/query_factory.php:143]
#3 queryFactory->set_error() called at [/home/shopping/includes/classes/db/mysql/query_factory.php:270]
#4 queryFactory->Execute() called at [/home/shopping/includes/functions/functions_general.php:978]
#5 zen_db_perform() called at [/home/shopping/includes/classes/order.php:703]
#6 order->create() called at [/home/shopping/includes/modules/checkout_process.php:88]
#7 require(/home/shopping/includes/modules/checkout_process.php) called at [/home/shopping/includes/modules/pages/checkout_process/header_php.php:14]
#8 require(/home/shopping/includes/modules/pages/checkout_process/header_php.php) called at [/home/shopping/index.php:36]

[06-Jun-2018 00:29:52 America/Los_Angeles] PHP Fatal error: 1366:Incorrect string value: '\xF0\x9F\x98\x87 e...' for column 'comments' at row 1 :: INSERT INTO orders_status_history (orders_id, orders_status_id, date_added, customer_notified, comments) VALUES ('16144', '2', now(), '1', 'ð emoji test ') ==> (as called by) /home/shopping/includes/functions/functions_general.php on line 978 <== in /home/shopping/includes/classes/db/mysql/query_factory.php on line 171

[chibipaw]

Did some more tested and it turns out that looks like from the using the previous' script to change everything to UTF-8 wasn't enough.
Specific fields inside the database's table must be changed to utf8mb4. such as: order_status_history "comments" fileld's encoding
has to be changed to utf8mb4, and collation to utf8mb4_general_ci

After changing those entries, I was able to complete a checkout using a number of emojis in the comments.

[mc12345678]

Alright, sound like a logical thing to do. I take that would apply to includes/languages/english.php too?
Thanks

No. It does not mean/include/imply the language file(s) as that/those define CHARSET which is not DB_CHARSET and are used for the interface between the data and html not specifically the database and seeing the follow-on message about database conversion to utf8 not being enough is true. If the conversion program was only converting the database to utf8 and not to utf8mb4, then it hasn't/hadn't gone far enough.

The change made to/in the language file(s) (admin and catalog sides) should be reverted back to utf-8 regardless of using the database collation/character set of utf8 or utf8mb4.