PHP exporting several excel files

I’m trying to export several tables from a database and creating a excel file for each table using PHP. The loop however is “bugged”. Instead of creating a new file for each loop it just puts all data into the same file.

The code:

  foreach ($pdo->query("SHOW TABLES;") as $allTables) {
    $table = $allTables[0];

    $allDataStmt = $pdo->prepare("SELECT * FROM $table;");
    $allDataStmt->execute();

    $fieldcount = $allDataStmt->columnCount();

    $col_title = "";
    $data = "";
    for ($i = 0; $i < $fieldcount; $i++) {
      $col = $allDataStmt->getColumnMeta($i);
      $column = $col['name'];
      $col_title .= '<Cell ss:StyleID="2"><Data ss:Type="String">' . $column . '</Data></Cell>';
    }

    $col_title = '<Row>' . $col_title . '</Row>';

    while ($row = $allDataStmt->fetch(PDO::FETCH_NUM)) {
      $line = '';
      foreach ($row as $value) {
        if ((!isset($value)) or ($value == "")) {
          $value = '<Cell ss:StyleID="1"><Data ss:Type="String"></Data></Cell>t';
        } else {
          $value = str_replace('"', '', $value);
          $value = '<Cell ss:StyleID="1"><Data ss:Type="String">' . $value . '</Data></Cell>t';
        }
        $line .= $value;
      }
      $data .= trim("<Row>" . $line . "</Row>") . "n";
    }

    $data = str_replace("r", "", $data);

    header("Content-Type: application/vnd.ms-excel;");
    header("Content-Disposition: attachment; filename=" . $table . ".xls");
    header("Pragma: no-cache");
    header("Expires: 0");
  }
}

If I kill the loop after the first iteration it exports the data correctly from ONE table. If I let the loop run it just loads the same file with the data and the file becomes corrupted. I can’t even open it in Excel.

What am I missing? Been stuck with this for hours.

Thank you!

Answer

What you are attempting with making multiple different attachments in the same HTTP request is not possible. The HTTP protocol does not have support for downloading multiple files. The most common workaround is to put the files in a zip archive for the client to download, so something like this:

$zip = new ZipArchive;
$zip_name = "excel_tables.zip";

if ($zip->open($zip_name, ZipArchive::CREATE|ZipArchive::OVERWRITE) === TRUE) {
    foreach ($pdo->query("SHOW TABLES;") as $allTables) {
        // (Your logic to build Excel file)

        $file_content = $col_title . "n" . $data;

        $file_name = $table . ".xls";
        file_put_contents($file_name, $file_content);

        // Add file to the zip file
        $zip->addFile($file_name);
    }

    $zip->close();
    header('Content-Type: application/zip');
    header('Content-Disposition: attachment; filename=' . $zip_name);
    header("Content-Length: " . filesize($zip_name));
    header("Pragma: no-cache"); 
    header("Expires: 0"); 
    ob_clean();
    flush();
    readfile(__DIR__ . "/" . $zip_name);
}