We help IT Professionals succeed at work.

Getting rid of Carriage return when processing MySQL Data in PHP

swhitlow
swhitlow asked
on
978 Views
Last Modified: 2010-04-21
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?

Thanks!
Comment
Watch Question

David BeveridgeLinux Systems Admin
CERTIFIED EXPERT

Commented:
try
str_replace(\r, etc
str_replace(\n, etc
separately.

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

CERTIFIED EXPERT
Expert of the Year 2008
Top Expert 2008

Commented:
Try:
$data = preg_replace('/[\r\n]$/U','',$field);
CERTIFIED EXPERT
Expert of the Year 2008
Top Expert 2008

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

Author

Commented:
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

Author

Commented:
scrathcyboy - that just gives me an error that says:
Warning:  Wrong parameter count for preg_replace() in...

didn't work for me.
CERTIFIED EXPERT
Expert of the Year 2008
Top Expert 2008

Commented:
copy and paste this:
$new_string= preg_replace("/[^a-z0-9 ]/i", '', $string);
CERTIFIED EXPERT
Expert of the Year 2008
Top Expert 2008

Commented:
>> jquery flexigrid
Ahh, most likely you are feeding it a json string. Try:
$data = preg_replace('/[\r\n]/U','',$field);

Author

Commented:
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.
CERTIFIED EXPERT
Expert of the Year 2008
Top Expert 2008

Commented:
try escaping the back slashes:
$data = preg_replace('/[\\r\\n]/U','',$field);

Author

Commented:
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
 Dr.
','Indianapolis','IN','46217']},
{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.
CERTIFIED EXPERT
Expert of the Year 2008
Top Expert 2008

Commented:
can you post the php portion that is querying the db and encoding the data?

Author

Commented:
if($tblName == 'accounts'){
      $sql = "select accounts.id,
            accounts.name,
            accounts.date_modified,
            accounts.phone_office,
            accounts.shipping_address_street,
            accounts.shipping_address_city,
            accounts.shipping_address_state,
            accounts.shipping_address_postalcode
      FROM accounts $where $sort $limit";
}else {
      $sql = "select contacts.id, 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('===================================================================');
$GLOBALS['log']->info('SQL Statment: ' . $sql);
$GLOBALS['log']->info('===================================================================');


$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);

Author

Commented:
there is more but, this is the main part that is getting the $address string
CERTIFIED EXPERT
Expert of the Year 2008
Top Expert 2008

Commented:
instead of:
$address = trim($row['shipping_address_street']);

try:
$address = trim( stripslashes($row['shipping_address_street']));

Author

Commented:
nope. didn't work either. I can't believe that this is giving me this much trouble!  :(
CERTIFIED EXPERT
Expert of the Year 2008
Top Expert 2008

Commented:
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?

Author

Commented:
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?
CERTIFIED EXPERT
Expert of the Year 2008
Top Expert 2008
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks for the assistance! I appreciate it!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.