Weird Characters - Need an SQL solution!

28 May 2013, 14:39

#1

Totally Zenned

Join Date:: Apr 2010
Posts:: 900
Plugin Contributions:: 0

Weird Characters - Need an SQL solution!

NOTE: Mod, please don't move this to the EP4 support page! I don't think it has anything to do with EP4. I was able to duplicate the problem by manually dropping code into new products. Same thing happened, with or without EP4.

Hi!

I recently uploaded about 1000 products using EasyPopulate 4.0 and hundreds of the products showed up with 'markup' types of weird characters.

To figure out what was going on, I picked a product, navigated to it in Admin, then clicked on SOURCE VIEW. Unable to find anything queer, I hit PREVIEW, then UPDATE. Low and behold, it fixed itself!

I repeated this several times, each time fixing all problems on every product I tried.

Here is an example of a PROBLEM:
Use ESD Shielding bags to follow ANSI/ESD S20.20 fundamental ESD control principle: â€œTransportation of ESDS items outside an Electrostatic Protected Area requires enclosure in static protective materials; low charging and static discharge shielding materials are recommended.â€

This is what it looks like after I UPDATE:
Use ESD Shielding bags to follow ANSI/ESD S20.20 fundamental ESD control principle: “Transportation of ESDS items outside an Electrostatic Protected Area requires enclosure in static protective materials; low charging and static discharge shielding materials are recommended.”

MY QUESTION:
Is there a way to automate the UPDATE process with an SQL command I can just drop in the SQL Query Executor?

It would take me all day to go thru Admin, click on each product and hit Preview, then Update. :no:

Any help would be greatly appreciated!
~Mike

28 May 2013, 16:21

#2

lhungil

Totally Zenned

Join Date:: Feb 2012
Posts:: 1,818
Plugin Contributions:: 2

Re: Weird Characters - Need an SQL solution!

Some programs (such as Microsoft Office) like to assume you want "smart quotes", "em-dashes", etc and will automatically "correct" your text replacing the characters you typed with these. The easiest fix is to either disable "auto-correct" for these in MS Office (or whatever program you are copying the text from) or use a program which does not "auto-correct" these characters.

CKEditor and similiar tools have added detection to "replace" these characters. When you open the product / catalog page using the admin interface the characters were probably replaced with html entities, encoded the character for your charset, or replaced with a plain ".

When working with Excel and exporting to CSV... Once again one needs to either disable "auto-correct" (and replace any characters excel has already "auto-corrected") or sanitize the exported document by replacing "smart quotes", "em-dashes", etc (after export). Then when you "import" the CSV to your database the characters will turn out as expected.

28 May 2013, 16:42

#3

lhungil

Totally Zenned

Join Date:: Feb 2012
Posts:: 1,818
Plugin Contributions:: 2

Re: Weird Characters - Need an SQL solution!

So to answer your question... You could either adjust the CSV files and re-import (which should fix all the products)... Or take a peek at Replacing Smart Quotes and Em-Dashes in Mysql. I have not used those instructions, but they appear plausible.

28 May 2013, 16:48

#4

rodg

Deceased

Join Date:: Jan 2007
Posts:: 6,263
Plugin Contributions:: 3

Re: Weird Characters - Need an SQL solution!

Feznizzle:

MY QUESTION:
Is there a way to automate the UPDATE process with an SQL command I can just drop in the SQL Query Executor?

It would take me all day to go thru Admin, click on each product and hit Preview, then Update. :no:

Any help would be greatly appreciated!
~Mike

Sorry Mike, but the answer is no.

lhungil said pretty much the same thing, possibly without realising it.

This is what I think is happening:

lhungil:

CKEditor and similiar tools have added detection to "replace" these characters. When you open the product / catalog page using the admin interface the characters were probably replaced with html entities, encoded the character for your charset, or replaced with a plain ".

In other words, it is the CKeditor that is doing the actual 'fixing', therefore MySQL and its 'update' command can't be of much help in this regard.

I've no doubt that there is a way to do this entirely with SQL (leaving CKeditor out of the picture) but it would probably be best/easiest to fix the data via other means (such as exporting to CSV, doing a search replace, then re-importing).

Cheers
Rod

28 May 2013, 18:51

#5

drbyte

Sensei

Join Date:: Jan 2004
Posts:: 63,506
Plugin Contributions:: 173

Re: Weird Characters - Need an SQL solution!

It really depends on where your bad data came from in the first place.

And the "fixing" that you say is happening might be triggered by cleaners in ckeditor, if that's what you've added as an editor, or it might be merely some built-in cleaners in Zen Cart that are cleaning it.

Of course, the best fix is to start with clean data. And it looks like your data is from a different character set ... and when you save it via your store admin is getting converted to the character set used by your store.
So, fixing the source data to be in the correct character set would stop the problem at the outset. If your data, which you're importing from some external source, is bad, then this isn't really even a Zen Cart issue.

30 May 2013, 14:32

#6

feznizzle

Totally Zenned

Join Date:: Apr 2010
Posts:: 900
Plugin Contributions:: 0

Re: Weird Characters - Need an SQL solution!

Thanks for the input you guys.

This data came direct from a manufacturer. It was like pulling teeth to get them to get it to me, so I'm kind of stuck with it. I've been using OpenOffice and TextWrangler for editing.

Looks like I'll go back to TextWrangler forums and try to find a way to detect/replace all special characters.

I totally forgot about ckeditor, that is indeed what I use. Man, I wish there was a way to simply trigger those filters on the entire db!

30 May 2013, 20:29

#7

solo_400

Zen Follower

Join Date:: Aug 2009
Posts:: 369
Plugin Contributions:: 0

Re: Weird Characters - Need an SQL solution!

UPDATE yourtable SET yourcolumn = replace(yourcolumn,"â€œ","\"");

?

30 May 2013, 21:10

#8

feznizzle

Totally Zenned

Join Date:: Apr 2010
Posts:: 900
Plugin Contributions:: 0

Re: Weird Characters - Need an SQL solution!

Hi Solo,

Thanks for the suggestion, but it's too late for me to try. I already went thru and did some simple find and replaces to take care of it.

If it happens again, I'll be sure to try it!

Thanks,
Mike

30 May 2013, 21:28

#9

allthingsidleroy

New Zenner

Join Date:: May 2013
Posts:: 38
Plugin Contributions:: 0

Re: Weird Characters - Need an SQL solution!

For anyone else that might have this issue, here is a good link about the wrong character encoding in a MySQL database:

detecting utf8 broken characters in mysql

30 May 2013, 22:24

#10

drbyte

Sensei

Join Date:: Jan 2004
Posts:: 63,506
Plugin Contributions:: 173

Re: Weird Characters - Need an SQL solution!

allthingsidLeroy:
For anyone else that might have this issue, here is a good link about the wrong character encoding in a MySQL database:

detecting utf8 broken characters in mysql

There's a whole lotta conflicting info in that post. And some of it will lead to problems. Some of it is a waste of time. Some of it is useful. It all depends on the original data and what's been done already to try mangling it.
My point: As with anything, use at your own risk. Be sure to make solid backups if you're going to go randomly trying those things!