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>Conditi on</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 />
<br />
<br />
<strong>Conditi on</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,0 d 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?
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>Conditi on</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 />
<br />
<br />
<strong>Conditi on</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,0 d 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?