mysql - 如何在 where 子句中使用 SOUNDS LIKE

Question

Codeigniter 事件记录查询给我错误。如何将 SOUNDS LIKE 放入 where 子句中。

function _search($type, $q, $qes, $sort = null, $start = 0) {
    $type = strtolower($type);
    $q = strtolower($q);


    $this->db->select("*");
    $this->db->from("books");
    $this->db->where("SOUNDEX(name) IN({$q})");
    foreach($qes as $k){
        $this->db->or_where("name SOUNDS LIKE '$k'");
    }
    foreach($qes as $k){
        $this->db->or_where("name LIKE '%$k%'");
    }
    $this->db->where("status", 1);
    if ($type != NULL) {
        $this->db->where("LOWER(type)", $type);
    }
    //$this->db->like("LOWER(name)", $q);

    $this->db->limit(BWK_MAX_BOOK_SIZE, $start);
    switch ($sort) {
        case 1:
            break;
        case 2:
            $this->db->order_by("sellingPrice", "ASC");
            break;
        case 3:
            $this->db->order_by("sellingPrice", "DESC");
            break;
        case 4:
            $this->db->order_by("created", "DESC");
            break;
  }

当我回显查询时，这会给我查询。我正在寻找technologi我需要获得技术技术等。

SELECT * FROM `books` WHERE SOUNDEX(name) IN('t254') OR `name` `SOUNDS` LIKE 'technolog' OR `name` LIKE '%technologi%' AND `status` = 1 AND LOWER(type) = 'school' LIMIT 50

出现错误

  You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '`SOUNDS` LIKE 'technolog' OR `name` LIKE '%technolog%' AND `status` = 1 AND LOWE' at line 4

一切正常，但是当我输入“SOUNDS LIKE”时，它给了我错误。

Answer 1

这里可能是最好的分组方式

您可以尝试以下方法

$this->db
    ->select('*')
    ->from('books')
    ->where_in('SOUNDEX(name)', $q,  NULL);

if (is_array($qes) && count($qes) > 0)
{
    $this->db->group_start();
    foreach($qes AS $k)
    {
        $this->db->or_group_start();
            $this->db
                ->where('name SOUNDS LIKE '.$this->db->escape($k), NULL, false)
                ->or_like('name', $k);

        $this->db->group_end();
    }
    $this->db->group_end();
}

if (!is_null($type))
{
    $this->db->where('LOWER(type)', $type);
}

switch ($sort) {
    case 1:
        break;
    case 2:
        $this->db->order_by("sellingPrice", "ASC");
        break;
    case 3:
        $this->db->order_by("sellingPrice", "DESC");
        break;
    case 4:
        $this->db->order_by("created", "DESC");
        break;
}

echo $this->db
    ->where('status',1)
    ->limit(BWK_MAX_BOOK_SIZE, $start)
    ->get_compiled_select();

这会产生类似的语句

SELECT *
FROM `books`
WHERE SOUNDEX(name) IN('t254') AND
(
    (
        name SOUNDS LIKE 'technologi' OR 
        `name` LIKE '%technologi%' ESCAPE '!'
    ) 
    OR 
    (
        name SOUNDS LIKE 'whatever' OR 
        `name` LIKE '%whatever%' ESCAPE '!'
    )
)
AND `status` = 1
AND LOWER(type) = 'school' 
LIMIT 50