Solved

Script to convert special characters to html entities?

Posted on 2011-09-28
6
479 Views
Last Modified: 2012-06-21
Hi All,

Historically I've added content to my database without converting special characters to html entities. How would I go about converting these special characters to html entities thoughout the whole database? Would I need to write a script that loops through each table and then each row to replace the characters in question or is there an easier way.

If a script is required please could you show an example of how to loop between the tables and rows because the loop will need to use and index or reference of some sort to handle the tables without using there saved names.  

Thank you
0
Comment
Question by:Mulith
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 36716715
I would not do this if I were you.  Instead, use htmlentities() in the display layer.  There are many reasons why you do not want to mung your data base.  One of them is that the htmlentities() function increases the length of the strings.  Thus there is a risk that you will cut the data off when you try to put it back into your tables.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 36716783
But if you are unconvinced and want to mung your data base, here are the general ideas you will need to implement.

SHOW TABLES to get the list of tables, then an iterator to access each table
SHOW COLUMNS FROM the list of tables to get "Field" and "Type" which are the names and definitions of the columns, then an iterator to access each column
SELECT the data in the column
Apply htmlentities() to the data
Compare the strlen() value of the new data to the length given in the "Type"
Issue a warning message if the new data is longer than the length of the column.

Once you are satisfied that you will not cut off your strings (or that the cutoffs do not matter) you can add the UPDATE statements to replace the contents of the data base tables.  You probably want to make a back up, because htmlentities() cannot be run twice on the same string.  You probably want to LOCK TABLES during the pervasive update.
0
 
LVL 1

Author Comment

by:Mulith
ID: 36717162
The problem ray is that when I try moving the content via mysql dump in SSH to another server I get a question mark where speacial characters used to appear.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:nemws1
ID: 36717693
That shouldn't be happening (MySQL is pretty smart about handing all sorts of types of data).

Sounds like a character encoding mismatch.  Ie.  You're table is using UTF-8 characters, but you're dumping out your data as LATIN-1 (or something similar).

When you look at the dump file (add --skip-extended-insert to your mysqldump command so you can see the rows more easily), are the characters already messed up, or do they look correct in the dump?
0
 
LVL 1

Accepted Solution

by:
Mulith earned 0 total points
ID: 36718208
Managed to sort it by adding --default-character-set=latin1 as advised on http://www.orthogonalthought.com/blog/index.php/2007/05/mysql-database-migration-and-special-characters/ 
0
 
LVL 1

Author Closing Comment

by:Mulith
ID: 36902259
Did a bit of reading and found this solution
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses how to create an extensible mechanism for linked drop downs.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question