datatable:make serverside script for filter each column

Question

i want to make my table can filtering in each column. i get client side script from DataTable.net. To make this work, i must make change at server side script for this datatable. My table fields is: Line, Model_name, Version, Lot, Serial_number, ID_number, Lot_no_S_Serial, and Prod_date.

i have tried to syncronizing each column to this server script, but i always get an error. this the following script:

sSearch:
bEscapeRegex:true
sSearch_0:
bEscapeRegex_0:true
bSearchable_0:true
sSearch_1:
bEscapeRegex_1:true
bSearchable_1:true
sSearch_2:
bEscapeRegex_2:true
bSearchable_2:true   //data array same until sSearch_7

---

EDIT

and this the query:

$sWhere = "";
if (postVar('sSearch') !="" )
{
  $sWhere = " WHERE Line LIKE '%".mysql_real_escape_string( $_POST['sSearch'] )."%'  ";
}
if (postVar('sSearch_0') !="")
{
  $sWhere = " AND Line LIKE '".mysql_real_escape_string( $_POST['sSearch_0'])."' ";
}
if (postVar('sSearch_1') !="") 
{ 
  $sWhere = " AND Model_name LIKE '%".mysql_real_escape_string( $_POST['sSearch_1'])."%' ";

//until sSearch_7

i'm getting error at this query:

error: "Error occuered during query execution: (): 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 'AND Line LIKE '2' ORDER BY Model_name desc LIMIT 0, 10' at line 1";

Answer 1

WHERE * LIKE is incorrect.

You need to provide a valid column name in place of *

And the PHP syntax error is because:

$sWhere = " AND Line LIKE '%".mysql_real_escape_string( $_POST['sSearch_0']."%' ";

has a missing ) to close the mysql_real_escape_string function cal

Answer 2

You code has several errors:

1. I doubt that "* LIKE" is valid SQL syntax. You should define a single field name here. If postVar('sSearch') equals "" you will also fail to add the "WHERE" to your query.

   if (postVar('sSearch') !="" )
   {
            $sWhere = " WHERE * LIKE '%".mysql_real_escape_string( $_POST['sSearch'] )."%'  ";
   }
   

2. You're missing a closing paranthesis here after mysql_real_escape_string( ..

   if (postVar('sSearch_0') !="")
   {
           $sWhere = " AND Line LIKE '%".mysql_real_escape_string( $_POST['sSearch_0']."%' ";
   }
   

Answer 3

you have to chain your queries.
while you are replacing $sWhere with each further condition.
you can use concatenation (.= instead of =), but let me suggest to use array for this:

$aWhere = array();
if (postVar('sSearch')) {
  $aWhere[] = "Line LIKE '%".mysql_real_escape_string( $_POST['sSearch'] )."%'";
}
if (postVar('sSearch_0'))
{
  $aWhere[] = "Line LIKE '".mysql_real_escape_string($_POST['sSearch_0'])."'";
}
if (postVar('sSearch_1')) 
{ 
  $aWhere = "Model_name LIKE '%".mysql_real_escape_string( $_POST['sSearch_1'])."%'";
}
//and so on
if (count($aWhere)) $where="WHERE ".implode(' AND ',$aWhere);
$query="select * from table $where";
mysql_query($query) or trigger_error(mysql_error().": ".$query);

the last line of this code will print out not only error message but also query itself which will be extremely helpful