MySQL Error when add colum and make a seach (error near LIKE)

[Nounours13]

When we add colum ($this->datatables->add_column) and we make a search they are an error of datatable (they are a LIKE in SQL request who have no colum name).

I've modified the Library at line 311 like this:

  if($sSearch != '')
   {
    for($i = 0; $i < count($mColArray); $i++)
     {
       if($mColArray[$i]['searchable'] == 'true' )
        {
          if($this->check_cType())
           {
            $sWhere .= $this->select[$mColArray[$i]['data']] . " LIKE '%" . $sSearch . "%' OR ";
           }
          else
           {

            if (array_key_exists($i,$this->columns))
             {

              $sWhere .= $this->select[$this->columns[$i]] . " LIKE '%" . $sSearch . "%' OR ";
             }
           }
        }
     }
   }

It's a soluce ?
Tell me if you integer this part of your marvelous Library.

Cordialy

[ptondereau]

Hi,

When you write the JS code and you declare your columns, you need to disable ordering and searchable :

columns: [
            {data: "col1"},
            {data: "col2"},
            {data: "action", orderable: false, searchable: false}
        ],

[tiegosilveira]

No need to change anything feathers add columns: in view

[iMezied]

trying this and the issue is the same
codeigniter error : Message: Undefined index:
MySQL error :
Error Number: 1064

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 '%8d9%' ) ORDER BY found desc LIMIT 25' at line 5

SELECT * FROM (report_info) WHERE userId = '1' AND ( LIKE '%8d9%' ) ORDER BY found desc LIMIT 25

@tiegosilveira thank you, but what do you mean by adding columns in view ?
here is my snippet for adding columns

{"data": "url",
"sortable": false,
"searchable": false },
{"data": "userID",
"sortable": false,
"searchable": false },
{"data": "image",
"sortable": false,
"searchable": false },

[ptondereau]

I think he means by adding column in html side.
Can you give us your PHP part ?

[iMezied]

thanks @rooferz for reply
here is PHP code (controller):

    $this->load->library('Datatables');
    $this->datatables->select('*', FALSE);
    $this->datatables->where(array('userId' => $userID, 'filter' => $truefilter));
    $this->datatables->from('report_view');

    $this->output
            ->set_content_type('application/json')
            ->set_output($this->datatables->generate());

[ptondereau]

Be sure in your database there are filter and userId columns

[ptondereau]

Becareful of not having a single one colunm to be searchable. If server side is true you all search will be on server side and not with DOM elements.

[iMezied]

yes, they exist
I created mysql view contains of two joint tables with exact aliases "userID" and "filter"
already the table load perfect when loading page that means the query worked fine
but when search phase loading result an error
and return columns defined as searchable as undefined index

[iMezied]

after tracing code I replace this line 315

$sWhere .= $this->select[$mColArray[$i]['data']] . " LIKE '%" . $sSearch . "%' OR ";

with this:

$sWhere .= $mColArray[$i]['data'] . " LIKE '%" . $sSearch . "%' OR ";

and now search works perfectly

[CaioIbraim]

private function get_filtering()
{
$mColArray = $this->ci->input->post('columns');
$sWhere = '';
$search = $this->ci->input->post('search');
$sSearch = $this->ci->db->escape_like_str(trim($search['value']));
$columns = array_values(array_diff($this->columns, $this->unset_columns));
if($sSearch != '')
for($i = 0; $i < count($mColArray); $i++)
if ($mColArray[$i]['searchable'] == 'true' && !array_key_exists($mColArray[$i]['data'], $this->add_columns))
if($this->check_cType())
$sWhere .= $this->select[$mColArray[$i]['data']] . " ILIKE '%" . $sSearch . "%' OR ";
else
$sWhere .= ($this->select[$this->columns[$i]] === NULL)?"":$this->select[$this->columns[$i]] . "::TEXT ILIKE '%" . $sSearch . "%' OR ";

  $sWhere = substr_replace($sWhere, '', -3);
  if($sWhere != '')
    $this->ci->db->where('(' . $sWhere . ')');
  // TODO : sRangeSeparator
  foreach($this->filter as $val)
    $this->ci->db->where($val[0], $val[1], $val[2]);
}