MYSQL using PHP to remove spaces in fields

[jalama]

I am trying to remove spaces in the middle of a field called body in a mysql database using regular expressions.  I want to replace any instance of 3 or more spaces with one space.

 

Without any error showing when I run the page the code is not removing any spaces.  Even weirder is when I display (using echo or print) the field as it is in an Associative array it displays without of the spaces I want to remove.  The part where the space are removed upon display happens even for variables which are not extracted form arrays (I'm sure I missed the boat on something there).  I am currently using PEAR DB, though I converted the whole thing to the native mysql commands mysql_connect, etc... with the same results.  I know the MYSQL Select works as I can get it to work in phpMyAdmin and isolate the correct records.  I am happy to pull this off in php or using a single UPDATE statement in MYSQL but couldn't figure out how to get MYSQL to do what I wanted using replace() and regexp.

 

My version info is as follows:

php version 5.2.2

Apache 2.2.4

Pear DB 1.7.13

MYSQL 5.0.18

Linux SLED 10.0

Mozilla 2.0.0.8

 

Code:

//This is the code that doesn't work, ignore the while statement for now I'm not us'ng it till I get this to work on one record
$connection = DB::connect($dsn, true);
if (DB::isError($connection))
   trigger_error($connection->getMessage(), E_USER_ERROR); 
$connection ->setFetchMode(DB_FETCHMODE_ASSOC);
$result = $connection->query("SELECT * FROM `table` WHERE body regexp '[[:space:]]{3,}';");
//while ($result->fetchInto($row)) {
$row = $result->fetchRow();
  echo $row['body'] . '<br/>';
  $update = $connection->query("Update table set body = '" . preg_replace('[[:space:]]{3,}',' ',$row['body']) . "') WHERE archive_number = '".  row['archive_number'] . "';");

//}
$result->free();

[toplay]

When echoing spaces and viewing it on a web page, HTML will only show one space even when there's multiple spaces, unless you use things like " " or "< p r e >" tags (I had to put spaces for pre otherwise it will mess up this forum post).

 

Anyway, can the body column contain single quotes?  If so, then you should escape the value returned by the preg_replace(). See:

 

http://us3.php.net/manual/en/function.mysql-real-escape-string.php