如何在编码器中编写包含OR、AND、LIKE的自定义查询 - How to write custom queries in codeigniter that includes OR, AND, LIKE

How to write custom queries in codeigniter that includes OR, AND, LIKE

我有两个表，我必须根据搜索条件获取记录。这是我之前尝试的实际查询，没有使用活动记录。

无活动记录:

$query = $this->db->query("select A.* from A LEFT JOIN B ON A.AId=B.BId where
        (A.Title like '%$searchstring%' OR B.Name like '%$searchstring%') and A.Status='1' and A.Type IN (0) and
        (A.UserId = '$userid' or A.Public='0')  Limit $start,20");

请注意我像这样把括号括起来的
(A.Title like '%$searchstring%' OR B.Name like '%$searchstring%')

实际上，这似乎是工作良好，如果搜索字符串是明文即没有引号。但是如果包含'或'引号，则返回数据库错误

所以我试着用这种方式写活动记录

        $this->db->select("A.*");
        $this->db->from('Bloop as A');
        $this->db->join('Keywords as B', 'A.BloopId=B.BloopId','LEFT');
        $this->db->where_in('A.Type','1');
        $this->db->where('A.Status','1');
        $this->db->like('A.Title', $string);
        $this->db->OR_like('B.Name', $string);
        $this->db->where('A.UserId',$userid);
        $this->db->or_where('A.Public',0);
        $this->db->limit(20,$start);
        $query = $this->db->get();

但是返回不正确的结果，原因是OR条件没有括在括号中。

有人能纠正我的问题吗?

谢谢. .

希望对您有所帮助。

    $this->db->select("A.*");
    $this->db->from('Bloop as A');
    $this->db->join('Keywords as B', 'A.BloopId=B.BloopId','LEFT');
    $this->db->where_in('A.Type','1');
    $this->db->where('A.Status','1');
    $this->db->where("(A.Title LIKE '%$string%' OR B.Name LIKE '%$string%')");
    $this->db->where("('A.UserId' = '$userid' OR 'A.Public' = 0)");
    $this->db->limit(20,$start);
    $query = $this->db->get();

您需要尝试使用false

$query = $this->db->query("select A.* from A LEFT JOIN B ON A.AId=B.BId where
    (A.Title like '%$searchstring%' OR B.Name like '%$searchstring%') and A.Status='1' and A.Type IN (0) and
    (A.UserId = '$userid' or A.Public='0')  Limit $start,20",false);

注意，传递第二个参数为false。

您也可以使用CI方式:

$like   = "( A.Title like '%".$string."%' OR B.Name like '%".$string."%' )";
$this->db->select("A.*");
$this->db->from('Bloop as A');
$this->db->join('Keywords as B', 'A.BloopId=B.BloopId','LEFT');
$this->db->where_in('A.Type','1');
$this->db->where('A.Status','1');
//$this->db->like('A.Title', $string);
//$this->db->OR_like('B.Name', $string);
$this->db->where($like, false);
$this->db->where('A.UserId',$userid);
$this->db->or_where('A.Public',0);
$this->db->limit(20,$start);
$query = $this->db->get();