MSSQL as Data Source... (I'm confused)

ioos

Hi, DataTables looks great! I'd really like to get it working, but I'm new to web development, so I'm confused about some of the basics here.

Right now, I've got a basic site that uses PHP to query a MS SQL 2008 DB and populate an HTML table. I'd like to convert it to use DataTables. Do I follow the examples for "Server-side Processing"? Do I have other options?

Can someone please point me in the right direction? The table won't have more than 20 rows if that matters.

Main Page:
[code]
REPORT:

ROW
BDATE
EDATE
WORK TYPE
ENG
HOURS
COMMENTS




<?php
include("connect-db.php");

$sql= "SELECT row,convert(varchar,BDate,101)as 'BDate',
convert(varchar,EDate,101) as 'EDate',
workty,eng,hours,comments
FROM dbo.worknotes
WHERE report='$report'
ORDER BY row";
$result = sqlsrv_query($conn, $sql);
while($value=sqlsrv_fetch_array($result)){

echo "",
"$value[row]",
"$value[BDate]",
"$value[EDate]",
"$value[workty]",
"$value[eng]",
"$value[hours]",
"$value[comments]",
"";
}
?>


[/code]

connect-db.php:
[code]
<?php
$hostname = "VMSERVER";
$username = "user";
$password = "password";
$dbname = "DB";
$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));
}
?>
[/code]

fbas

You might be best off having the PHP/db write to HTML table then run datatables on the HTML table - this way you don't have to write any new code to interface the db.

The code you have (I'm assuming it works, right?) is sufficient. All you need to do now is add the datatables code

at minimum make sure you include: (basically I'm getting this from http://www.datatables.net/release-datatables/examples/basic_init/zero_config.html )
1) jquery library
2) data tables library
3) OPTIONAL, but you probably really do want to include a datatables CSS file (you can copy from http://www.datatables.net/release-datatables/media/css/demo_table.css or the zip file) and images used by the CSS (the header sort icons, for example)
4) call dataTable() routine on your HTML table

[code]






$(document).ready(function() {
$('#thisReport').dataTable();
} );

[/code]

With this minimum config/install, you'll be able to verify that your datatable works. You can then begin to customize it by adding elements to the init object passed into the dataTable() function

see more examples on http://www.datatables.net/examples/ or the feature reference on http://www.datatables.net/ref
[code]
// just an example, not an exhaustive list. see the examples
$(document).ready(function() {
$('#thisReport').dataTable(
"bPaginate": false,
"bLengthChange": false,
"bFilter": true,
"bSort": false,
"bInfo": false,
"bAutoWidth": false
);
} );
[/code]

Allan recently had written a blog for beginners: http://www.datatables.net/blog/Getting_started_with_DataTables%3A_First_steps

ioos

Hi fbas, thanks for your response! That works! I thought I was going to have to rewrite everything. The server side examples looked nothing like what I had.

fbas

Basically although you've generated your HTML table by a server-side db query, you are using DataTables in HTML-sourced mode. So you don't need the server-side examples... the basic examples will all work for you.