The Blog of Someone Who Builds Things on the Internet

There is this form that people enter a bunch of info and attached 1 to 7 files too. The files get uploaded to a web server directory and the form responses are stored in a mysql table. I wanted to create a script that would let me download all responses including the uploaded files, as having to SSH in to download the files, and then dump the info from mysql to a csv file was annoying.

$sql = "SELECT * FROM `responses`";

$result = mysql_query($sql) or die(mysql_error());

if (mysql_num_rows($result) > 0) :

    $files_to_include = [];

    // create your zip file
    $tmp_file = tempnam('.','');
    $zip = new ZipArchive();
    $zip->open($tmp_file, ZipArchive::CREATE);

    // create a temporary file for the csv
    $fd = fopen('php://temp/maxmemory:1048576', 'w');
    if (false === $fd) {
        die('Failed to create temporary file');
    }

    $headers = array('id','timestamp', 'fname', 'lname', 'email', 'phone','files');
    // write the data to csv
    fputcsv($fd, $headers);
    while($record = mysql_fetch_assoc($result)) {
        fputcsv($fd, $record);

        $fs = explode(",", $record['files']);
        foreach ($fs as $f) {
            if (!in_array(trim($f), $files_to_include))
                $files_to_include[] = trim($f);
        }

    }

    // return to the start of the stream
    rewind($fd);

    // add the in-memory file to the archive, giving a name
    $zip->addFromString('responses.csv', stream_get_contents($fd) );
    //close the file
    fclose($fd);

    foreach($files_to_include as $file){
        if (file_exists ( './file_uploads/'.$file )) {
        # download file
        $download_file = file_get_contents('./file_uploads/'.$file);

        #add it to the zip
        $zip->addFromString(basename('./file_uploads/'.$file),$download_file);
        }
    }

    // close the archive
    $zip->close();

    # send the file to the browser as a download
    $filename = 'Responses.zip';
    header('Content-disposition: attachment; filename='.$filename);
    header('Content-type: application/zip');
    readfile($tmp_file);
    unlink($tmp_file);
    die();

endif;

Yes, I know I'm using the old mysql extension for php here. It was an expansion to an old script. I'm too lazy to rewrite the whole thing with mysqli.

Also, you should note that the file names that get uploaded on this form are stored in a mysql column called files, which is a common separated list of the filenames.