Export SQL Server Data to excel using PHP

Question

I am currently trying to automate monthly invoices of orders to my application with its backend using PHP and SQL Server. I referred to various other questions of people who were using MySQL as their backend but it still doesn't work and fails to create an excel file. I tried creating a CSV file and it worked but it fetched a single record.

My PHP code is as follows:

<?php
include 'db_connect.php';
$filename = "excelfilename";
$file_ending = "xls";
header("Content-Type: application/xls");
header("Content-Disposition: attachment; filename=$filename.xls");
header("Pragma: no-cache");
header("Expires: 0");
$sql = "SELECT * 
        FROM Trn_Order tro
            INNER JOIN Trn_Place_Order tpo ON tro.Order_Id = tpo.Order_Id
            INNER JOIN Mst_User_Login mul ON tro.User_Id = mul.User_Id
        WHERE tro.Status != 'Rejected' 
        AND tro.Added_On LIKE '2022-01%' 
        ORDER BY tro.Order_Id ASC";

$result = sqlsrv_query($conn, $sql);
$sep = '\t';
for ($i = 0;$i < sqlsrv_num_fields($result);$i++) {
    echo sqlsrv_get_field($result, $i) . '\t';
}
echo '\n';
while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_BOTH)) {
    $schema_insert = "";
    for ($j = 0;$j < sqlsrv_num_fields($result);$j++) {
        if (!isset($row[$j])) {
            $schema_insert = $schema_insert."NULL" . $sep;
        } elseif ($row[$j] != "") {
            $schema_insert  = $schema_insert. "$row[$j]" . $sep;
        } else {
            $schema_insert  = $schema_insert."" . $sep;
        }
    }
    $schema_insert = str_replace($sep . "$", "", $schema_insert);
    $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
    $schema_insert  = $schema_insert. "\t";
    echo trim($schema_insert);
    echo '\n';
}
?>

** Working Code **

<?php
date_default_timezone_set('Asia/Kolkata');
include 'db_connect.php';

$FileName = "MonthlyExcelReport.csv";
$fp = fopen('php://output', 'w');

$Fetch_Month = $_POST["Fetch_Month"];

$sql = "SELECT tro.Order FROM Trn_Order tro
    INNER JOIN Mst_User_Login mul
    ON tro.User_Id = mul.User_Id
    WHERE tro.Status != 'Rejected' AND tro.Added_On LIKE '2022-01%' ORDER BY tro.Order_Id ASC";
$resec = sqlsrv_query($conn, $sql);
 

while ($export= sqlsrv_fetch_array($resec, SQLSRV_FETCH_ASSOC)) 
{
    if (!isset($headings))
        {
            $headings = array_keys($export);
            fputcsv($fp, $headings, ',', '"');
        }
        fputcsv($fp, $export, ',', '"');
    }
    fclose($fp);
  
    header('Content-Type: application/csv');
    header('Content-Disposition: attachment; filename='.$FileName.'');
    header('Expires: 0');
    header('Cache-Control: must-revalidate');
    header('Pragma: public');
    header('Content-Length: ' . filesize("php://output"));
    readfile("php://output");
?>

Comments are not for extended discussion; this conversation has been moved to chat. — Samuel Liew
– Samuel Liew, Commented Jan 20, 2022 at 15:10

Sahil · Accepted Answer · 2022-01-20 15:32:36Z

Thanks to everybody who suggested a solution. I found a working solution with @ADyson guidance.

Due to clashing column names the excel had issues after being created, which was fixed by using alias or referencing the tablename.

The working code is as follows :

<?php
date_default_timezone_set('Asia/Kolkata');
include 'db_connect.php';

$FileName = "MonthlyExcelReport.csv";
$fp = fopen('php://output', 'w');

$Fetch_Month = $_POST["Fetch_Month"];

$sql = "SELECT tro.Order_Id,ml.User_Name FROM Trn_Order tro
, Mst_User_Login ml
WHERE tro.User_Id = ml.User_Id and tro.Status != 'Rejected' AND tro.Added_On LIKE '2022-01%' ORDER BY tro.Order_Id ASC";
$resec = sqlsrv_query($conn, $sql);

while ($export = sqlsrv_fetch_array($resec, SQLSRV_FETCH_ASSOC))
{
    if (!isset($headings))
    {
        $headings = array_keys($export);
        fputcsv($fp, $headings, ',', '"');
    }
    fputcsv($fp, $export, ',', '"');
}
fclose($fp);

header('Content-Type: application/csv');
header('Content-Disposition: attachment; filename=' . $FileName . '');
header('Expires: 0');
header('Cache-Control: must-revalidate');
header('Pragma: public');
header('Content-Length: ' . filesize("php://output"));
readfile("php://output");
?>

Collectives™ on Stack Overflow

Export SQL Server Data to excel using PHP

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related