Link to home
Start Free TrialLog in
Avatar of swhitlow

asked on

Getting rid of Carriage return when processing MySQL Data in PHP

I have a field in a database that has a carriage return. It is an address (although it could be anything) and it is the following two lines:
8923 Chowsnik Dr.

If you take the two lines above, you will also take the empty line that it has below the address as well. This is what is in the database.

Now, in php, I need to eliminate this extra carriage return white space. I have tried trim, str_replace(\n\r, etc...

But, nothing seems to work. I am using the return along with a jquery flexigrid control and this is what is not liking that extra carriage return.

So, is there anything I can do to get rid of this dead space at all?

Avatar of David Beveridge
David Beveridge
Flag of Australia image

str_replace(\r, etc
str_replace(\n, etc

In unix/linux there is just a /n
in mac os there is just /r
win windows there is /r/n

your replace had /n/r

$data = preg_replace('/[\r\n]$/U','',$field);
In case of multiple newlines use this instead:
$data = preg_replace('/[\r\n]+$/U','',$field);
Avatar of swhitlow


I tried both suggestion (actually all three) none of them worked.

Anything else I can try?  This is driving me nuts!
since you only want letters and numbers, use this --
$new_string= preg_replace("[A-Za-z0-9]", $string);

$new_string becomes the filtered version of what you want, it eliminates all but A-Z, a-z, and 0-9
scrathcyboy - that just gives me an error that says:
Warning:  Wrong parameter count for preg_replace() in...

didn't work for me.
copy and paste this:
$new_string= preg_replace("/[^a-z0-9 ]/i", '', $string);
>> jquery flexigrid
Ahh, most likely you are feeding it a json string. Try:
$data = preg_replace('/[\r\n]/U','',$field);
Nope. that didn't work either. Also, note the details above. It is php that is creating the javascript. So, it is returning a json post formatted string. Don't know if that matters or not, but I thought I would mention it.
try escaping the back slashes:
$data = preg_replace('/[\\r\\n]/U','',$field);
nope. didn't work either. Not sure if this will help but, here is the data I am getting back from Firebug:

rows: [

{id:'aa505f50-dade-6272-2717-47f2b333320e',cell:['aa505f50-dade-6272-2717-47f2b333320e', 'Add to Route','2 Big Consolidation Corp 418993','(754
) 301-4377','9 IBM Path','St. Petersburg','CA','37000']},
{id:'d89e5bd7-20d4-8831-65dc-47f2b3d721b2',cell:['d89e5bd7-20d4-8831-65dc-47f2b3d721b2', 'Add to Route','2 Tall Stores 376377','(236) 854-7906','8923 Chessie
{id:'c0c5e69c-59fa-f341-1aa8-47f2b3a9b06c',cell:['c0c5e69c-59fa-f341-1aa8-47f2b3a9b06c', 'Add to Route','A.G. Parr PLC 828674','(743) 194-6946','345 Sugar
 Blvd.','St. Petersburg','CA','39570']},

Notice the extra carriage return on the line that says "8923 Chessie Dr." then it drops down one line and has the "Indianapolis".

That is where the problem is. If I go into the database and get rid of that one line, It solves the problem. But, I obviously have to code for the user who might input the one line in there. Even if by accident.
can you post the php portion that is querying the db and encoding the data?
if($tblName == 'accounts'){
      $sql = "select,
      FROM accounts $where $sort $limit";
}else {
      $sql = "select, contacts.last_name, contacts.first_name,
            CONCAT(IFNULL(contacts.salutation,''), ' ',  IFNULL(contacts.first_name,''), ' ', IFNULL(contacts.last_name,'')) as name,
            contacts.phone_work as phone_office,
            contacts.primary_address_street as shipping_address_street,
            contacts.primary_address_city as shipping_address_city,
            contacts.primary_address_state as shipping_address_state,
            contacts.primary_address_postalcode as shipping_address_postalcode
      FROM contacts $where $sort $limit";

$GLOBALS['log']->info('SQL Statment: ' . $sql);

$result = runSQL($sql);

if($tblName == 'accounts') {
      $total = countRec('id','accounts',$where);
} else {
      $total = countRec('id','contacts',$where);

header("Expires: Mon, 26 Jul 1997 05:00:00 GMT" );
header("Last-Modified: " . gmdate( "D, d M Y H:i:s" ) . "GMT" );
header("Cache-Control: no-cache, must-revalidate" );
header("Pragma: no-cache" );
header("Content-type: text/x-json");
$json = "";
$json .= "{\n";
$json .= "page: $page,\n";
$json .= "total: $total,\n";
$json .= "rows: [";
$rc = false;

if($_REQUEST['type'] == 'samples'){
      $type = 'samples';

while ($row = mysql_fetch_array($result)) {
if ($rc) $json .= ",";
      //str_replace("\n", "", $string)
      $address = trim($row['shipping_address_street']);

      // $address = trim(addslashes($row['shipping_address_street'])) . ' ' .
      //                         trim(addslashes($row['shipping_address_city'])) . ', ' .  
      //                         trim(addslashes($row['shipping_address_state'])) . ' ' .
      //                         trim(addslashes($row['shipping_address_postalcode']));
      //$address = str_replace("\n", "", $address);
      //$address = str_replace("\r", "", $address);
      //$address = str_replace("/U", "", $address);
      $address = preg_replace('/[\\r\\n]/U','',$address);
there is more but, this is the main part that is getting the $address string
instead of:
$address = trim($row['shipping_address_street']);

$address = trim( stripslashes($row['shipping_address_street']));
nope. didn't work either. I can't believe that this is giving me this much trouble!  :(
If you echo the address immediately after you trim() it, does the output still reflect the newline character?Could something else be appending it later on in the code?
just so everyone knows here - I had to do a json_encode($value) and change the code above so that instead of looping through the records and adding the values to concatenate the string, it looped through the values and added the string to an array. I then used json_encode to properly encode it and it now works perfectly!

Thanks everyone for their suggestions!

Not sure what to do about the points now....what is the appropriate thing to do here?
Avatar of hielo
Flag of Wallis and Futuna image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for the assistance! I appreciate it!