PHP array with array (2d array?) to csv

Question

I'm trying to create a CSV from an array with some data and another array in it.

Code:

public function exportCSV()
{
    $user = Auth::user();
    $company = $user->company;
    $dealers = $company->dealers;

    $formatted_dealers = [];

    foreach ($dealers as $dealer) {
        if($dealer->is_deleted == 0){
          array_push($formatted_dealers, $dealer);
        }
    }

    if(count($formatted_dealers) > 0){
        $csvData = array('name,phone,email,is_active,streetname,number,zip,city,special_id,products');
        foreach($formatted_dealers as $dealer){
            $products = [];
            foreach ($dealer->products as $product) {
              array_push($products, $product->slug);
            }
            $products = json_encode($products);
            $csvData[] = $dealer->name . ',' . $dealer->phone . ',' . $dealer->email . ',' . $dealer->is_active . ',' . $dealer->address->streetname . ',' . $dealer->address->number . ',' . $dealer->address->zip . ',' . $dealer->address->city . ',' . $dealer->special_id . ',' . $products;
        }

        $new_time = date('d-m-Y h:i:s', strtotime('+2 hours'));
        $filename = $new_time . '.csv';
        $file_path = base_path() . '/' . $filename;
        $file = fopen($file_path,'w+');
        foreach ($csvData as $exp_data){
          fputcsv($file, explode(',', $exp_data));
        }
        fclose($file);

        $headers = ['Content-Type' => 'application/csv'];

        return response()->download($file_path, $filename, $headers)->deleteFileAfterSend(true);
    } else {
        return redirect()->route('dealers.index', ['export' => 'error']);
    }
}

So, I have some data, like the dealers name, phone etc. and an array of products that the dealer has. My line of thought was to json_encode the products array and then append to the string that is being pushed to the csvData array. But the result I'm getting when doing this is:

name,phone,email,is_active,streetname,number,zip,city,special_id,products
"Some name",11111111,example@example.com,1,xxxx,x,xxxx,xxxxxxxx,96548,"[""nBuGbW""","""qP3DAF""]"
"Another name",22222222,anoter@example.com,0,xxxxxxxx,x,xxxx,xxxxxx,,"[""nBuGbW""","""IRTQBN""]"

the products json array has way too many " and I can't seem to figure out why. Any ideas?

In my head it should be something like:

name,phone,email,is_active,streetname,number,zip,city,special_id,products
"Some name",11111111,example@example.com,1,xxxx,x,xxxx,xxxxxxxx,96548,["nBuGbW","qP3DAF"]"
"Another name",22222222,anoter@example.com,0,xxxxxxxx,x,xxxx,xxxxxx,,["nBuGbW","IRTQBN"]"

Answer 1

This should be a comment but its a bit long (the problem you asked about, as jeroen says, you are double encoding the CSV data).

My line of thought was to json_encode the products array and then append to the string

So you have the lassitude to invent your own encoding system - which implies that you control the code which both encodes and decodes data. But why are you writing data to a flatfile?

You are heading for a whole world of pain if you are using flat files as a database. There is a reason that programmers have been using things like Oracle, MySQL, MongoDB, Redis.... for at least the last 40 years.

If, on the other hand, you are trying to implement a way of publishing data to different instances of an application (which does use a proper DB) then there are many, much better ways of doing this.

If you are publishing the data for someone else to consume then you shouldn't be inventing your encoding.

This looks like the XY problem.

Answer 2

The problem is that you are manually constructing - invalid - csv:

$csvData[] = $dealer->name . ',' . $dealer->phone . ',' . $dealer->email . ',' 
    . $dealer->is_active . ',' . $dealer->address->streetname . ',' . $dealer->address->number . ',' 
    . $dealer->address->zip . ',' . $dealer->address->city . ','
    . $dealer->special_id . ',' . $products;

Your $products json will contain commas as well, so when you explode() on the commas, your data is all messed up and you have invalidated your json:

foreach ($csvData as $exp_data) {
    // Here you break your json data
    fputcsv($file, explode(',', $exp_data));
}

You should use a multi-dimensional array instead, making exploding unnecessary:

$csvData[] = [
    $dealer->name,
    ...
    $products,
];

and:

foreach ($csvData as $exp_data) {
    fputcsv($file, $exp_data);
}

Note that making it valid csv, will cause escaping of for example the double-quotes. So it will still look weird in a way :-)