如何使用where子句

eqzww0vc 于 2021-06-25 发布在 Mysql

关注(0)|答案(3)|浏览(300)

codeigniter活动记录查询给我错误。如何把听起来像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;
  }

当我回显查询时，这将提供查询。我正在寻找技术，我需要获得技术等。

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

每件事都很好，但当我把听起来像给我错误。

sql mysql codeigniter activerecord

来源：https://stackoverflow.com/questions/50287224/how-to-use-sounds-like-in-where-clause

3条答案

按热度按时间

31moq8wy1#

在这里把事情分组可能是最好的
您可以尝试以下方法

$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

赞(0）回复(0）举报 2021-06-25

ds97pgxw2#

从：db\u query\u builder.php中，可以看到第三个参数决定是否转义。

public function or_where($key, $value = NULL, $escape = NULL){

}

所以告诉他不要逃避

$this->db->or_where("name SOUNDS LIKE '$k'", NULL, FALSE);

赞(0）回复(0）举报 2021-06-25

c8ib6hqw3#

你能用having条款来表达你的订单吗？
有条款：https://www.w3schools.com/sql/sql_having.asp

赞(0）回复(0）举报 2021-06-25

我来回答

如何使用where子句

3条答案

相关问题

热门标签

最新问答