search function not working (server side processing)

guihamel

Hi,

I've implemented a PHP/mysqli server side solution that is working almost perfectly fine. The only thing, for now, that isn't working is the search box.

I've taken the code from there -> http://datatables.net/development/server-side/php_mysqli

I've also searched in the forums here and found some potential solutions/workaround yet none were really what I was looking for.

Here's the 2 JSON response I'm getting

#1 When table is first generated
[code]{
"sEcho": 2,
"iTotalRecords": "19",
"iTotalDisplayRecords": "16",
"aaData": [
[
"2012-12-18 12:18:21",
"TypeC",
"123123",
"321321 ..00",
"Edit"
],
[
"2012-12-18 12:14:04",
"TypeC",
"555888",
"987654321",
"Edit"
],
[
"2012-12-06 13:10:05",
"TypeA",
"222222",
"222222",
"Edit"
],
[
"2012-12-06 13:08:32",
"TypeA",
"111111",
"111111",
"Edit"
],
[
"2012-12-06 13:07:25",
"TypeA",
"789789",
"789789",
"Edit"
],
[
"2012-12-06 13:03:09",
"TypeA",
"456456",
"456456",
"Edit"
],
[
"2012-12-06 13:02:09",
"TypeA",
"123123",
"123123",
"Edit"
],
[
"2012-11-30 11:47:25",
"TypeA",
"345345345",
"345345345345",
"Edit"
],
[
"2012-11-29 17:58:12",
"TypeA",
"555555",
"guillaume",
"Edit"
],
[
"2012-11-29 14:24:33",
"TypeC",
"654654",
"jkghlk jhkl",
"Edit"
],
[
"2012-11-29 14:04:55",
"tres",
"5555555",
"hey",
"Edit"
],
[
"2012-11-28 14:34:39",
"TypeD",
"9854542",
"qwerty",
"Edit"
],
[
"2012-11-27 14:55:25",
"TypeC",
"456456",
"I can more than likely write a big comment. I wonder how long can I go.",
"Edit"
],
[
"2012-11-27 14:54:23",
"TypeD",
"951753",
"c'est un autre commentaire",
"Edit"
],
[
"2012-11-12 15:16:27",
"TypeA",
"123456",
"This is a test comment 123123",
"Edit"
],
[
"2012-11-05 15:22:37",
"TypeC",
"654321",
"This is another comment",
"Edit"
]
]
}
[/code]

and #2, when I get the error (please note that the error appears the minute I type 1 character in the search box):
[quote]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 '(`created_date` LIKE '%t%' OR `name` LIKE '%t%' OR `reference` LIKE '%t%' OR `co' at line 2
[/quote]

From my investigation, it appears that the code generating the error is the following:
[code]/**
* Filtering
* NOTE this does not match the built-in DataTables filtering which does it
* word by word on any field. It's possible to do here, but concerned about efficiency
* on very large tables, and MySQL's regex functionality is very limited
*/
$iColumnCount = count($aColumns);

if ( isset($input['sSearch']) && $input['sSearch'] != "" ) {
$aFilteringRules = array();
for ( $i=0 ; $i<$iColumnCount ; $i++ ) {
if ( isset($input['bSearchable_'.$i]) && $input['bSearchable_'.$i] == 'true' ) {
$aFilteringRules[] = "`".$aColumns[$i]."` LIKE '%".$db->real_escape_string( $input['sSearch'] )."%'";
}
}
if (!empty($aFilteringRules)) {
$aFilteringRules = array('('.implode(" OR ", $aFilteringRules).')');
}
}
[/code]

Any hints or help that would help me resolve this will be greatly appreciated. Let me know if there is any more information I can provide to help with this matter.

Cheers

allan

> "iTotalRecords": "19"

That and it's friend `iTotalDisplayRecords` should both be integers - not strings.

> and #2, when I get the error

And what does the JSON look like when you get that error?

Would be very useful if you could link to a test page please: http://datatables.net/forums/discussion/12899/post-test-cases-when-asking-for-help-please-read

One other thing - the response has 16 records in it - did DataTables request 16 or more for the page? The default is 10, so unless you've altered it, there is something wrong there.

Allan

guihamel

As far as the iTotalRecords and iTotalDisplayRecords, I'd be tempted to say that they are integers. I've taken the code from the example on the site and didn't modify that section.

Here's the section about it:
[code]
/**********************************
* Data set length after filtering
**********************************/
$sQuery = "SELECT FOUND_ROWS()";
$rResultFilterTotal = $db->query( $sQuery ) or die($db->error);
list($iFilteredTotal) = $rResultFilterTotal->fetch_row();

/************************
* Total data set length
*************************/
$sQuery = "SELECT COUNT(`".$sIndexColumn."`) FROM `".$sTable."`";
$rResultTotal = $db->query( $sQuery ) or die($db->error);
list($iTotal) = $rResultTotal->fetch_row();


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

Upon further reading on the internet, i found -> http://php.net/manual/en/function.list.php which seems to confirm that they aren't integers.
- Would that be the reason why my search is not working?
- How would you recommend modifying the code at make it work?

As far as the amount of records, I think I had changed the amount of values to be shown with the drop down. After hitting refresh, i get the following at the bottom of the table
[quote]Showing 1 to 10 of 16 entries (filtered from 19 total entries)[/quote]

Lastly, I understand that a working site example is best to troubleshoot, but in my situation, it's hard as the code relies on LDAP data to select which data has to be displayed (the table only shows the records created by the "logged in" user).

I could duplicate it without the LDAP integration but it would require a lot of work.

guihamel

Forgot to mention too that the minute I put a value in the search box, no JSON is sent back. Here's a screenshot of it.

http://hip-services.com/json_error.PNG

Also, don't know if it's important to know but the columns are taken from 2 different tables.
- created, reference & comments are from the table 'tickets'
- name is from the table 'type'