Export MSSQL Query To CSV File From PHP

Question

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>

A quick google search returned this answer: https://stackoverflow.com/questions/13108157/php-array-to-csv — Tonmoy Roy, Jun 24 '17 at 20:45
@TonmoyRoy - that is mysql not mssql - I am very new to this and still learning. Could that be adapted for a mssql solution? — Hot Love, Jun 24 '17 at 20:55
Try to receive the results as array (not objects) and use fputcsv to save in a csv file: http://php.net/manual/de/function.fputcsv.php — user1915746, Jun 25 '17 at 13:08

miken32 · Accepted Answer · 2017-09-12T23:01:01.743

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>

Where is the button press that exports this to a csv? I follow your syntax (and it is much more readable than mine) - but I do not see where that step is. — Hot Love, Jun 26 '17 at 22:50
Your question said you knew how to do that. I've edited my answer to include it. — miken32, Jun 26 '17 at 22:55
I should have elaborated further, I know how to create the button - was not sure how to assign the function to it. Thank you for the assistance. — Hot Love, Jun 26 '17 at 23:09

Export MSSQL Query To CSV File From PHP

1 Answers1