How to strip carriage returns from text fields returned from mysql in PHP?

Question

I am using PHP to read a text field (DESCRIPTION) from MYSQL and prepare a JSON object. Modelled on JQUERY DataTables Plug-In example.

The DESCRIPTION field contains carriage returns and I believe this leads to invalid JSON. JSONLINT.com produces "Syntax error, unexpected TINVALID at line 35. Parsing failed".

http://ageara.com/exp3/server_processing_details_col.php should return valid JSON

I am attempting to use PHP TRIM() function to remove CR but not having much luck.

relevant PHP code follows ....

$rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());

$sQuery = "
    SELECT FOUND_ROWS()
";
$rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
$aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
$iFilteredTotal = $aResultFilterTotal[0];

$sQuery = "
    SELECT COUNT(TITLE)
    FROM   geometa_small
";
$rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
$aResultTotal = mysql_fetch_array($rResultTotal);
$iTotal = $aResultTotal[0];

/* write the JSON output */
$sOutput = '{';
$sOutput .= '"sEcho": '.intval($_GET['sEcho']).', ';
$sOutput .= '"iTotalRecords": '.$iTotal.', ';
$sOutput .= '"iTotalDisplayRecords": '.$iFilteredTotal.', ';
$sOutput .= '"aaData": [ ';
while ( $aRow = mysql_fetch_array( $rResult ) )
{ 
    $sOutput .= "[";  
    $sOutput .= '"<img src=\"details_open.png\">",';
    $sOutput .= '"'.str_replace('"', '\"', $aRow['TITLE']).'",';
    $sOutput .= '"'.str_replace('"', '\"', $aRow['DATA_CUSTODIAN_ORGANIZATION']).'",';
    $sOutput .= '"'.str_replace('"', '\"', $aRow['RECORD_TYPE']).'",';
    $sOutput .= '"'.str_replace('"', '\"', $aRow['RESOURCE_STATUS']).'",';
    $sOutput .= '"'.str_replace('"', '\"', $aRow['RESOURCE_STORAGE_LOCATION']).'",';
    $sOutput .= '"'.str_replace('"', '\"', $aRow['UNIQUE_METADATA_URL']).'",';
    $sOutput .= '"'.trim(str_replace('"', '\"', $aRow['DESCRIPTION']), "/r").'"';
    $sOutput .= "],";
}
$sOutput = substr_replace( $sOutput, "", -1 );

$sOutput .= '] }';
echo $sOutput;

function fnColumnToField( $i )
{
    /* Note that column 0 is the details column */
    if ( $i == 0 ||$i == 1 )
        return "TITLE";
    else if ( $i == 2 )
        return "DATA_CUSTODIAN_ORGANIZATION";
    else if ( $i == 3 )
        return "RECORD_TYPE";
    else if ( $i == 4 )
        return "RESOURCE_STATUS";
    else if ( $i == 5 )
        return "RESOURCE_STORAGE_LOCATION";
    else if ( $i == 6 )
        return "UNIQUE_METADATA_URL";
    else if ( $i == 7 )
        return "DESCRIPTION";

}

?>

Answer 1

TRIM() (both PHP and MySQL versions) will not remove all carriage returns, only whitespace before and after the entry. You can strip out the returns with either php or MySQL. Offhand I know how to do it in php:

str_replace(array("\r", "\n"), array('', ''), $mysqldata);

Hope this helps. Not 100% sure if that's the reason the JSON is not valid.

By the way you don't need to go nuts and write your own JSON if you have PHP 5.2. You can use json_encode()

Answer 2

Trim does remove \r and \n according to manual:

This function returns a string with whitespace stripped from the beginning and end of str. Without the second parameter, trim() will strip these characters:

* " " (ASCII 32 (0x20)), an ordinary space.
* "\t" (ASCII 9 (0x09)), a tab.
* "\n" (ASCII 10 (0x0A)), a new line (line feed).
* "\r" (ASCII 13 (0x0D)), a carriage return.
* "\0" (ASCII 0 (0x00)), the NUL-byte.
* "\x0B" (ASCII 11 (0x0B)), a vertical tab.

http://www.php.net/trim