Unserialize MYSQL Data And Export As CSV

Question

Ok I have MYSQL data and I can perfectly export it to CSV. Problem here is one of the columns have serialized data and when I export, it shows as serialized. I have tried unsterilized function of PHP but it doesn't seem to work.

Please find below code to export to CSV and column having serialized data is fine_type.

Thanks

Code For Export to CSV

<?php 
// Load the database configuration file 
include 'connect.php';
//$h=$_REQUEST['inspector_name'];
//$emp_numberd=$_REQUEST['datepicker'];


// Filter the excel data 
function filterData(&$str){ 
    $str = preg_replace("/\t/", "\\t", $str); 
    $str = preg_replace("/\r?\n/", "\\n", $str); 
    if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"'; 
} 
 
// Excel file name for download 
$fileName = "members-data_" . date('d-m-Y') . ".xls"; 
 
// Column names 
$fields = array('اسم المحل','.الرخصة','اسم المفتش','Fine Date','Fine Details','المستفيد الحقيقي'); 
 
// Display column names as first row 
$excelData = implode("\t", array_values($fields)) . "\n"; 
 
// Fetch records from database 
$query = $link->query("SELECT  * FROM fine_controls WHERE str_to_date(fine_date, '%d-%m-%Y') between str_to_date('01-11-2022', '%d-%m-%Y') and str_to_date('10-03-2023', '%d-%m-%Y') order by id desc"); 

if($query->num_rows > 0){ 
    // Output each row of the data 
    while($row = $query->fetch_assoc()){
        
        $status = ($row['statuse'] == 1)?'Active':'Inactive'; 
        $lineData = array($row['shopname'],$row['license'],$row['inspector_name'],$row['fine_date'],$row['fine_type'],$row['beneficiary']); 
        array_walk($lineData, 'filterData'); 
        $excelData .= implode("\t", array_values($lineData)) . "\n"; 
    } 
}else{ 
    $excelData .= 'No records found...'. "\n"; 
} 
 
// Headers for download 
header("Content-Type: application/vnd.ms-excel"); 
header("Content-Disposition: attachment; filename=\"$fileName\""); 
 print chr(255) . chr(254).mb_convert_encoding($excelData, 'UTF-16LE', 'UTF-8');
// Render excel data 
//echo $excelData; 
 
exit;

Changes I have already tried

if($query->num_rows > 0){ 
        // Output each row of the data 
        while($row = $query->fetch_assoc()){
 $data=unserialize($row['fine_type']);
            
            $status = ($row['statuse'] == 1)?'Active':'Inactive'; 
            $lineData = array($row['shopname'],$row['license'],$row['inspector_name'],$data,$row['fine_type'],$row['beneficiary']); 
            array_walk($lineData, 'filterData'); 
            $excelData .= implode("\t", array_values($lineData)) . "\n"; 
        } 
    }else{ 
        $excelData .= 'No records found...'. "\n"; 
    } 

Answer 1

As the serialized column $row['fine_type'] is an array of strings, you will have to do something to cope with that. I suggest creating a new line in the output for each occurance of the string describing the fine, like this.

This of course will depend upon the user requirements for this output.

       while($row = $query->fetch_assoc()){           
            $status = ($row['statuse'] == 1)?'Active':'Inactive';             
            
            // $row['fine_type'] is an serialized array of strings
            $data=unserialize($row['fine_type']);
            
            // so loop round it and create a line per occurance
            foreach($data as $fine) {
                $lineData = [ 
                              $row['shopname'], $row['license'], $row['inspector_name'], 
                              $fine,  $row['beneficiary']
                            ];
                array_walk($lineData, 'filterData'); 
                $excelData .= implode("\t", array_values($lineData)) . "\n"; 
            }
        }