fputcsv ignore newline from database

Question

So i am using Laravel to generate database query into csv like this:

public function downloadVoucher(Request $req)
    {
        header('Content-Type: text/csv; charset=utf-8');  
        header('Content-Disposition: attachment; filename=data.csv');  
        if ($req->start == "") {
            $req->start = "2000-01-01";
        } else if ($req->end == "") {
            $req->end = "3000-31-12";
        }
        $output = fopen("php://output", "w");
                fputcsv($output, array('No', 'Nama', 'Email', 'No Hp', 'Tanggal Redeem Voucher',
                'Kode Voucher','Progress Study','Quiz Score','Date Download Certificate','Download Certificate', 
                'Sudah Jualan Online?', 'Deskripsi Produk', 'Instagram Toko Online', 'Marketplace Toko Online', 
                'Website Toko Online'));  
        $data = VoucherBitX::select('quiz_score.score','quiz_score.status','voucher_bit_x.track_id',
                'voucher_bit_x.user_id','voucher_bit_x.redeem_at','voucher_bit_x.voucher_code','users.phone',
                'users.ref_id','users.email','users.name','certificate.published', 'users.is_online_selling', 
                'users.selling_desc', 'users.instagram_selling_acc', 'users.marketplace_acc', 'users.online_selling_website')
        ->where("voucher_bit_x.status",0)
        ->whereDate("voucher_bit_x.redeem_at", ">=", $req->start)
        ->whereDate("voucher_bit_x.redeem_at", "<=", $req->end)
        ->leftJoin('users','users.id','=','voucher_bit_x.user_id')
        ->leftJoin("certificate",function($join){

            $join->on("certificate.user_id","=","voucher_bit_x.user_id")
    
                ->on("certificate.track_id","=","voucher_bit_x.track_id");
    
        })
        ->leftJoin("quiz_score",function($join){

            $join->on("quiz_score.user_id","=","voucher_bit_x.user_id")
    
                ->on("quiz_score.track_id","=","voucher_bit_x.track_id");
    
        })
        ->orderBy("voucher_bit_x.redeem_at","DESC")
        ->get();
        $no = 0;
        foreach ($data as $key => $value) {
            $no = $no+1;
            $urlQr = "";
            $solve = Track::userProgress($value->user_id,$value->track_id);
            $solve .="%";
            if(!empty($value->status)){
                $track = Track::find($value->track_id);
                $urlQr = 'https://connect.xxxx.id/api/certificate/'.base64_encode(json_encode(["user_id"=>$value->user_id,"slug"=>$track->slug,"track_id"=>$track->id]));
            }
            $redeem_at = $value->redeem_at;
            fputcsv($output, array($no,$value->name,$value->email,$value->phone, $redeem_at,
            $value->voucher_code,$solve,isset($value->score)?$value->score:0,$value->published, $urlQr,
            ($value->is_online_selling == 1 ? 'Sudah' : 'Belum'), $value->selling_desc, 
            $value->instagram_selling_acc,$value->marketplace_acc, $value->online_selling_website));  
        }  
        fclose($output);  
    }

The thing is, when the data from one field is newline (enter) included, it turns into new row on csv which is a misleading data, it has to be one record (one the $value->selling_desc field). How do i overcome this issue? I followed this but it doesn't seem to be match with my problem. Any advice?

Answer 1

Actually line feeds are allowed by CSV standard while they are within quotes.

When reading the file, fgetcsv can handle them properly.

as a workaround, you can just remove newlines from the items (e.g. replacing them by spaces):

        ...
        $data = array($no,$value->name,$value->email,$value->phone, $redeem_at,
            $value->voucher_code,$solve,isset($value->score)?$value->score:0,$value->published, $urlQr,
            ($value->is_online_selling == 1 ? 'Sudah' : 'Belum'), $value->selling_desc, 
            $value->instagram_selling_acc,$value->marketplace_acc, $value->online_selling_website
        );

        foreach ($data as $k => $v) { 
            $data[$k] = preg_replace('/[\r\n]/', ' ', $v); // replace newlines by spaces
        }

        fputcsv($output, $data);  
        ...