Solved

Script to convert special characters to html entities?

Posted on 2011-09-28
6
454 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

831 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