Solved

Script to convert special characters to html entities?

Posted on 2011-09-28
6
486 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

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!

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
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.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

724 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