Updating Table with New Query with

majortom

So I have a PHP script that queries a database, and outputs JSON. The query is something like so

[code]
SELECT year AS 'label', SUM(size) AS 'value'
FROM db.dbo.table
GROUP BY year
[/code]

This adds up the size of each item made in the respective year, and displays the total sum for all years in a Datatable. I made a dropdown box that shows all of the years, like so

[code]
$.getJSON('script.php', function(data){
var html = '';
var len = data.length;

html += 'All';

for (var i = 2; i< len; i++) {
html += '' + data[i].label + '';
}

$('select#list').append(html);
});
[/code]

What I want to do is when the user selects a year, it updates the Datatable with all of the individual objects that were made in that year. This would require new columns. I was able to get it to update the table using this

[code]
$(document).on('change', '#list', function (event) {
oTable.fnReloadAjax();
});
[/code]

However, I need to get that option to the PHP script. And then the script has to determine whether the option is All or a year (if statement, I assume), and then select the correct query.

[code]
<?php
$myServer = "server";
$myDB = "db";

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

$sql ="SELECT year AS 'label', SUM(size) AS 'value'
FROM db.dbo.table
GROUP BY year";

$data = sqlsrv_query ($conn, $sql);
$result = array();

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

echo json_encode ($result);

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

Here is my initialization code

[code]
var oTable = $('#chart').dataTable({
"bProcessing": true,
"sPaginationType": "full_numbers",
"sAjaxSource": "script.php",
"sAjaxDataProp": "",
"aoColumns": [
{ "mData": "label", "sClass": "center" },
{ "mData": "value", "sClass": "center" }
],
"fnServerParams": function (aoData) { // Can I use this without using server-side? I could never get this working because I was using SQL Server
// Push an object to the aoData array to make it available server side
aoData.push({ "name": "label", "value": $('#list')[0].value });
},
"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, "mColumns": [ 0, 1 ] },
{ "sExtends": "csv", "bSelectedOnly": true, "mColumns": [ 0, 1 ], "bFooter": false },
{ "sExtends": "xls", "bSelectedOnly": true, "mColumns": [ 0, 1 ], "bFooter": false },
{ "sExtends": "pdf", "bSelectedOnly": true, "mColumns": [ 0, 1 ], "bFooter": false },
]
},
{ "sExtends": "print", "sButtonText": "Print View" }
]
}
});
[/code]

And my HTML for the original query (where the sizes are summed)

[code]



year
size





Loading data from server





year
size



[/code]

I'm running into a few problems. For all of the years, the columns are coded into the HTML page, and I don't know how to change them. I also have no idea how to update the query on the PHP script so that it shows the new information when they click an option.

Can someone please help me out? Thanks!

fbas

I believe fnServerData only works with server side processing (correct me if I'm wrong someone)...

but you could still override your datatables settings to update the sAjaxSource and re-initialize the table

[code]
oSettings = {
"bProcessing": true,
"sPaginationType": "full_numbers",
"sAjaxSource": "script.php",
"sAjaxDataProp": "",
"aoColumns": [
{ "mData": "label", "sClass": "center" },
{ "mData": "value", "sClass": "center" }
],
// other items skipped for brevity
};

var oTable = $('#chart').dataTable(oSettings);

// then later after someone selects a year
oSettings.sAjaxSource = "script.php?year=" + year;
oSettings.bDestroy = true;

oTable = $('#chart').dataTable(oSettings);

// need to change back? override those settings again
oSettings.sAjaxSource = "script.php";
oSettings.bDestroy = false;
[/code]

not fully tested, but I believe this approach will work as a quick hack.

majortom

A few questions. Where is the variable, "year" coming from in the URL? And this value is passed to the PHP as a variable? What would the variable be in the PHP code? And would the JS be exactly as you wrote it, or do these statements need to be in if statements? I am getting this error from your code:

[quote]DataTables warning (table id = 'chart'): Cannot reinitialize DataTable. To retrieve the DataTable object for this table, pass no arguements or see the docs for bRetrieve or bDestroy
[/quote]

Also, what would I need to do to the script to enable server-side processing, if you know? Would it be easier to do that route?

Thanks

fbas

oh. the bDestroy should stay true. setting it back to false makes no sense.

yes, using server-side processing is MUCH nicer and is pretty easy. there's a sample script for the back end db (in PHP) already built and available on http://datatables.net/release-datatables/examples/server_side/server_side.html for mysql. it's not much more complex than your mssql script.. it just handles a few more parameters (column sorting, filtering, pagination) and you can probably port it easily to mssql.

the specs you need to match for a server-side processing script are on http://datatables.net/usage/server-side .. just make sure your result json includes the value specified on the table on the right hand side of this page.

~~~

I imagined you would know where to set/query the 'year' somewhere in javascript (i.e. when user selects a year in the SELECT element)

adding it to the ajax url in the fashion I mention above basically would pass the value as a query string param, and would be accessible in your PHP script in $_GET['year']. If the variable is set and is a valid number, you would probably alter the SQL to include "WHERE year = '$year' "

but once again, moving to proper server-side processing is a better approach.

majortom

So, I think I'm going to scrap this approach because I cannot get server side to work. I have brainstormed another solution though that seems to be working a bit nicer. I added a column that links to each year

[code]
{
"sTitle": "View Individual Records",
"sClass": "center",
"mData": "label",
"bSortable": false,
"sWidth": "200px",
"mRender": function (data, type, full) {
return 'View Record';
}
}
[/code]

So this takes the year, and adds it to a URL (e.g. http://example.com/stats/query.php?value=YEAR). Now, this link will take the user and the variable to this page when they click it. This new page will have a new table on it for the new query. Do I need a separate page for the JSON? Or can I do something such as this...

[code]
<?php
// Uncomment this line for troubleshooting / if nothing displays
//ini_set('display_errors', 'On');

// Server Name
$myServer = "server";

// Database
$myDB = "db";

// Notice that the latest driver uses sqlsrv rather than mssql
$conn = sqlsrv_connect ($myServer, array('Database'=>$myDB));

$value = $_GET['value'];

$sql ="SELECT year AS 'label', size AS 'value'
FROM db.dbo.table
WHERE year = $value";

$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);
?>

<!DOCTYPE html>



<!-- Stuff goes here -->



<?php include '../common/header.inc' ?>








There doesn't seem to be anything here!





<?php include '../common/footer.inc'?>





[/code]

I then have the initialization for Datatables linking to this page.

I know the PHP at the top creates the proper JSON, but the Datatables initialization cannot read the JSON now because of the HTML below it. I'm trying to do this because I need to cut down on the amount of files I use. I'm already using an insane amount of pages for the tables that will link to the ones I'm trying to make (30 graphs already, so 30 PHP scripts returning JSON, 30 JS files with the initialization, 30 HTML files to display it). Is there any better way to do this? (and to even get this working for that matter)