Enabling server-side processing using PHP to connect to SQL Server DB - Causing 404 error

mstiver2019

Hello!

Using razor pages, I've created the following datatable. This works for client-side processing, and I am working to switch it to server-side by connecting to my SQL Server database:

<script type="text/javascript">
    $(document).ready(function () {
        $('#OOResponse').DataTable({
            serverSide: true,
            ajax: "getData.php",
            processing: true,
            paging: true,
            pageLength: "10",
            language: { search: "", searchPlaceholder: "Search" },
            columnDefs: [{ type: 'date', targets: [1, 3] }],
            colReorder: true,
            order: [[3, 'desc']]
        });
    });
</script>

I've never used the PHP language before, however, I've been doing a lot of googling and have gathered this (My SQL Server uses Windows Auth to connect. I'd like to just select all of the table rather than call out specific columns like I've seen in other examples, if that's possible):

<?php
$serverName = "COMPUTERNAME\\sqlexpress, 1433";
$databaseName = "app_sppcwebdev";

$conn = sqlsrv_connect ($serverName, array( 'Database' => $databaseName));

$sql = "SELECT * FROM dbo.OOResponses";
$table = sqlsrv_query( $conn, $sql);

$result = array();

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

// Output in JSON format
echo json_encode($result);
sqlsrv_free_stmt($table);
sqlsrv_close($conn);

?>

The PHP file is saved in the same directory as my pages.. So I'm unsure of why it doesn't seem to be locating my script. I've tried "OOResponses/getData.php", among several other path names. Could something else be wrong? Here's a screenshot from VS:

I also used the debugger, in case you'd like to see that: https://debug.datatables.net/olonag

Please let me know if you need any other info.

allan

The response from the server (in the debug trace - thanks for that!) shows a 404 not found. That means that getData.php is not in the same directory as where your Javascript file is being executed.

Its impossible to say for sure why that is, but I would suggest trying to use an absolute path - e.g. /OOResponses/getData.php if you aren't sure where it is being executed from (the exact path depends on where the web root is). The server's error log will also likely give a clue.

Allan

mstiver2019

Allan, thanks for confirming that this is the problem.
(Let me know if I need to post this as a brand new question in the forum for this, since it IS changing the subject a bit)

For the sake of time, I've decided to keep my datatable client-side for now and to use the scroller extension. I've been looking at the example given here: https://datatables.net/extensions/scroller/examples/initialisation/large_js_source.html
and am working to tweak it for my needs

My question with this is, how would I display my data now? The example just produces numbers, and of course I have data I'd like to display instead.

<script type="text/javascript">
    $(document).ready(function () {
        var data = []; // what does my 'data' definition need updated to?
        for (var i = 0; i < 100; i++) {
            data.push([i, i, i, i]);
        }
        $('#OOResponse').DataTable({
            data: data,
            deferRender: true,
            scrollY: 200,
            scrollCollapse: true,
            scroller: true,
            language: { search: "", searchPlaceholder: "Search" },
            columnDefs: [{ type: 'date', targets: [1, 3] }],
            colReorder: true,
            order: [[3, 'desc']]
        });
    });
</script>

Here's my table:

<div class="table-responsive">
                        <table class="table-condensed table-bordered nowrap" id="OOResponse" width="100%" cellspacing="0">
                            <!--Table Header-->
                            <thead>
                                <tr>
                                    <th>
                                        @Html.DisplayNameFor(model => model.OOResponse[0].SupplierNo)
                                    </th>
                                    <th>
                                        @Html.DisplayNameFor(model => model.OOResponse[0].ResponseDate)
                                    </th>
                                    <th>
                                        @Html.DisplayNameFor(model => model.OOResponse[0].EnterBy)
                                    </th>
                                    <th>
                                        @Html.DisplayNameFor(model => model.OOResponse[0].EnterDate)
                                    </th>
                                    <th></th>
                                </tr>
                            </thead>
<!--tbody would not exist here, correct?-->
                        </table>
                    </div>

allan

Hi,

Information on how to load data into the table is available in the manual here.

In the example you've copy / pasted the data array is generated and assigned to data. That is one of the ways to populate the table with data.

Allan