Export MSSQL Query To CSV File From PHP

I am using the below syntax within my joomla article and I am in need of a way to add a php button (I know the syntax or this) - and on the button press event fire off exporting the SQL Query Results (header and data) to a csv file. This is the syntax i am using to populate a table. Is this easily ammendable to add in a function to export to .csv also?

<html>
<body>
    <form method="POST">
    </form>
</body>

<?php
$option = array();
$option['driver'] = 'mssql';
$option['host'] = 'IP Address';
$option['user'] = 'username';
$option['password'] = 'password';
$option['database'] = 'database';
$option['prefix'] = '';
$db = JDatabase::getInstance($option);
$query = $db->getQuery(true);
$query = "Select height, weight, userID, name from personelinfo;";
$db->setQuery($query);
$query = $db->loadObjectList();
if ($query) 
{
?>
<table border="1">
    <thead>
        <tr>
            <th>height </th>
            <th>weight </th>
            <th>userID </th>
            <th>name </th>
        </tr>
    </thead>
<?php
    foreach ($query as $res) 
    {
        print "<tr>";
        print "<td>" . $res->height . "</td>";
        print "<td>" . $res->weight . "</td>";
        print "<td>" . $res->userID . "</td>";
        print "<td>" . $res->name . "</td>";
        print "</tr>";
    }
} 
?>
</table>
</html>

Solution

You want to have much more separation between your PHP and HTML output. This will serve you well when you want to output other formats such as CSV. Here I get the database results at the top of the file and load them into an array, before any output is done — ideally this would be done in a separate file.

Then we can check if CSV output is desired. I've changed the database code to return an associative array instead of an object, this makes it trivial to pass each row to fputcsv().

Note I've also used alternative syntax and short echo tags to reduce PHP/HTML intermixing. This is a good practice to get into. Finally, your HTML was a mess; you were closing the body before outputting the table, and omitting the <head> and <tbody> elements.

<?php
$option = array();
$option['driver']   = 'mssql';
$option['host']     = 'IP Address';
$option['user']     = 'username';
$option['password'] = 'password';
$option['database'] = 'database';
$option['prefix']   = '';
$db                 = JDatabase::getInstance($option);
$query              = "Select height, weight, userID, name from personelinfo;";

$db->setQuery($query);
$resultset = $db->loadAssocList();

if (!empty($_GET["csv"])) {
    $out = fopen("php://stdout");
    header("Content-Type: text/csv");
    foreach ($resultset as $res) {
        fputcsv($out, $res);
    }
    die;
}

?>
<!DOCTYPE html>
<html>
<head>
    <title>Test</title>
</head>
<body>
<?php if(count($resultset):?>
<table border="1">
    <thead>
        <tr>
            <th>height </th>
            <th>weight </th>
            <th>userID </th>
            <th>name </th>
        </tr>
    </thead>
    <tbody>
<?php foreach($resultset as $res):?>
        <tr>
            <td><?= $res["height"] ?></td>
            <td><?= $res["weight"] ?></td>
            <td><?= $res["userID"] ?></td>
            <td><?= $res["name"] ?></td>
        </tr>
<?php endforeach;?>
    </tbody>
</table>
<form method="get">
    <button type="submit" name="csv" value="1">Export to CSV</button>
</form>
<?php endif;?>
</body>
</html>