By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,646 Members | 1,175 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,646 IT Pros & Developers. It's quick & easy.

Problem with hidden characters in MySQL longtext field.

P: 1
I have a problem with text being inserted into a mysql database containing hidden characters.
The text arrives there via multiple stages. Basically it is copied from Ms Word into an Ms Access rich text field (I'm pretty sure this is where the problem originates from). From here it is written to a .txt file and uploaded via ftp to the webserver where php inserts it into the mysql table.

The problem becomes noticable when it's displayed on a web page. There are too many blank lines. So I want to remove some of these blank lines from the MySQL field.

Looking at what's actually stored in the field I have something like this:

..snip..
floor

 

<strong>Condition</strong>
..snip..

Inspecting the above in Hex the characters between floor and <strong> are:
0d 0a 0d 0a 26 6e 62 73 70 3b 0d 0a 0d 0a

I output this field using php and nl2br(). Looking at the source of the output I have:

floor<br />
<br />
&nbsp;<br />
<br />
<strong>Condition</strong>

So it would appear that nl2br() is converting 0d to <br /> and 0a to an end of line feed.

I'm wanting to use MySQL Replace function to remove some of the extra returns but don't know what to use in the replace text part.
Basically I want something along the lines of:
$query = "UPDATE table SET field = REPLACE(field,0d 0a 0d 0a 26 6e 62 73 70 3b 0d 0a 0d 0a,'<br /><br />') ";

Obviously the above is wrong, but what should it be?
Feb 18 '10 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.