PHP codeIgniter 查询区分大小写，想要使其不敏感

Question

我知道只有使用 LOWER 和 strlower 才能解决问题。但我面临的问题是语法，因为我正在使用数据表库，并且他们没有将其设置为不敏感，所以我需要做很少的更新。

上次它对我有用

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

但现在主要查询是

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

所以我尝试这样

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

但是我不擅长这个符号，请任何人都可以帮助我，因为我遇到了错误..

此行来自以下函数的 Ignited-dataTables 库。

 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

问题已解决。搜索有效，但现在不敏感。新代码。

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]);
}

我改变了

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

进入

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

还有已更改

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

进入

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