Inserting empty first column breaks serverside processing

zlikowsky

I have an an empty first column for controls (edit,delete etc)

[code]
"aoColumnDefs":[
{"aTargets":[0],"sTitle":"controls","mData":null,"bSortable": false},
{"aTargets":[1],"sTitle":"first","mData":0},
{"aTargets":[2],"sTitle":"second","mData":1},
{"aTargets":[3],"sTitle":"third","mData":2},
...
],
"sAjaxSource": "server_processing.php,
[/code]

Now the sort function doesn't work. Also when I press sort on the last column I get this message: DataTables warning: JSON data from server could not be parsed.

Server returns this
Notice: Undefined offset: 6 in server_processing.php on line 87
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 'asc LIMIT 0, 81' at line 5


ie. this line in server_processing.php:
[code]if ($_GET['bSortable_' . intval($_GET['iSortCol_' . $i])] == "true")
{
$sOrder .= $aColumns[intval($_GET['iSortCol_' . $i])] . "
" . mysql_real_escape_string($_GET['sSortDir_' . $i]) . ", ";
}
[/code]

I tried fixing this by adding an " " first field in $aColumns. This seems to work but now the search function is broken.
When I eneter something in the search field I get this again :
DataTables warning: JSON data from server could not be parsed.

server_processing.php outputs this error:
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 alert_id LIKE '%a%' OR machine_id LIKE '%a%' OR type LIKE '%a%' OR' at line 3



....

zlikowsky

solved:
indexing wasn't right in the filter loop (changing 0 to 1 fixed it)

[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
*/
$sWhere = "";
if (isset($_GET['sSearch']) && $_GET['sSearch'] != "")
{
$sWhere = "WHERE (";
for ($i = 1; $i < count($aColumns); $i++)
{
$sWhere .= $aColumns[$i] . " LIKE '%" . mysql_real_escape_string($_GET['sSearch']) . "%' OR ";
}
$sWhere = substr_replace($sWhere, "", -3);
$sWhere .= ')';
}

/* Individual column filtering */
for ($i = 1; $i < count($aColumns); $i++)
{
if (isset($_GET['bSearchable_' . $i]) && $_GET['bSearchable_' . $i] == "true" && $_GET['sSearch_' . $i] != '')
{
if ($sWhere == "")
{
$sWhere = "WHERE ";
}
else
{
$sWhere .= " AND ";
}
$sWhere .= $aColumns[$i] . " LIKE '%" . mysql_real_escape_string($_GET['sSearch_' . $i]) . "%' ";
}
}
[/code]


the whole concept of working with indexes instead of column names is not good. for example if my empty column was anywhere else but on the first position this wouldn't work. the whole php side of this needs to be thoruoughly rewritten to allow more flexibility. (something I cant be bothered to do now tho)