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;
}
当我回显查询时,这给了我查询。我正在寻找技术,我需要获得技术技术等。
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 放在一起时,它给我错误。
它可能是最好的在这里分组的东西
您可以尝试以下操作
$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
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句