Filling Datatables with MSSQL data

Question

I am really struggling to populate a datatable with an MSSQL database (It is managed in SQL Server 2012 Express). I've tried several scripts from their site (Their server side PHP with ODBC script); however, none have worked. I stumbled across a post (http://www.datatables.net/forums/discussion/7359/mssql-as-data-source...-im-confused/p1) that I'm trying to use now. Here is my code

<table class="display" id="table1">
    <thead>
        <tr>
            <th>PRIMARY HEADINGS</th>
            <th>PRIMARY CLASS CODE</th>
            <th>PH DESCRIPTION</th>
        </tr>
</thead>
    <tbody>
        <?php
            include("scripts/script.php");

            $sql= "SELECT *
            FROM dbo.PriClass
            ORDER BY ID";
            $result = sqlsrv_query($conn, $sql);

            while($value=sqlsrv_fetch_array($result)){
                echo "<tr>",
                "<td>$value[Primary Headings]</td>",
                "<td>$value[Primary Class Code]</td>",
                "<td>$value[PH Description]</td>",
                "</tr>";
            }
        ?>
    </tbody>
    <tfoot>
        <tr>
            <th>PRIMARY HEADINGS</th>
            <th>PRIMARY CLASS CODE</th>
            <th>PH DESCRIPTION</th>
        </tr>
</tfoot>
</table>

And here is the .php file

<?php
    $hostname = "SERVERNAME";
    $username = "USERNAME";
    $password = "PASSWORD";
    $dbname = "DBNAME";
    $connectionInfo = array( "UID"=>$username, "PWD"=>$password, "Database"=>$dbname);
    $conn = sqlsrv_connect($hostname, $connectionInfo);

    if($conn === false) {
        echo "Unable to connect to database.";
        die( print_r( sqlsrv_errors(), true));
    }  
?>

So this code outputs this:

https://i.stack.imgur.com/UQecw.png

I know next to nothing about PHP and databases, so I'm not sure how to fix this. Ideally, I'd like to have another while loop that loops through the columns as well. E.g.

while(loops through each row) {
    echo "<tr>",

    while(loops through each column) {
        "<td>value of the cell</td>",
    }

    "</tr>";
}

This way I can easily reuse the code for databases of different sizes. I guss I'd also have to have other php code for the thead and tfoot though. The database that I'm testing this on has 4 columns (one is ID, and is just used for indexing) and 14 rows. I am building this in WebMatrix 3, and I have already connected my databases through that. If someone can help me out, that would be great. Thanks

---

EDIT: I solved the question (Thank you for those who closed this...). The answer does not rely on server-side processing, but I don't think I need it anyways. I just reads the data from the MSSQL database, then fills the table with it.

.php

<?php
// This is for SQL Authentication. I've added instructions if you are using Windows Authentication

// Uncomment this line for troubleshooting / if nothing displays
//ini_set('display_errors', 'On');

// Server Name
$myServer = "SRVR";

// If using Windows Authentication, delete this line and the $myPass line as well.
// SQL Server User that has permission to the database
$myUser = "usr";

// SQL Server User Password
$myPass = "Passwd1";

// Database
$myDB = "TestDB";

// If using Windows Authentication, get rid of, "'UID'=>$myUser, 'PWD'=>$myPass, "
// Notice that the latest driver uses sqlsrv rather than mssql
$conn = sqlsrv_connect($myServer, array('UID'=>$myUser, 'PWD'=>$myPass, 'Database'=>$myDB));

// Change TestDB.vwTestData to YOURDB.dbo.YOURTABLENAME
$sql ="SELECT * FROM TestDB.dbo.vwTestData";
$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 if you try to hit the page in a browser
echo json_encode($result);

sqlsrv_free_stmt($data);
sqlsrv_close($conn);
?>

.js

$(document).ready(function() {
    $('#table1').dataTable( {
        "bProcessing": true,
        "sAjaxSource": "scripts/script.php",
        "sAjaxDataProp": "",
        "aoColumns": [
            { "mData": "Column 1" },
            { "mData": "Column 2" },
            { "mData": "etc..." },
        ]
    });
});

Answer 1

I would agree, check your server to make sure it is parsing php.

In addition, I have found that when you access php array variables inside of an echo, you must put brackets around them. Therefore:

$value[Primary Headings]

would become:

{$value['Primary Headings']}

Also having spaces as indicies in an array is bad practice, so I would avoid it in the future. As for the while loops, try this:

function associativeArrayForResult($result){
  $resultArray = array();
    for($i=0; $i<sqlsrv_num_rows($result); $i++){
        for($j=0; $j<sqlsrv_num_fields($result); $j++){
            sqlsrv_fetch($result, $i);
            $resultArray[$i][sqlsrv_get_field($result, $j)] = sqlsrv_get_field($result, $j);
        }
    }
    return $resultArray;
}