Strip line breaks, quotes, HTML out of SQL query result for CKEditor field contents (for CSV output)

Question

I have a very simple "PHP allowed" EE template that queries the EE db and generates CSV reports from it. I need to add to one of these reports the contents of one particular channel field, which happens to be a CKEditor field. The double quotes and line breaks within the contents of this field in some rows are wreaking havoc with my CSV output, and other HTML doesn't actually break the CSV but is useless at best in the context of readable CSV output.

So what's the most straightforward way to strip out line breaks and doublequotes, and maybe also all HTML, without having to install any new add-ons, which to my eternal chagrin I cannot do on this site?

My full template code is below... you obviously won't be able to run it because my channel field definitions are unique, but it shows you the simplicity of what I am doing, so I'm hoping for a likewise simple solution to my HTML stripping issue.

<?php
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=report.csv');
?>"Title","URL title","First name","Last name","Notes"
<?

    {exp:query sql="SELECT title, url_title, field_id_128 AS 'FirstName', field_id_129 AS 'LastName', field_id_132 AS 'Notes' FROM (exp2_channel_data cd JOIN exp2_channel_titles ct ON ct.entry_id = cd.entry_id) WHERE cd.site_id = 3 and cd.channel_id = 19 GROUP BY cd.entry_id ORDER BY field_id_129"}
        ?>"{title}","{url_title}","{FirstName}","{LastName}","{Notes}"<?
        print "\n";
    {/exp:query}
?>

Answer 1

I see that you can't use add-ons. I also realized that my previous answer would not work because of parse order issues between EE tags and PHP. Here is an updated, purely PHP answer for you. I've tested it on a template with PHP turned on (doesn't matter if it's input or output).

Credit definitely goes to this stack answer: https://stackoverflow.com/questions/3933668/convert-array-into-csv

I simply modified it to fit your needs. You may also have to modify it for whatever your final CSV format requirements are. I wanted to give you an answer using the fputcsv() PHP function, but it's broken and will only enclose values if certain conditions are met, and you can't force it to enclose every field.

<?php

// Set headers
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=report.csv');

/**
  * Formats a line (passed as a fields  array) as CSV and returns the CSV as a string.
  * Adapted from http://us3.php.net/manual/en/function.fputcsv.php#87120
  */
function arrayToCsv( array &$fields, $delimiter = ';', $enclosure = '"', $encloseAll = false, $nullToMysqlNull = false ) {
    $delimiter_esc = preg_quote($delimiter, '/');
    $enclosure_esc = preg_quote($enclosure, '/');

    $output = array();
    foreach ( $fields as $field ) {
        if ($field === null && $nullToMysqlNull) {
            $output[] = 'NULL';
            continue;
        }

        // Enclose fields containing $delimiter, $enclosure or whitespace
        // Also strip html tags
        if ( $encloseAll || preg_match( "/(?:${delimiter_esc}|${enclosure_esc}|\s)/", $field ) ) {
            $output[] = $enclosure . strip_tags(str_replace($enclosure, $enclosure . $enclosure, $field)) . $enclosure;
        }
        else {
            $output[] = $field;
        }
    }

    // Return with new line ending
    return implode( $delimiter, $output ) . "\r\n";
}

// Use the database class to get the query
$f = ee()->db   ->select("title, url_title")
                ->from("exp_channel_titles")
                ->get()->result_array();


// Open output to browser
$fp = fopen('php://output', 'w'); // this file actual writes to php output

// Headers
$out = '"Title","Url Title"' . "\r\n";

// Autobots, write, transform, and roll out
foreach($f as $row)
{
    $out .= arrayToCsv($row, ',', '"', TRUE);
}

fwrite($fp, $out);
fclose($fp);

?>

Answer 2

Would a plugin like Streeng work out for you? https://github.com/caddis/streeng

It allows you to find/replace text. It can encode/decode HTML character codes, selectively strip HTML, etc.

As jchrono-battle said, I'd be surprised if there wasn't a CSV exporter already available.