creating multiple csv files from php loop

Question

Im trying to create a loop that when executed it created multiple csv files and downloads them. This is my code:

session_start();
require '../connect.php'; //connect.php has connection info for my database
// and uses the variable $connect

$sqldept     = "SELECT department_name from department;";
$departments = mysqli_query($connect, $sqldept);

while ($department = mysqli_fetch_array($departments)) {
    $department = $department[0];
    header('Content-Type: text/csv; charset=utf-8');
    header("Content-Transfer-Encoding: UTF-8");
    header('Content-Disposition: attachment; filename=summary-' . $department . '.csv');
    header("Cache-Control: no-cache, no-store, must-revalidate"); // HTTP 1.1
    header("Pragma: no-cache"); // HTTP 1.0
    header("Expires: 0"); // Proxies

    $date  = date("Y-m-d", strtotime("-28 days" . date("Y-m-d")));
    $edate = date("Y-m-d");

    $startdate  = "(time.dateadded BETWEEN '$date' AND '$edate') AND";
    $department = " and department_name = '$department'";
    // create a file pointer connected to the output stream
    $output     = fopen('php://output', 'w');

    // output the column headings
    $sql2 = "SELECT time.id as timeid, time.staff_id, SUM(time.timein), COUNT(NULLIF(time.reasonforabsence,'')) AS count_reasonforabsence, GROUP_CONCAT(CONCAT(NULLIF(time.reasonforabsence,''),' ', date_format(time.dateadded, '%d-%m-%Y'),' ')) AS reasonforabsence, time.dateadded,  staff.id AS staffid, department.id AS departmentid, department.department_name, staff.staff_name, staff.department_id, SUM(staff.workhoursperday), staff.payrollnum FROM time, staff, department WHERE $startdate staff.id = time.staff_id AND staff.department_id = department.id $department $staffsearch GROUP BY staff.id ORDER BY `time`.`dateadded` ASC;";



    // output headers so that the file is downloaded rather than displayed
    fputcsv($output, array(
        'Payroll Number',
        'Name',
        'Department',
        'Hours Worked',
        'Days Absent',
        'Overtime',
        'Reasons for Absence'
    ));
    $rows = mysqli_query($connect, $sql2);

    while ($rowcsv = mysqli_fetch_assoc($rows)) {
        $reasonforabsence = $rowcsv['reasonforabsence'];
        //$reasonforabsence = explode( ',', $rowcsv['reasonforabsence'] );

        $overtime = 0;
        if (empty($rowcsv['SUM(time.timein)']) == true) {
            $rowcsv['SUM(time.timein)'] = 0;
        }
        ;
        if ($rowcsv['SUM(time.timein)'] > $rowcsv['SUM(staff.workhoursperday)']) {

            $overtime = $rowcsv['SUM(time.timein)'] - $rowcsv['SUM(staff.workhoursperday)'];
        }
        ;

        fputcsv($output, array(
            $rowcsv['payrollnum'],
            $rowcsv['staff_name'],
            $rowcsv['department_name'],
            $rowcsv['SUM(time.timein)'],
            $rowcsv['count_reasonforabsence'],
            $overtime,
            $reasonforabsence
        ));
    };
    readfile("php://output");
    fclose($output);
};

Currently the loop created 1 CSV with a new header and the department details below it like this

I want the loop to create a new CSV for each department but its just not working for me. Any help is appreciated. Thanks

Have you tried to change name output for each ? like output1, output2 ? — Fky
– Fky, Commented Sep 10, 2015 at 9:15
Do you mean the name of the file or the name of the ` $output` variable? — Anthony Broadbent
– Anthony Broadbent, Commented Sep 10, 2015 at 9:16
I kind of did that but instead of incrementing the numbers I appended the name of each department on the filename but unfortunately it only outputs 1 file with a random department name like: summary-A-Hull.csv — Anthony Broadbent
– Anthony Broadbent, Commented Sep 10, 2015 at 9:23
Okay so try to create zip file like suggest @Michael Kunst ;) — Fky
– Fky, Commented Sep 10, 2015 at 9:25

Community · Accepted Answer · 2017-05-23 11:46:52Z

3

Unfortunately you can't, 1 PHP Request results in one file, and there isn't really a way around this. You can, however, try to download them all as a ZIP file. Take a look at this question f.e.

edited May 23, 2017 at 11:46

CommunityBot

11 silver badge

answered Sep 10, 2015 at 9:16

Michael Kunst

2,98828 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Anthony Broadbent Over a year ago

The Zip files look like a good option. When you say that 1 request = 1 file. does that mean i would have to make multiple requests to get what I was i.e. multiple page loads (which isn't a good idea as the browser may think the page is failing)

Anthony Broadbent Over a year ago

or is it possible to request the data through an ajax loop for each dept. I know its a different route though.

Michael Kunst Over a year ago

An ajax loop for each dept. would definitely be an option. For the part 1 request = 1 file take a look at this question: stackoverflow.com/questions/3663127/php-multiple-file-download

Anthony Broadbent Over a year ago

I see. Ill give the ajax option a shot and see what happens then try the zip file. thanks for you help :D

evilReiko · Accepted Answer · 2020-12-21 11:07:34Z

0

The below are some workaround ideas, which might be useful in certain scenarios (and might be dangerous in other scenarios). Use under your own risk!

Workaround A: Loop by redirect

Output a single file normally
Do a redirect to same url that's creating the CSV file in step#1, but append a GET flag to that, like http://www.example.net/output_csv?i=1
Make sure to add a loop-breaker in step#1, like if($i==10) { exit; }

Workaround B: Loop by cronjob

Output a single file normally
Make 2nd file output be handled by a separate cronjob call.
Make sure to add a loop-breaker in step#1, like if($mycron==10) { exit; }

answered Dec 21, 2020 at 11:07

evilReiko

20.6k24 gold badges93 silver badges110 bronze badges

Comments

Ahmad Sharif · Accepted Answer · 2021-05-07 08:56:54Z

0

You can not do this by for loop.

However, You can make a php file which can do your purpose.

 <a onclick="getcsv()" href="php_file_location.php?table_name=test"> Download </a>

 <script>
                                            
     function getcsv() {
                 window.open(php_file_location);
             }
</script>

answered May 7, 2021 at 8:56

Ahmad Sharif

4,4536 gold badges40 silver badges51 bronze badges

Comments

Hashir Qadeer · Accepted Answer · 2022-12-29 15:32:40Z

0

I was in the same problem as mentioned. But in my case I was not trying to download multiple CSVs but I was uploading it to sFTP server. While creating the file instead of using

$output     = fopen('php://output', 'w');

I used

$output     = fopen($path_and_name, 'w');

where $path_and_name = $path_to_sftp_folder.'/'.$file_name; after the execution the correct file was uploaded to there respective folders correctly the way I wanted it to be. But yes the wrong file was also downloaded with same issue as sent above.

So if you are looking for uploading files on a server it can be done(even if they all have same name).

answered Dec 29, 2022 at 15:32

Hashir Qadeer

511 silver badge5 bronze badges

Collectives™ on Stack Overflow

creating multiple csv files from php loop

4 Answers 4

4 Comments

Workaround A: Loop by redirect

Workaround B: Loop by cronjob

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Workaround A: Loop by redirect

Workaround B: Loop by cronjob

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related