need to add a the option to export data to a csv file

kat35601 · August 14, 2015

I need to add to the output of this code a button that will then export the data to a csv file.

<html>
<head>
<title>Status Screen</title>
</head>
<body>
<p> Created RedTags  </p>

<?php
$connect =odbc_connect("removed");
if(!$connect) {
	exit("Connection Failed: " . $connect);
}

$sql="SELECT uompschedulenumber, uompscheduleColor,jmaPartID,ompSalesOrderID, uomlSorP,serial,rail,panel,stile,upsdescription,itemtype,jmaPartShortDescription
, uomlStyleGroup,status ,
Case
when status=10 then 'Created' 
 when status=3 then 'PreSanding' 
 when status=4 then 'PrePaint' else 
'' end as Status1
FROM WIP_master 
	where  redtag='Y' and redtagclosed !='Y' and status=10
	order by uompschedulenumber,uomlSorP,upsdescription,jmaPartID 	";

$result =odbc_exec($connect,$sql);
if(!$result){
exit("Error in SQL");
}
echo "<table cellspacing='10' border='1'><tr>";

echo "<th>PartID</th>";
echo "<th>OrderID</th>";
echo "<th>SorP</th>";
echo "<th>Serial</th>";
echo "<th>Description</th>";
echo "<th>ItemType</th>";
echo "<th>ItemDesc</th>";
echo "<th>StatusDesc</th>";
echo "<th>Schedule</th>";


while (odbc_fetch_row($result)) {
	$bgcolor= odbc_result($result, "uompscheduleColor");
	$jmaPartID= odbc_result($result, "jmaPartID");
	$ompSalesOrderID= odbc_result($result, "ompSalesOrderID");
	$uomlSorP= odbc_result($result, "uomlSorP");
	$serial= odbc_result($result, "serial");
	$upsdescription= odbc_result($result, "upsdescription");
	$itemtype = odbc_result($result, "itemtype");
	$jmaPartShortDescription = odbc_result($result, "jmaPartShortDescription");
	$status1 = odbc_result($result, "status1");
   $uompschedulenumber=odbc_result($result, "uompschedulenumber");

   

  echo "<tr><td bgcolor=$bgcolor >$jmaPartID</td>";
  echo "<td style='text-align:center'>$ompSalesOrderID</td>";
  echo "<td style='text-align:center'>$uomlSorP</td>";
  echo "<td style='text-align:center'>$serial</td>";
   echo "<td style='text-align:center'>$upsdescription</td>";
  echo "<td style='text-align:center'>	$itemtype</td>";
   echo "<td style='text-align:center'>$jmaPartShortDescription</td>";
  echo "<td style='text-align:center'>$status1</td>";
    echo "<td style='text-align:center'> $uompschedulenumber</td>";

  
 
}

odbc_close($connect);
?>
</body>
</html>

Barand · August 14, 2015

Have your button link to another php file. That other php file should contain only code that queries the table and downloads the file.

for example:

mydownload.php

<?php
$mysqli = new mysqli(HOST,USERNAME,PASSWORD,'test');

$sql="SELECT uompschedulenumber, uompscheduleColor,jmaPartID,ompSalesOrderID, uomlSorP,serial,rail,panel,stile,upsdescription,itemtype,jmaPartShortDescription
, uomlStyleGroup,status ,
Case
when status=10 then 'Created' 
 when status=3 then 'PreSanding' 
 when status=4 then 'PrePaint' else 
'' end as Status1
FROM WIP_master 
    where  redtag='Y' and redtagclosed !='Y' and status=10
    order by uompschedulenumber,uomlSorP,upsdescription,jmaPartID     ";
    
// call the download
sql2csv($mysql, $sql, 'orderstatus.csv', 1);


/***************************** download function ******************************/    
function sql2csv($mysqli, $sql, $filename='', $headings=1)
        /**
        * Parameters
        * $mysqli   -   connection
        * $sql      -   the sql query to be executed
        * $filename -   name of download file (default "download_yymmddhhii.csv")
        * $headings -   1 if fieldname headings required (default), 0 if not required
        */
{
    if (!$filename)
        $f = 'download_' . date('ymdhi') . '.csv';
    else 
         $f = $filename;
    $fp = fopen('php://output', 'w');        // so you can fputcsv to STDOUT
    if ($fp) {
        $res = $mysqli->query($sql);
        if ($res) {
            header('Content-Type: text/csv');
            header('Content-Disposition: attachment; filename="'.$f.'"');
            header('Pragma: no-cache');
            header('Expires: 0');
            $row = $res->fetch_assoc();
            if ($headings) {
                fputcsv($fp, array_keys($row));
            }
            do {
                fputcsv($fp, $row);
            } while ($row = $res->fetch_assoc());
            
        }
        else echo "Error in query";
        fclose($fp);
    }
    
}

Sign In

need to add a the option to export data to a csv file

Recommended Posts

kat35601

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information