Fetch Names/Number of Columns from Database

majortom

I have a SQL Server database, and I would like to create a table based on the query that is run. Here is my .PHP code

[code]
<?php
// Server Name
$myServer = "serverName";

// Database
$myDB = "databaseName";

$conn = sqlsrv_connect ($myServer, array('Database'=>$myDB));

$sql ="SELECT col1, col2, col3, col4, col5
FROM databaseName.dbo.tableName";
$data = sqlsrv_query ($conn, $sql);

$result = array();

do {
while ($row = sqlsrv_fetch_array ($data, SQLSRV_FETCH_ASSOC)) {
$result[] = $row;
}
} while (sqlsrv_next_result($data));

// This will output in JSON format
echo json_encode ($result);

sqlsrv_free_stmt ($data);
sqlsrv_close ($conn);
?>
[/code]

Right now, I have to manually specify that the column names are col1, col2, col3, etc..., and how many there are. This will not work if a new column was added/removed later in the database since I would have to go back to the datatable code/HTML and change the columns. Here is my initialization

[code]
$('#table').dataTable({
"bProcessing": true,
"sPaginationType": "full_numbers",
"sAjaxSource": "script.php",
"sAjaxDataProp": "",
"aoColumns": [
{ "mData": "col1", "sClass": "center" },
{ "mData": "col2", "sClass": "center" },
{ "mData": "col3", "sClass": "center" },
{ "mData": "col4", "sClass": "center" },
{ "mData": "col5", "sClass": "center" }
],

"sDom": 'T<"clear">Rlfrtip',
"oTableTools": {
"sSwfPath": "media/swf/copy_csv_xls_pdf.swf",
"sRowSelect": "multi",
"aButtons": ["select_all", "select_none",
{
"sExtends": "collection",
"sButtonText": "Export Selected Rows",
"aButtons": [
{"sExtends": "copy", "bSelectedOnly": true },
{ "sExtends": "csv", "bSelectedOnly": true },
{ "sExtends": "xls", "bSelectedOnly": true },
{ "sExtends": "pdf", "bSelectedOnly": true },
]
},
{ "sExtends": "print", "sButtonText": "Print View" }
]
}
});
[/code]

And HTML

[code]



col1
col2
col3
col4
col5





Loading data from server





col1
col2
col3
col4
col5



[/code]

How can I query the database, and datatables add the proper columns/names to the table? Can someone help me out? Thanks!