Why Export to CSV Data only showing in one column

Question

We have many dynamic tables being generated in the process of our application. so we are trying to export the data from those dynamically generated table column data to .csv file.

$secondsql = "SELECT * FROM ".$reslt->dname." WHERE userid=:userid AND eventid=:eventid";
    
$querysec = $dbh -> prepare($secondsql); 
$querysec-> bindParam(':userid', $usrid, PDO::PARAM_STR);  
$querysec-> bindParam(':eventid', $exporteventid, PDO::PARAM_STR);                                     
$querysec-> execute();
$ressec = $querysec -> fetchAll(PDO::FETCH_OBJ);

This is how we are fetching the data and converting it to json and getting key value pairs and passing onto fputcsv $jsonString = json_encode($ressec);

        $jsonDecoded = json_decode($jsonString, true);
        $keys = array_keys(json_decode($jsonString, true));
        $csvHeader=array();
        $csvData=array();
        
        foreach($jsonDecoded as $key => $val) {
           
            if (!$val) {
            }else{
                if(!$csvHeader){
                    $csvHeader = array_keys($val);
                }
           }
        }
        
        $kcnt = count($csvHeader);
        
        $tempstring = "";
        
        foreach($jsonDecoded as $val2) {
            $arrvals = array_values($val2);
            $resultStringValues = implode(",", $arrvals);
            
            $temparray=array();
            array_push($temparray,$resultStringValues);
          
            array_push($csvData,$temparray);
        }
        $tempstring = rtrim($tempstring, ",");
        $delimiter = ","; 
        $filename = $reslt->eventname. date('Y-m-d') . ".csv"; 
        $f = fopen('php://memory', 'w'); 
    
        fputcsv($f, $csvHeader, $delimiter);
       
        foreach($csvData as $fields){
            fputcsv($f,$fields,$delimiter);
        }
       
        fseek($f, 0); 
        header('Content-Type: text/csv'); 
        header('Content-Disposition: attachment; filename="' . $filename . '";');
        fpassthru($f);

The result for this is enter image description here

why it is not splitting the data in the appropriate columns? my $csvHeader and $csvData array outputs

Array
(
    [0] => id
    [1] => eventid
    [2] => userid
    [3] => fullname
    [4] => email
    [5] => mobile
    [6] => reg_date
)
1
Array
(
    [0] => Array
        (
            [0] => 21,22,19,khasim,khasim@khasim.com,8786468768,2022-05-30 00:00:00
        )

    [1] => Array
        (
            [0] => 22,22,19,tanveer,khasim@ism.com,2343434,2022-05-30 01:00:00
        )

    [2] => Array
        (
            [0] => 23,22,19,tan,tan@gmail.com,24343,2022-05-30 00:00:00
        )

    [3] => Array
        (
            [0] => 24,22,19,raju,raj@kaju.com,234324324,2022-05-30 01:00:00
        )

    [4] => Array
        (
            [0] => 26,22,19,tan,tan@gmail.com,24343,2022-05-30 00:00:00
        )

    [5] => Array
        (
            [0] => 27,22,19,raju,raj@kaju.com,234324324,2022-05-30 01:00:00
        )

    [6] => Array
        (
            [0] => 29,22,19,raju,raj@kaju.com,234324324,2022-05-30 01:00:00
        )

    [7] => Array
        (
            [0] => 30,22,19,raju,raj@kaju.com,234324324,2022-05-30 01:00:00
        )

    [8] => Array
        (
            [0] => 31,22,19,tan,tan@gmail.com,24343,2022-05-30 00:00:00
        )

    [9] => Array
        (
            [0] => 32,22,19,raju,raj@kaju.com,234324324,2022-05-30 01:00:00
        )

    [10] => Array
        (
            [0] => 33,22,19,raju,raj@kaju.com,234324324,2022-05-30 01:00:00
        )

    [11] => Array
        (
            [0] => 34,22,19,ttttt,tttt@ttt.com,234324324,2022-05-30 01:00:00
        )

    [12] => Array
        (
            [0] => 35,22,19,mohan,mohan@mohan.com,123456789,2022-05-30 01:00:00
        )

    [13] => Array
        (
            [0] => 36,22,19,seet,seeet@mohan.com,123456789,2022-05-30 01:00:00
        )

    [14] => Array
        (
            [0] => 37,22,19,bhadrachalam,bhadrachalam@gmail.com,865956854,2022-06-15 07:17:49
        )

)

I messed up the array and done all unnecessary conversions, as no one pointed at what I did wrong I managed to solve so pasting the solution for others

$secondsql = "SELECT * FROM ".$reslt->dname." WHERE userid=:userid AND eventid=:eventid";
    
    $querysec = $dbh -> prepare($secondsql); 
    $querysec-> bindParam(':userid', $usrid, PDO::PARAM_STR);  
    $querysec-> bindParam(':eventid', $exporteventid, PDO::PARAM_STR);                                     
    $querysec-> execute();
    //$ressec = $querysec -> fetchAll(PDO::FETCH_OBJ);
    $table_fields = array_keys($querysec->fetch(PDO::FETCH_ASSOC));
    $ressec = $querysec -> fetchAll(PDO::FETCH_ASSOC);
    
    
        $delimiter = ","; 
            $filename = $reslt->eventname. date('Y-m-d') . ".csv"; 
            $f = fopen('php://memory', 'w'); 
    
        fputcsv($f, $table_fields, $delimiter);
       
        foreach($ressec as $fields){
                        
            fputcsv($f,$fields,$delimiter);
             
        }
        fseek($f, 0); 
        header('Content-Type: text/csv'); 
        header('Content-Disposition: attachment; filename="' . $filename . '";'); 
        fpassthru($f);

are my array outputs correct i am not sure about it can you please check i have updated it now — user1675571, Jun 17 '22 at 08:46
The fact that you are imploding your data yourself, is absolutely wrong to begin with. You should pass an _array_ of fields to `fputcsv`, not a _single_ string value wrapped into an array. — CBroe, Jun 17 '22 at 08:55
where i should make changes can you please point me to the wrong array so that i can change it — user1675571, Jun 17 '22 at 08:59
Well that would be easier to say, if we had an actual example of your data. But you should probably just loop over `$jsonDecoded` — CBroe, Jun 17 '22 at 09:04
_"This is how we are fetching the data and converting it to json"_ - it looks like the only purpose of encoding this as JSON, and then decoding it again, is to convert from object to array ...? If you don't want objects returned, then don't instruct PDO to fetch objects in the first place! — CBroe, Jun 17 '22 at 09:09

score 0 · Answer 1 · answered Jun 17 '22 at 07:59

0

Try replace data seperator from "," to ";", because You using Microsoft excel. Or open same file in libre office calc.

answered Jun 17 '22 at 07:59

Vykintas

633
5
16

Why Export to CSV Data only showing in one column

1 Answers1