PHP codeIgniter query is case sensitive, want to make it insensitive

Question

i know only using LOWER and strlower can fix the problem. but problem i am facing is of syntax as i am using datatables library and they have not set it for insensitive so i need to do little update.

last time it worked for me with

like('LOWER(' .$field. ')', strtolower($value));

but now the main query is

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

so i tried like this

$sWhere .= $this->select[."LOWER(".$mColArray[$i].")".] . " LIKE '%" . strtolower($sSearch) . "%' OR ";

but i am not good with this notations, please can anyone help me out, cuz i get error with this..

This line is from the Ignited-dataTables Library from below function.

 protected function get_filtering()
    {
        if($this->check_mDataprop())
            $mColArray = $this->get_mDataprop();
        elseif($this->ci->input->post(mysql_real_escape_string('sColumns'))){
            $mColArray = explode(',', $this->ci->input->post('sColumns'));
            $mColArray = array_filter($mColArray);
            if(empty($mColArray)){
                $mColArray = $this->columns;
            }
        }
        else
            $mColArray = $this->columns;

        $sWhere = '';
        $sSearch = mysql_real_escape_string($this->ci->input->post('sSearch'));
        $mColArray = array_values(array_diff($mColArray, $this->unset_columns));
        $columns = array_values(array_diff($this->columns, $this->unset_columns));

        if($sSearch != '')
            for($i = 0; $i < count($mColArray); $i++)
                if($this->ci->input->post('bSearchable_' . $i) == 'true' && in_array($mColArray[$i], $columns))
                    $sWhere .= $this->select[$mColArray[$i]] . " LIKE '%" . $sSearch . "%' OR ";

        $sWhere = substr_replace($sWhere, '', -3);

        if($sWhere != '')
            $this->ci->db->where('(' . $sWhere . ')');

        for($i = 0; $i < intval($this->ci->input->post('iColumns')); $i++)
        {
            if(isset($_POST['sSearch_' . $i]) && $this->ci->input->post('sSearch_' . $i) != '' && in_array($mColArray[$i], $columns))
            {
                $miSearch = explode(',', $this->ci->input->post('sSearch_' . $i));

                foreach($miSearch as $val)
                {
                    if(preg_match("/(<=|>=|=|<|>)(\s*)(.+)/i", trim($val), $matches))
                        $this->ci->db->where($this->select[$mColArray[$i]].' '.$matches[1], $matches[3]);
                    else
                        $this->ci->db->where($this->select[$mColArray[$i]].' LIKE', '%'.$val.'%');
                }
            }
        }

        foreach($this->filter as $val)
            $this->ci->db->where($val[0], $val[1], $val[2]);
    }

Answer 1

Problem Solved. Search works and is now insensitive. New Code.

protected function get_filtering()
{
    if($this->check_mDataprop())
        $mColArray = $this->get_mDataprop();
    elseif($this->ci->input->post(mysql_real_escape_string('sColumns'))){
        $mColArray = explode(',', $this->ci->input->post('sColumns'));
        $mColArray = array_filter($mColArray);
        if(empty($mColArray)){
            $mColArray = $this->columns;
        }
    }
    else
        $mColArray = $this->columns;

    $sWhere = '';
    $sSearch = mysql_real_escape_string($this->ci->input->post('sSearch'));
    $mColArray = array_values(array_diff($mColArray, $this->unset_columns));
    $columns = array_values(array_diff($this->columns, $this->unset_columns));

    if($sSearch != '')
        for($i = 0; $i < count($mColArray); $i++)
            if($this->ci->input->post('bSearchable_' . $i) == 'true' && in_array($mColArray[$i], $columns))
                $sWhere .= "UPPER(".$this->select[$mColArray[$i]] . ") LIKE '%" . strtoupper($sSearch) . "%' OR ";

    $sWhere = substr_replace($sWhere, '', -3);

    if($sWhere != '')
        $this->ci->db->where('(' . $sWhere . ')');

    for($i = 0; $i < intval($this->ci->input->post('iColumns')); $i++)
    {
        if(isset($_POST['sSearch_' . $i]) && $this->ci->input->post('sSearch_' . $i) != '' && in_array($mColArray[$i], $columns))
        {
            $miSearch = explode(',', $this->ci->input->post('sSearch_' . $i));

            foreach($miSearch as $val)
            {
                if(preg_match("/(<=|>=|=|<|>)(\s*)(.+)/i", trim($val), $matches))
                    $this->ci->db->where($this->select[$mColArray[$i]].' '.$matches[1], $matches[3]);
                else
                    $this->ci->db->where('UPPER('.$this->select[$mColArray[$i]].') LIKE', '%'.strtoupper($val).'%');
            }
        }
    }

    foreach($this->filter as $val)
        $this->ci->db->where($val[0], $val[1], $val[2]);
}

i Changed

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

in to

$sWhere .= "UPPER(".$this->select[$mColArray[$i]] . ") LIKE '%" . strtoupper($sSearch) . "%' OR ";

And Changed

$this->ci->db->where($this->select[$mColArray[$i]].' LIKE', '%'.$val.'%');

in to

$this->ci->db->where('UPPER('.$this->select[$mColArray[$i]].') LIKE', '%'.strtoupper($val).'%');

Answer 2

With MySQL

• Using **_bin, means using the binary version of ** (You are using utf8_bin, which is the utf8 binary collation) forces MySQL to search case-sensitive

-Using anything else with MySQL is case-insensitive (there are some differences between all the collations).

In your case, you should use utf8_general_ci if you operate "general" characters. Sidenote : With this collation, the German ß would become "ss" in a search query (same goes for other special characters).

However, if you want the entire column/table to be utf8_bin (case-sensitive) BUT you still want to do a single query case-insensitive, you can specify it in the query

SELECT * FROM table WHERE name LIKE 'something' COLLATE utf8_general_ci

(If you change the collation, you need to change the column itself, not only the table)

To alter a column :

ALTER TABLE tblname ALTER COLUMN name varchar(100) COLLATE utf8_general_ci

Answer 3

TRY including third parameter as FALSE in where condition

For example:-

$this->ci->db->where($this->select[$mColArray[$i]].' LIKE', '%'.$val.'%',FALSE);