Unwanted characters showing up in Comments section of Admin - Orders

Have latest Edit Orders, Easy Populate, Image Handler4, Zen Lightbox installed.

Updating my site to be responsive and in final stages of testing. Made up test customer and placed some orders, made up invoices, emailed, etc. All looks ready to go except I see this in my Comments section of Order Status History & Comments:

The customer receives email that looks fine:

But if he logs into his account and looks online he sees the extra characters in comments instead of neatly formatted message.

In the past my Admin comments section looked just the same as the email looks- correctly formatted and without the "\r\n\r\nJoe,\r\n\r\n" characters. Not sure what I did but think it might have something to do with updating database tables.

Looking for advice on how to correct the formatting issues and get everything looking normal again in the Admin comments order section and for the customer who checks comments online.

Thanks, Ed

I assume that you are using ZenCart v1.5.4 but from what version did you upgrade?

Use phpmyadmin and check what collation your tables are
latin or utf8

If the former use this to change them
https://www.zen-cart.com/downloads.php?do=file&id=1318

Kobra thanks for the reply.

Yes the new responsive test site is v1.5.4. Version info:

Database Patch Level: 1.5.4
v1.5.4 [2016-01-22 15:08:29] (Version Update 1.5.3->1.5.4)
v1.5.3 [2016-01-22 15:08:29] (Version Update 1.5.2->1.5.3)
v1.5.2 [2016-01-22 15:08:28] (Version Update 1.5.1->1.5.2)
v1.5.1 [2016-01-22 15:08:22] (Version Update 1.5.0->1.5.1)
v1.5.0 [2016-01-20 17:39:37] (Fresh Installation)

Both old and new site databases say Server charset: UTF-8 Unicode (utf8).

The site that is soon to be replaced is v1.5.1. I copied some database tables over from it's MySQL5.1 database into the new database which is 5.5.47-cll . The only issues have observed so far in testing is this Comments text issue, fingers crossed.

Ted

That character sequence is a carriage return with a line feed... Is a windows system involved somehow in all this?

No Windows system involved except on my PC at home. Here is server info:

Server Host: xxxxxxx.com (xxx.xx.20.51) Database Host: localhost (127.0.0.1)

Server OS: Linux 2.6.32-604.30.3.lve1.3.63.el6.x86_64 Database: MySQL 5.5.47-cll

Server Date: 02/06/2016 18:38:33 Database Date: 02/06/2016 18:38:33

Server Up Time: 18:38:33 up 15 days, 4:22, 0 users, load average: 1.15, 1.29, 1.64 HTTP Server: Apache

PHP Version: 5.5.31 (Zend: 2.5.0) PHP Memory Limit: 128M PHP Safe Mode: Off

PHP File Uploads: On Max Size: 2M POST Max Size: 8M

Database Data Size: 7,518 kB Database Index Size: 1,969 kB

Ted

Both old and new site databases say Server charset: UTF-8 Unicode (utf8).
Actually, what is the collation of the tables - use phpmyadmin and look at the right of each table

OK. The 96 Zen-Cart specific tables all have "utf8_general_ci" under Collation column.

The responsive template I am using adds a 97th table and it has "latin1_swedish_ci" under Collation. Another difference with that table is that it has "InnoDB" under Type column where all the other tables have "MyISAM" under Type.

The info in this table adds Settings under Admin>Tools and lets me change the color scheme of site, tagline, logo image, and a couple other things easily. It's been working fine as far as I can tell.

At the bottom in phpMyAdmin I see this text: " 97 tables | Sum | 24,903 | MyISAM | latin1_swedish_ci | 9.3 Mib | 9.9 KiB "

Thanks, Ted

It's got nothing to do with the CHARSET (or DB_CHARSET used). It's the fact that the information, upon recording in the database, was over-prepared.

If you look at the orders_history record for the order in question, you'll see data like

\\r\\n\\r\\n

See those additional slashes, that's what's causing the CRLF (\r\n) to display as \r\n ... the data with the backslashes have been run through zen_db_input and then inserted into the database using zen_db_perform.

Thanks.

I checked order #518 in the database table orders_status_history and under "comments" it shows

-\r\n\r\nJoe,\r\n\r\nHave received your payment, thank you. The TEST ITEM will be shipping out tomorrow. The tracking number is sdfgskldfjsdlkfjsdlkfjsd654684.\r\n\r\nLook forward to doing more business.\r\n\r\nThanks, Ted

Checked out the link- Zen Cart Rule #3: Don’t Over-Prepare . Not sure how well I understand but did use info to do a search using Developer's Tools and came up with some things to check under ADMIN/orders.php:

Line #184 : '" . zen_db_input($comments) . "')");

And this under ADMIN/edit_orders.php:

Line #224 : 'comments' => zen_db_input($comments),

Looked for stray single- (‘) or double-quotes (“) . Tried changing a things but just came up with blank pages in Admin when I tried to click on the orders page.

Right, I misspoke regarding the extra backslashes in the data. What you *should *be seeing in the comments field is:

Joe,

Have received your payment, thank you. The TEST ITEM will be shipping out tomorrow. The tracking number is sdfgskldfjsdlkfjsdlkfjsd654684.

Look forward to doing more business.

Thanks, Ted

where those \r\n sequences were converted to their CRLF equivalents.

I'm pretty sure that it's the edit_orders processing, as the built-in orders.php handling performs the database insert "directly" (i.e. using the $db->Execute function) while edit_orders uses the zen_db_perform function.

You might try editing your copy of edit_orders.php, changing this block:

      $sql_data_array = array(
        'orders_id' => (int)$oID,
        'orders_status_id' => [B]zen_db_input[/B]($status),
        'date_added' => 'now()',
        'customer_notified' => [B]zen_db_input[/B]($customer_notified),
        'comments' => [B]zen_db_input[/B]($comments),
      );
      // BEGIN TY TRACKER 3 - INCLUDE DATABASE FIELDS IN STATUS UPDATE
      foreach($track_id as $id => $track) {
        $sql_data_array['track_id' . $id] = [B]zen_db_input[/B]($track);
      }
      unset($id); unset($track);
      // END TY TRACKER 3 - INCLUDE DATABASE FIELDS IN STATUS UPDATE
      zen_db_perform(TABLE_ORDERS_STATUS_HISTORY, $sql_data_array);

removing the calls to zen_db_input, as follows:

      $sql_data_array = array(
        'orders_id' => (int)$oID,
        'orders_status_id' => $status,
        'date_added' => 'now()',
        'customer_notified' => $customer_notified,
        'comments' => $comments,
      );
      // BEGIN TY TRACKER 3 - INCLUDE DATABASE FIELDS IN STATUS UPDATE
      foreach($track_id as $id => $track) {
        $sql_data_array['track_id' . $id] = $track;
      }
      unset($id); unset($track);
      // END TY TRACKER 3 - INCLUDE DATABASE FIELDS IN STATUS UPDATE
      zen_db_perform(TABLE_ORDERS_STATUS_HISTORY, $sql_data_array);

Changing the code in ADMIN/edit_orders.php as suggested has solved the problem.

Just made a donation to the Zen Cart team in appreciation for all the help I have gotten on the forum for several issues from lat9, MC12345678, kobra, Dr. Byte, picaflor-azul, and others.

Thanks and please keep up the good work.

It seems as though after applying this fix, multiline comments now contain embedded HTML break characters:

**Test remark 1<br />Test remark 2<br />Test remark 3