Solved

Script to convert special characters to html entities?

Posted on 2011-09-28
6
465 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
  • 3
  • 2
6 Comments
 
LVL 109

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 109

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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

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…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

821 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