A & E Luxury Apartments Bensenville Review Illinois

It's been a crazy month, with lots of drama all over the place. Hither at DigWP.com, nosotros had an episode where the site was all screwed upwards and not loading or merely partially loading, blank white pages, and the whole bit. During the process of keeping it together and trying to restore full functionality, numerous database imports and exports were performed under a diverseness of circumstance. During the rush, patently the about recent database backup file was somehow uncompressed outside of MySQL before final import.

Several days later, that decompression/unzipping basically converted every quotation mark, em dash, en dash, ellipses and other special characters into some really ugly-looking codes.

Some mutual examples of weird characters

What are they?

I recall what happened is that the restoration database that nosotros ended up using had been opened in a file/text editor. It's just a guess, and sort of irrelevant, but the text editor converted our UTF-8 characters into another character gear up, like ISO-8859-1. So after restoration, nosotros ended upwardly with hundreds of these weird characters in the database – quotes, hyphens, dashes, and ellipses were all converted to Klingon:

          “ = left quote = " ” = right quote = "  ‘ = left single quote = ' ’ = right unmarried quote = '  â€" = en dash = – â€" = em dash = —  • = hyphen = - … = ellipsis = …        

Identifying almost of these characters was relatively painless, but the en-nuance and em-nuance characters may be reversed (i.e., â€" = em nuance, and â€" = en dash). Testing the other character replacements in the database was easy, but discerning between instances of em & en dashes proved futile. So do your own testing and make skilful backups before making any mass changes. Hopefully someone can help us out with more of the specifics.

Clean 'em upwardly

Earlier making any changes to your database, make sure you have a good fill-in (or three). Then to clean up these weird characters from the WordPress database, use a program like phpMyAdmin to execute the post-obit queries.

Clean upwards post_content

          UPDATE wp_posts Prepare post_content = Replace(post_content, '“', '"'); UPDATE wp_posts Set post_content = REPLACE(post_content, '”', '"'); UPDATE wp_posts Set post_content = Supersede(post_content, '’', '''); UPDATE wp_posts SET post_content = Supervene upon(post_content, '‘', '''); UPDATE wp_posts SET post_content = Supersede(post_content, 'â€"', '–'); UPDATE wp_posts SET post_content = REPLACE(post_content, 'â€"', '—'); UPDATE wp_posts Fix post_content = Supervene upon(post_content, '•', '-'); UPDATE wp_posts Prepare post_content = Supersede(post_content, '…', '…');        

Clean up comment_content

          UPDATE wp_comments Set comment_content = Supplant(comment_content, '“', '"'); UPDATE wp_comments Prepare comment_content = Supplant(comment_content, '”', '"'); UPDATE wp_comments Prepare comment_content = REPLACE(comment_content, '’', '''); UPDATE wp_comments SET comment_content = Supercede(comment_content, '‘', '''); UPDATE wp_comments Ready comment_content = Supervene upon(comment_content, 'â€"', '–'); UPDATE wp_comments SET comment_content = Supervene upon(comment_content, 'â€"', '—'); UPDATE wp_comments Set up comment_content = REPLACE(comment_content, '•', '-'); UPDATE wp_comments Prepare comment_content = Supercede(comment_content, '…', '…');        

Other tables

While cleaning up the DigWP database, several other weird characters as well showed upwardly in diverse places, but they were very few in number. I besides noticed several instances of converted quotes, dashes, and hyphens scattered around in another tables, generally in the options table, cached deep within temporary rss_ data. And so I didn't bother with anything beyond the post_content and comment_content tables, but hands could accept done so by modifying the previous queries similar and then:

UPDATE [table_name] Prepare [col_name] = Supercede([col_name], '…', '…');

Just supercede [table_name] with whatever table you want to clean up, col_name with the column proper name, and and then replicate or edit the query with the proper character replacements.

Lesson learned

Accept-habitation bulletin: don't open your database in a text editor. But if you practice, execute these SQL queries for easy clean-up.

slagleungazintonat.blogspot.com

Source: https://digwp.com/2011/07/clean-up-weird-characters-in-database/

Share this post