Problems when exporting from PHP to CSV

Question

I’m having trouble visualizing a CSV file. My intention is that I can use the Excel command "data to column" and stay row by row, but the file has skipped line impossible. The code was made based on another example right here posted by an Adm.

     date_default_timezone_set('America/Sao_Paulo');

ini_set('memory_limit', '12000M');
 require_once($_SERVER['DOCUMENT_ROOT']."/administrator/lib/connection.php");

//echo "<script type=\"text/javascript\"> window.open(\"http://www.tometoo.com.br/generateCSV2.php\", \"_blank\")</script>";

        $PDO = Database::Connect();

        $SQL = "SELECT idpost, idcomment, cm_id_author, pg_name, ps_date, cm_date, ps_message, cm_name_author, cm_message, pss_likes, pss_shares, pss_comments, cms_like, cms_reply 
        from fb_post
        INNER JOIN fb_pssummary ON pss_idpost = idpost
        INNER JOIN fb_page ON ps_idpage = idpage
        INNER JOIN fb_comment ON cm_idpost = idpost
        INNER JOIN fb_cmsummary ON cms_idcomment = idcomment 
        where ps_date between '2016-09-01 00:00' and '2016-09-01 23:59:59'
        ORDER BY ps_date ASC";
        
        $SQL = $PDO->prepare($SQL);

        $SQL->execute();

        $result = $SQL->fetchAll(PDO::FETCH_ASSOC);


        function array_para_csv(array &$array)
        {
           if (count($array) == 0) {
             return null;
           }
           ob_start();
           $df = fopen("php://output", 'w');
           fputcsv($df, array_keys(reset($array)));
           foreach ($array as $row) {
              fputcsv($df, $row);
           }
           fclose($df);
           return ob_get_clean();
        }

        function cabecalho_download_csv($filename) {
    // desabilitar cache
    $now = gmdate("D, d M Y H:i:s");
    header("Expires: Tue, 03 Jul 2001 06:00:00 GMT");
    header("Cache-Control: max-age=0, no-cache, must-revalidate, proxy-revalidate");
    header("Last-Modified: {$now} GMT");

    // forçar download  
    header("Content-Type: application/force-download");
    header("Content-Type: application/octet-stream");
    header("Content-Type: application/download");

    // disposição do texto / codificação
    header("Content-Disposition: attachment;filename={$filename}");
    header("Content-Transfer-Encoding: binary");
}


cabecalho_download_csv("setembro_1_1M_1_parte" . date("Y-m-d") . ".csv");
echo array_para_csv($result);

the output has been like this in excel 229151370439618_1206843959337016,1206843959337016_1206850609336351,1063115217101837,"2016-09-01 00:15:31","2016-09-01 00:27:42","POLICE VIOLENCE The center of Sà Paulo became a real war square tonight, from the time that Military Police, decided to disperse the demonstration§It is against the coup from the middle of it. The effect, was the dispersion of protesters to various corners and a real hunting§promoted by the police the basis of bombs and rubber bullets. According to information§Ãµes, a young woman is at this time in the Clan-nica Hospital with the strong possibility of having lost sight of an eye and a photograph had his work equipment broken and was arrested. At the time, not even the photograph lawyers were informed about the reason for the arrest§ Pictures by Tadeu Amaral

Coup #Impeachment","Marco Antonio Camelo","Capital letter defends bandits." ,418,76,263,4,0

please ignore facebook content that has nothing to do with my opinion. As you can see beyond the accentuation has the problem of lines.

Answer 1

A solution to avoid this type of problem is through a very well developed library PHP Excel:

1) After downloading the library PHP Excel

2) Just use it as follows from your code:

date_default_timezone_set('America/Sao_Paulo');
ini_set('display_errors', 1);
error_reporting(-1); //mostra todos os erros (-1 ou E_ALL)
ini_set('memory_limit', '12000M');
require_once($_SERVER['DOCUMENT_ROOT']."/administrator/lib/connection.php");
require_once($_SERVER['DOCUMENT_ROOT']."/administrator/lib/PHPEXcel/Writer/PHPExcel.php");
require_once($_SERVER['DOCUMENT_ROOT']."/administrator/lib/PHPEXcel/Writer/Excel2007.php");

$PDO = Database::Connect();

$SQL = "SELECT idpost, idcomment, cm_id_author, pg_name, ps_date, cm_date, ps_message, cm_name_author, cm_message, pss_likes, pss_shares, pss_comments, cms_like, cms_reply 
        from fb_post
        INNER JOIN fb_pssummary ON pss_idpost = idpost
        INNER JOIN fb_page ON ps_idpage = idpage
        INNER JOIN fb_comment ON cm_idpost = idpost
        INNER JOIN fb_cmsummary ON cms_idcomment = idcomment 
        where ps_date between :initial and :final
        ORDER BY ps_date ASC";

$SQL = $PDO->prepare($SQL);
$SQL->execute([':initial'=>'2016-09-01 00:00', ':final'=>'2016-09-01 23:59:59']);

$result = $SQL->fetchAll(PDO::FETCH_ASSOC);

exportExcel("nome_do_arquivo", $result);

function exportExcel($filename, $result)
{ 
    $result = addHeader($result);
    $export = new PHPExcel();
    $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
    $cacheSettings = array(' memoryCacheSize ' => '8MB');
    PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

    $export->setActiveSheetIndex(0)
            ->fromArray($result, null, 'A1');

    $xmlWriter = new PHPExcel_Writer_Excel2007($export);

    header("Pragma: protected"); // required
    header("Expires: 0");
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Cache-Control: public", false); // required for certain browsers
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8');
    header("Content-Disposition: attachment;filename='{$filename}.xlsx'");
    header("Content-Transfer-Encoding: binary");
    $xmlWriter->save("php://output");
    exit();
}

function addHeader($value)
{
    $return = null;
    if (is_array($value)) {
        $header = array_keys($value[0]);
        $return[0] = $header;
        foreach ($value as $key => $row) {
            foreach ($row as $columnKey => $column) {
                $currentfindedKey = array_search($columnKey, $header);
                $return[$key][$currentfindedKey] = $column;
            }
        }
        array_unshift($return, $header);
    }
   return $return;
}