Detail row + column search together

Jr0x

Hi,

I'm new to jQuery and have a issue implementing both detail row and column search together.

Currently, this is my jQuery

[code]
var asInitVals = new Array();

$(document).ready(function() {
/* Formating function for row details */
function fnFormatDetails ( nTr )
{
var aData = oTable.fnGetData( nTr );
var sOut = '';
sOut += 'Rendering engine:';
sOut += 'Link to source:Could provide a link here';
sOut += 'Extra info:And any further details here (images etc)';
sOut += '';

return sOut;
}
/*
* Support functions to provide a little bit of 'user friendlyness' to the textboxes in
* the footer
*/
$("tfoot input").each( function (i) {
asInitVals[i] = this.value;
} );
$("tfoot input").focus( function () {
if ( this.className == "search_init" )
{
this.className = "";
this.value = "";
}
} );
$("tfoot input").blur( function (i) {
if ( this.value == "" )
{
this.className = "search_init";
this.value = asInitVals[$("tfoot input").index(this)];
}
} );

var oTable = $('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "server_processing.php?dt=<?php echo $type; ?>",
"oLanguage": {
"sSearch": "Search all columns:"
},
"bStateSave": true,
"fnInitComplete": function() {
var oSettings = $('#example').dataTable().fnSettings();
for ( var i=0 ; i0){
$("tfoot input")[i].value = oSettings.aoPreSearchCols[i].sSearch;
$("tfoot input")[i].className = "";
}
}
}
} );

$("tfoot input").keyup( function () {
/* Filter on the column (the index) of this element */
oTable.fnFilter( this.value, $("tfoot input").index(this) );
} );

$('#example tbody td img').live( 'click', function () {
var nTr = this.parentNode.parentNode;
if ( this.src.match('details_close') )
{
/* This row is already open - close it */
this.src = "images/details_open.png";
oTable.fnClose( nTr );
}
else
{
/* Open this row */
this.src = "images/details_close.png";
oTable.fnOpen( nTr, fnFormatDetails(nTr), 'details' );
}
} );



} );

[/code]

In my server processing script, I have input the following:

[code]$aColumns = array( ' ', 'a', 'b', 'c', 'd', 'e', 'f' );
$sOutput .= '"",';[/code]

When I open up server_processing.php, it works fine. Results output are ok (with image).

On the page that I'm suppose to output the data, I have also added a new th for header and footer.

[code][/code]

And when I load the script, it says JSON formatting error. What am I missing here? Thanks

allan

Almost certainly I'd say that there is an unescaped quote (the $sOutput with the img tag looks dodgy) or a trailing comma. Run your json through http://jsonlint.com to know for sure.

Allan

Jr0x

I tried to use jsonlint and it says that the output is valid.

Here's my JSON data.

[code]
{
"sEcho": 0,
"iTotalRecords": 3,
"iTotalDisplayRecords": 3,
"aaData": [
[
"",
"3S",
"38",
"09",
"300000",
"500000",
"Test data"
],
[
"",
"5I",
"34",
"12",
"500000",
"1000000",
"aaaaa"
],
[
"",
"Jumbo",
"9",
"8",
"888888",
"8888888",
"testtt"
]
]
}
[/code]

I have changed to

[code]$sOutput .= '"",';[/code]

make it valid.

Why is this happening?

Jr0x

I had it working by changing to jquery code. Not sure what happens actually.

But had another issue, will post soon.

Jr0x

Ok, one step at a time.

I've manage to get it working with details row, not doing column search at the moment.

[code]/* Formating function for row details */
function fnFormatDetails ( oTable, nTr )
{
var aData = oTable.fnGetData( nTr );
var sOut = '';
sOut += 'Rendering engine:'+aData[1]+' '+aData[4]+'';
sOut += 'Link to source:Could provide a link here';
sOut += 'Extra info:And any further details here (images etc)';
sOut += '';

return sOut;
}

$(document).ready(function() {
/*
* Insert a 'details' column to the table
*/
var nCloneTh = document.createElement( 'th' );
var nCloneTd = document.createElement( 'td' );
nCloneTd.innerHTML = '';
nCloneTd.className = "center";

$('#example thead tr').each( function () {
this.insertBefore( nCloneTh, this.childNodes[0] );
} );

$('#example tbody tr').each( function () {
this.insertBefore( nCloneTd.cloneNode( true ), this.childNodes[0] );
} );

/*
* Initialse DataTables, with no sorting on the 'details' column
*/
var oTable = $('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "server_processing.php?dt=<?php echo $type; ?>",
"aoColumnDefs": [
{ "bSortable": false, "aTargets": [ 0 ] }
],
"aaSorting": [[1, 'asc']],
"oLanguage": {
"sSearch": "Search all columns:"
}
});

/* Add event listener for opening and closing details
* Note that the indicator for showing which row is open is not controlled by DataTables,
* rather it is done here
*/
$('#example tbody td img').live('click', function () {
var nTr = this.parentNode.parentNode;
if ( this.src.match('details_close') )
{
/* This row is already open - close it */
this.src = "images/details_open.png";
oTable.fnClose( nTr );
}
else
{
/* Open this row */
this.src = "images/details_close.png";
oTable.fnOpen( nTr, fnFormatDetails(oTable, nTr), 'details' );
}
} );

} );[/code]

But when I try to do a search, it is not working. It says that the JSON data is wrong.

allan

Can you post the JSON from when you get the error? I'd imagine that there is a character there which is making it invalid. Also, it's jQuery which is noting that the JSON is invalid, not DataTables - and jQuery has a strict JSON parser.

Allan

Jr0x

When I first load the page, the data is being loaded out correctly with detailed row. However, when I'm doing search, it returns a JSON error. I tested JSON and it is valid. I post out the whole code that I'm using, please me help see.

[code]

/* Formating function for row details */
function fnFormatDetails ( oTable, nTr )
{
var aData = oTable.fnGetData( nTr );
var sOut = '';
sOut += 'Rendering engine:'+aData[1]+' '+aData[4]+'';
sOut += 'Link to source:Could provide a link here';
sOut += 'Extra info:And any further details here (images etc)';
sOut += '';

return sOut;
}

$(document).ready(function() {
/*
* Insert a 'details' column to the table
*/
var nCloneTh = document.createElement( 'th' );
var nCloneTd = document.createElement( 'td' );
nCloneTd.innerHTML = '';
nCloneTd.className = "center";

$('#example thead tr').each( function () {
this.insertBefore( nCloneTh, this.childNodes[0] );
} );

$('#example tbody tr').each( function () {
this.insertBefore( nCloneTd.cloneNode( true ), this.childNodes[0] );
} );

/*
* Initialse DataTables, with no sorting on the 'details' column
*/
var oTable = $('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "server_processing.php",
"aoColumnDefs": [
{ "bSortable": false, "aTargets": [ 0 ] }
],
"aaSorting": [[1, 'asc']],
"oLanguage": {
"sSearch": "Search all columns:"
}
});

/* Add event listener for opening and closing details
* Note that the indicator for showing which row is open is not controlled by DataTables,
* rather it is done here
*/
$('#example tbody td img').live('click', function () {
var nTr = this.parentNode.parentNode;
if ( this.src.match('details_close') )
{
/* This row is already open - close it */
this.src = "images/details_open.png";
oTable.fnClose( nTr );
}
else
{
/* Open this row */
this.src = "images/details_close.png";
oTable.fnOpen( nTr, fnFormatDetails(oTable, nTr), 'details' );
}
} );

} );

[/code]

[code]




Type
Blk
Floor
Valuation
Asking
Remarks




Loading data from server





Type
Blk
Floor
Valuation
Asking
Remarks



[/code]

Jr0x

[code]
<?php
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 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)
*/
// Inserted a non-db field
$aColumns = array( ' ', 'hdb_type', 'blk', 'floor', 'valuation', 'asking', 'remarks' );

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

$sTable = 'belief_hdb';

/* Removed the SQL connection */

/* Removed in betweens */

/*
* Output
*/
$sOutput = '{';
$sOutput .= '"sEcho": '.intval($_GET['sEcho']).', ';
$sOutput .= '"iTotalRecords": '.$iTotal.', ';
$sOutput .= '"iTotalDisplayRecords": '.$iFilteredTotal.', ';
$sOutput .= '"aaData": [ ';
while ( $aRow = mysql_fetch_array( $rResult ) )
{
$sOutput .= "[";
// Inserted this
$sOutput .= '"",';
for ( $i=0 ; $i

allan

What version of jQuery are you using? Can you try using the nightly version of DataTables, which you can download from the downloads page: http://datatables.net/download/ . Does that fix it? If not, I think we are going to need a link to see what is actually happening.

Allan

Jr0x

I'm using jQuery 1.4.3. Tested Nightly version and it does not fix. But gave me a parsing error while doing the search.

I'm currently setting up a test site for you to take a look. Please hang on.

Jr0x

Hi allan,

Please take a look at

Let me know if I'm missing out of anything to provide.

Thanks.

allan

Thanks for the link. When I try typing something into the search box the response from the server is:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIKE '%a%' OR hdb_type LIKE '%a%' OR blk LIKE '%a%' OR floor LIKE '%a%' OR valua' at line 3

So that's why DataTables is giving you the invalid JSON warning - it is in fact invalid JSON :-). So a modification is needed for your server-side script to address whatever is causing that (it's the end bit which looks dodgy).

Allan

Jr0x

Would it be the WHERE statement? Does adding a blank non-database field will cause the issue?

As in the WHERE statement, the ' ' will also be searched?

The only modification made was

[code]$sOutput .= '"",';[/code]

If at the first load it is fine, the only thing that is affecting the search I can think of might be the WHERE statement. What do you think?

The server side script was taken whole load from your example with exclusion to the non-database field and img output.

allan

Yes I would imagine that the non-blank column field probably won't help (not sure if it would cause exactly this problem, but it will likely cause an error). Can you just remove that, and add an empty string to the sOutput at the start, and see if that makes a difference?

Beyond that, I'd suggest printing out the generated SELECT statement and see where the query is going wrong.

The other thing which looks most odd is that sEcho is 0. sEcho should never be zero for server-side processing, 1 or higher (incrementing with each draw, which will happen with my demo script).

Allan

Jr0x

Our guess is right. It seem like the non-db field that I added to the array is causing the problem.

I removed the blank from the array and it is working fine now. Even though, my sEcho is still 0. Just a small question, did you notice that the + button is a little out of place. What is causing that?

I'll add in column search and other stuff.
Will need to trouble you again if there is any issue.

Thanks for the help though, else might not work out so fast.

allan

Good to hear that is sorted. The sEcho should simply be echo out the sEcho which is given to the script (cast as an int for security reasons) - and this actually appears to be happening on your site - just not in the above json - so perhaps safe to ignore.

The reason for the + button being out of place is that you have margin-bottom: 20px on it (styles.css line 44).

Regards,
Allan

Jr0x

Yep, saw that CSS. Thanks.

Another question, I need to sort out the data out according to different id. So I pass this statement.

[code]"sAjaxSource": "server_processing.php?dt=<?php echo $type; ?>&id=<?php echo $R['id']; ?>", ?>[/code]

$type refer to the database table. I have no issue with this. But the ID has some issue.
So do I add in the code in server side under the $sWhere to add in

[code]WHERE aid = $_GET['id'][/code]

allan

yes that looks like a fair enough thing to do. Just remember to escape the using input for SQL :-)

Allan

Jr0x

Yes, but when I add in another line in the $sWhere right before the closing ')'. It is not working as I thought it should be.

[code]
if ( $_GET['sSearch'] != "" )
{
$sWhere = "WHERE (";
for ( $i=0 ; $i

allan

What SQL does that generate? It looks like it probably is about the right place, but you'll need brackets around the OR parts - try switching lines 10 and 12 (and add spaces to the bracket in line 12).

Allan

Jr0x

Ok. It's working now, but this is what I did.

Changed the line so that the SQL query works correctly. AND added this code.

[code]
// Before was != "", that is if there is a search.
// But what if there is no search (first load), have to set the search to blank
if ( $_GET['sSearch'] == "" )
{
$sWhere = "WHERE (";
for ( $i=0 ; $i

allan

Looks perfectly reasonable to me - and if it's working that sounds promising!

Allan

Jr0x

Great. Feels more assured now.

Am trying to include another field that allows user to click (view) where it will popup css window and from there show images of that particular row.

Thanks for the help all along, Allan. Appreciate it!