Datatables, refresh and fancybox

samb90

Hey,

I have been using datatables for the last few weeks. What I am now trying to do is load the data from a mySQL database at runtime and have all the filtering, pagination done on the client machine rather than having to keep contacting the server. I use a fancybox plugin which opens up when a row in a table is clicked. The user can then edit the row of data within the fancy box using an iframe. When the user closes the iframe I want the table to destroy itself then reload itself by downloading the rows of data from mySQL without reloading the whole page. I've seen several topics on the forum regarding this and they all point to the same api called fnReloadAjax() . I've also found a blog dictating exactly what I want here http://www.mccran.co.uk/index.cfm/2011/5/19/JQuery-datatable-with-added-FancyBox-and-auto-refresh

Im just really unsure in how I can get the data from mySQL and put it into the table using the json object... I have tried to use the script on the datatables website for php but keep getting this error 'DataTables warning: JSON data from server could not be parsed. This is caused by a JSON formatting error'.

I don't really want to use server side processing, but im not sure how else I can refresh the table without having to refresh the page.

Im pretty sure I am getting close, any help would be appreciated.

Cheers,
Sam

samb90

I tried to use the php script found on this website for php < 5.2 and I seem to get this error:

Unknown column 'SQL_CALC_FOUND_ROWS' in 'field list'

Bit confused

SirSpammenot

In perl (have you tried mojolicio.us?) I do 2 lines to populate the json for the table:

[code]my $CallsArray = $dbh->selectall_arrayref( "SELECT IDnumber, confirmation, lastChange, TIMEDIFF(NOW(),lastChange) AS 'ago...', currState, sizeSeconds, sizeBytes, CIDnumber, DID FROM Recordings ORDER BY lastChange DESC LIMIT 200" );
return $self->render_json({ aaData => $CallsArray }) if $CallsArray; [/code]

And it is all basically automatic since I specify the fields and names in the SQL. Just giving it a plug since I owe it my sanity.

OK, so where does that error happen? In PHP (an error log file) or in js (ie: in the browser). Sounds like a typo somewhere from the little you have posted. Post MORE details - in particular the part that specifies the SQL query (would be in PHP I guess) and the part that displays the table (in javascript).

samb90

Hey thanks for the reply, The PHP script I am using is the default script from the site. I've resorted to using the demo table and demo script until I can understand it better to implement it on my site.

[code]

<?php
/*
* Script: DataTables server-side script for PHP and MySQL
* Copyright: 2010 - Allan Jardine
* License: GPL v2 or BSD (3-point)
*/

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/

/* Array of database columns which should be read and sent back to DataTables. Use a space where
* you want to insert a non-database field (for example a counter or static image)
*/
$aColumns = array( 'engine', 'browser', 'platform', 'version', 'grade' );

/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "id";

/* DB table to use */
$sTable = "ajax";

/*REMOVED DATABASE DETAILS*/


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP server-side, there is
* no need to edit below this line
*/

/*
* MySQL connection
*/
$gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password'] ) or
die( 'Could not open connection to server' );

mysql_select_db( $gaSql['db'], $gaSql['link'] ) or
die( 'Could not select database '. $gaSql['db'] );


/*
* Paging
*/
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".mysql_real_escape_string( $_GET['iDisplayStart'] ).", ".
mysql_real_escape_string( $_GET['iDisplayLength'] );
}


/*
* Ordering
*/
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i

fbas

fnUpdate (http://www.datatables.net/ref#fnupdate) let's you update a row's data with values. set the 4th param to false to tell it not to redraw the table.

this only updates the client side. so you want to also send the data to a server side script that also updates the server.

allan

> Unknown column 'SQL_CALC_FOUND_ROWS' in 'field list'

What version of MySQL are you using? SQL_CALC_FOUND_ROWS is a MySQL instruction which will cause the database to calculate the number of rows that would have been returned if there wasn't a "LIMIT" on the SQL - this is an optimisation to try and make the script run faster.

Allan

samb90

hi allan mysql version is 3.23.56

fbas

that's an ANCIENT version of mysql.

you will have to take out the SQL_CALC_FOUND_ROWS and query to get that count

replace lines 106-116 with

[code]
/*
* SQL queries
* Get data to display
*/
$sQuery = "
SELECT ".str_replace(" , ", " ", implode(", ", $aColumns))." // took out SQL_CALC_FOUND_ROWS
FROM $sTable
$sWhere
$sOrder
$sLimit
";
$rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());

/* Data set length after filtering */
$sQuery = "
SELECT count($sIndexColumn)
FROM $sTable
$sWhere
";
$rFilteredTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
$iFilteredTotal= mysql_num_rows($rFilteredTotal) or die(mysql_error());
[/code]

samb90

Thanks, for the help thats moved me on from that error. Im now getting the JSON response Unknown column 'id' in 'field list'

Just trying to figure out why this is, the database table I have in mySQL was created using the demo version from this site and all the fields are there. Perhaps it could be a problem with my ancient version of mysql

fbas

ahh, set the $sIndexColumn near the top of the script to your primary key. (line 18)


also note that I left out " FROM $sTable" in my intial post (line 17 of the snippet of code I posted)

samb90

Ah brilliant, i added the FROM $sTable statement into the code and I now get the JSON response:

{"sEcho": 0, "iTotalRecords": 57, "iTotalDisplayRecords": , "aaData": [ ["Trident","Internet Explorer 4.0","Win 95+","4","X"],["Trident","Internet Explorer 5.0","Win 95+","5","C"]

Unfortunately the "DataTables warning: JSON data from server could not be parsed. This is caused by a JSON formatting error." dialog box error still appears.

I must be getting very close to fixing this as the response is now retrieving the data from the database.

Thank you for your patience by the way, its good to finally see some light at the end of the tunnel with your help after spending hours trying to understand the problem

fbas

the part that fetches iTotalDisplayRecords is broken. did you note my change to the code I posted?

I left out " FROM $sTable" in my intial post (line 17 of the snippet of code I posted)

samb90

yes i added the from statement into the code and it fixed the error telling me "Unknown column 'id' in 'field list'".... just trying to fix the iTotalDisplayRecords now

samb90

I got it working, thanks for your help fbas and allen, much appreciated. Just a quick question if i want to refresh the data in the table by calling my php script connected to mysql would it be best to use frmReloadAjax or the draw method?

fbas

no. fnReloadAjax is for (static) AJAX sourced data.

for bServerSide sourced data, just call fnDraw http://www.datatables.net/ref#fnDraw or http://www.datatables.net/api#fnDraw , and you might want to call fnDraw(false) to keep it from re-sorting and moving back go page 1.

the fnDraw will trigger the fnServerData, calling your server side script via AJAX.

samb90

ah right i got that implemented thanks, I have another question.

In my table i now create table with the following fields: ID, Name, Manager, Client.

I would like another column after client which displays an image of a tick if the record is complete or a cross if it is complete. I have the code for this and it reads no problem, to decide which image to use I usually query the mysql table for a column called Complete which displays either the values On or Off based upon whether to display an image of a tick or cross.

I need to query this column in the php server script for my if statement to decide on which picture to use.

The problem is i do not want this column which contains the values on or off to display in my table, I just want to query it and then display an image which i can do.

How can i query the column Clients from my mysql database without having the column values displayed in my JSON table? is there anyway to hide this column.

Sam

fbas

yes, you can hide columns in DataTables using bVisible for that column. http://www.datatables.net/ref#bVisible

Since you need a column in order to display the image, you might not want the column to be invisible, but just might want to render that column as an image based on the value in it. http://www.datatables.net/ref#fnRender

(and you can disable sorting on that column with bSortable http://www.datatables.net/ref#bSortable )

samb90

"bProcessing": true,
"bStateSave": true,
"bServerSide": true,
'aoColumnDefs': [{'aTargets': [6,7,8,9,10], 'bSortable': false},
{'aTargets': [ 3 ], 'bVisible': false}
],
'sPaginationType': 'full_numbers',


bVisible has no effect?

dianar

Hey samb90!

I'm having the same as problem as you did, I mean I'm getting JSON code when I called the script, but unfortunately the "DataTables warning: JSON data from server could not be parsed. This is caused by a JSON formatting error." dialog box error still appears.
May you tell me how you fixed that problem???? or can anyone help me?

Thanks!

allan

@dlanar - please don't cross post the same message :-). See http://datatables.net/forums/discussion/4467/fields-with-html-tags-are-not-sorting-properly#Item_27 .

Allan