Range filtering issue when using a memcached layer with datatables

gordyr

I'm adding a 'memcached' layer to my application and have come accross an extremely odd problem that I am at a complete loss to explain.

Firstly I will explain how i am using memcached:-

I am assigning a unique key based upon the parameters that are sent to the server when datatables fires off it's get requests.

So for instance if 'sSearch=hello' and 'sSearch_9=1~60' are sent to the server the unique string that is generated by my server-side script will be a hash version of:-

searchKey_hello.1~60

This way it means that all queries are individually cahed and can be recalled later.

This is working perfectly accross the board with one exception, my custom range filter built in to the server side script.

The bizarre part of the problem is that the JSON that gets returned by memcached is actually correct even though the table doesn't update. This is what I cannot understand. Also I have noticed it seems to only occur when using fnDraw();

For e.g.

My range filter is set to work from ranges 0 - 300 and filters a single column.

The server side code I am using for this is:-

[code]
$columnFilterValue = mysql_real_escape_string($_GET['sSearch_' . $i]);
// check for values range
$rangeSeparator = "~";
if (!empty($rangeSeparator) && strstr($columnFilterValue, $rangeSeparator)) {
// get min and max

$columnFilterRangeMatches = explode('~', $columnFilterValue);

// get filter
if (empty($columnFilterRangeMatches[0]) && empty($columnFilterRangeMatches[1]))
$sWhere .= " 0 = 0 ";
else if (!empty($columnFilterRangeMatches[0]) && !empty($columnFilterRangeMatches[1]))
$sWhere .= $aColumns[$i] . " BETWEEN '" . $columnFilterRangeMatches[0] . "' and '" . $columnFilterRangeMatches[1] . "' ";
else if (empty($columnFilterRangeMatches[0]) && !empty($columnFilterRangeMatches[1]))
$sWhere .= $aColumns[$i] . " < '" . $columnFilterRangeMatches[1] . "' ";
else if (!empty($columnFilterRangeMatches[0]) && empty($columnFilterRangeMatches[1]))
$sWhere .= $aColumns[$i] . " > '" . $columnFilterRangeMatches[0] . "' ";
} else {
$sWhere .= $aColumns[$i] . " LIKE '%" . $columnFilterValue . "%' ";
}
}
[/code]

And the jquery code I am using to render a jqueryui slider and perform the filtering is as follows:-

[code]
$('#Slider').slider({
orientation: 'horizontal',
range: true,
animate: 200,
min: 0,
max: 300,
step: 1,
values: [ $tempoFrom, $tempoTo ],
stop: function(event, ui){//This event is triggered when the user stops sliding.
$('.table-row').draggable( "destroy" );

if ($("#example").length == 0){
tableCheck();
}
else {
$('#example').dataTable().fnDraw();
}

},
//values: [min_tempo, max_tempo],
slide: function(event,ui) {
$lower.val(ui.values[0]);
$upper.val(ui.values[1]);
$lower.text(ui.values[1]);
$upper.text(ui.values[0]);
}
});

$lower.change(function () {
var low = $lower.val(),
high = $upper.val();
low = Math.min(low, high);
$lower.val(low);

$slider.slider('values', 0, low);
});

$upper.change(function () {
var low = $lower.val(),
high = $upper.val();
high = Math.max(low, high);
$upper.val(high);
$slider.slider('values', 1, high);
});

[/code]

Now... If i completely flush the contents of memcached and perform a filtering action with my range filter, lets say 20-100, it works fine the first time. The unique key is generated and the JSON results are compressed by zlib and stored in the memcached buffer.

If i then move the slider somewhere else it again filters correctly.

Finally however, If I mvoe the slider back to "20-100" the table stays exactly the same. Even though I can see in firebug that the correct parameters are being sent and the correct JSON is returning!

It is as though the table just is refusing to redraw after the retrieval of the JSON. As I said I am at a complete loss to explain this and the only thing that I can think of is that some how fnDraw isn't firing correctly. But how could this be possible when the JSON coming back is correct? Memcached should have no effect whatsoever. Or am I missing something?

Incidentally, if I perform fnDestroy and then reinitialise the table instead, I still retrieve the results from memcached giving me sub 50ms round trips on a hugely complex and massive table and everthing works as expected.

Any ideas at all on what could be causing tis, and indeed, how to get around it without destroying the table everytime I perform a range filtering action?

Many thanks all!

EDIT: I've just been investigating further and seems that the problem is definitely client side. The processing animation that is hidden on fnDrawCallback stays visible when I retrieve the JSON from memcached. Again I am at a loss as to why this would be but clarly datatables is not finishing it's job for some reason when the results come from memory rather than the database, despite the GET request retrieval being completely identical. Just FYI, there are no javascipt errors whatsoever when this problem occurs.

EDIT 2:

Okay after more investigations I think the problem below 'may' be to do with the sEcho parameter. Since I am retrieving a cached response the sEcho value will not match the request. What I don't understand is why it works with everything else other than my range filter. I'm thoroughly confused!

gordyr

Okay... I have solved the problem by removing the sEcho line in its entirety out of the server side script.

Allan... Could you explain what the consequence of me removing this might be? And also.... Can you think of any other way around it?

I have added my full server side code just in case you want to take a peek. It also might be useful for anyone else wanted to implement memcached.

[code]
<?php

define('WP_USE_THEMES', false);
require('../wp-load.php');


$author = wp_get_current_user();
$userid = $author->ID;


/* memcached key variables */

$textSearch = mysql_real_escape_string($_GET['sSearch']);
$rangeSearch = mysql_real_escape_string($_GET['sSearch_9']);


/* Create an instance from Memcache loaded Class */
$memcache = new Memcache;

/* Establish a connection to the Memcached Server. */
$memcache->connect('localhost', 11211) or die ("Could not connect");

/* Set the unique key (I'm using the Query like an unique key) */
$key = md5("songsearch_$textSearch.$rangeSearch");

/* Try to get the query result from the cache */
$get_result = $memcache->get($key);

/* If the cached data found use it */
if ($get_result) {
//$get_result['aaData'][] = $row;

//echo "Data Pulled From Cache";
echo json_encode($get_result);
}

/* Else fetch data from database (I'm using MySQL) */
else
{




/*
* 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( 'song_name', 'artist_band_name', 'author', 'song_artwork', 'song_file', 'genre', 'song_description', 'uploaded_time', 'emotion', 'tempo', 'songsID', 'user', 'happiness', 'instruments', 'similar_artists', 'play_count', 'projects_count', 'rating', 'ratings_count', 'voted');

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

/* DB table to use */
$sTable = "wp_dbt_songs";
$sTable2 = "wp_project_songs";
$sTable3 = "wp_song_ratings";
$sTable4 = "wp_artists_favourites";


/* Database connection information */
$gaSql['user'] = "songbanc_cms";
$gaSql['password'] = "Z.q4CrNSCoEX";
$gaSql['db'] = "songbanc_cms";
$gaSql['server'] = "localhost";


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 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

gordyr

Code continued... as it was too long...

[code]


/*
* Output
*/
$output = array(
/*"sEcho" => intval($_GET['sEcho']),*/
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => array()
);




while ( $aRow = mysql_fetch_array( $rResult ) )
{
$row = array();
for ( $i=0 ; $iset($key, $output, MEMCACHE_COMPRESSED, time() + 24*60*60*365);

}
//echo "Data Pulled from the Database";
echo json_encode( $output );



/* Use results */
//echo json_encode($get_result);

}

/* Close Memcached server connection */
$memcache->close();
?>[/code]