Jordan Vit Jordan Vit - 7 months ago 12
SQL Question

Select files from 3 columns in mysql and zip them

I have database table where I store file names. They are stored in 3 columns and in each columns can be stored multiple files separated with comma. Like this:

file_id
,
file_1
,
file_2
and
file_3
are column names

file_id | file_1 | file_2 | file_3
1 file_1.txt, file_1.1.txt.. | file_2.txt,file_2.2.txt | file_3.txt,file_3.3.txt


I hope is clear what is look like. I want with select to take all files names and then look in the folder and download in zip. So what I have until now is

$filePath = 'uploads/';
$sql = "SELECT * FROM uploads WHERE file_id = 1"; // just for testing
$result = $pdo->prepare($sql);
//$result->bindParam(":id", $id);
$result->execute();

$resArray = $result->fetch();

foreach(['file_1', 'file_2', 'file_3'] as $col){
if (file_exists($filePath . $resArray[$col])){
$valid_files[] = $resArray[$col];
}
}

if(count($valid_files > 0)){
$zip = new ZipArchive();
$zip_name = "zipfile.zip";
if($zip->open($zip_name, ZIPARCHIVE::CREATE)!==TRUE){
$error .= "* Sorry ZIP creation failed at this time";
}

foreach($valid_files as $res){

$zip->addFile($filePath.$res['file_1']);
$zip->addFile($filePath.$res['file_2']);
$zip->addFile($filePath.$res['file_3']);

}
//print_r($valid_files);
$zip->close();
// zip download
}


When I run the code I've got errors

Warning: Illegal string offset 'file_1' in ...
Warning: Illegal string offset 'file_2' in ...
Warning: Illegal string offset 'file_3' in ...


UPDATE:

print_r($res);
contain only 1 file from
file_1
column


file_1.txt


print_r($resArray);
contain all files from all columns.. and the strange things is that each file is 2 times in array


Array ( [file_1] => file_1.txt [0] => file_1.txt [file_2] => file_2.txt , [1] => file_2.txt , [file_3] => file_3.txt ,file_3.3.txt .jpg,file_3.3.txt,file_3.3.txt,file_3.3.txt, [2] => file_3.3.txt,file_3.3.txt,file_3.3.txt,file_3.3.txt,file_3.3.txt, )

Answer

Just change a bit this part to:

foreach(['file_1', 'file_2', 'file_3'] as $col){
    $items = explode(',', $resArray[$col]);
    foreach ($items as $item) {

        if (file_exists($filePath . trim($item))){
             $valid_files[] = $filePath . trim($item);
        }
    }
}

Then you'll be able to use it like here:

foreach($valid_files as $res){
   $zip->addFile($res);
}