我的模型中有以下代码:
$sql = "SELECT t1.*, t2.* FROM Table1 t1 INNER JOIN Table2 t2 ON t1.id = t2.id AND t2.title IN (?) AND t1.type = ? ORDER BY t1.id";
$q = $this->db->query( $sql, array( $filter, $type ) );
问题是这个查询在以下情况下工作正常:
$filter = 'a';
但如果出现以下情况则不返回任何内容:
$filter = "'a','b','c','d'";
对于第二种情况,我可以看到 CI 正在转义 $filter,如下所示:
SELECT t1.*, t2.* FROM Table1 t1 INNER JOIN Table2 t2 ON t1.id = t2.id AND t2.title IN ('\'a\',\'b\',\'c\',\'d\'') AND t1.type = ? ORDER BY t1.id
这是正确的行为。 CI 在将引号发送到 MySQL 之前对其进行转义。您可能应该使用一个数组作为过滤器并构造类似的东西(未经测试;))
$filter = array('a','b','c');
$sql = "SELECT t1.*, t2.*
FROM Table1 t1
INNER JOIN Table2 t2
ON t1.id = t2.id
AND t2.title IN (". implode(',', array_fill(0, count($filter), '?')).")
AND t1.type = ?
ORDER BY t1.id";
//edit: check if $filter is not an array ( when it is a single value string )
$filter = is_array( $filter ) ? $filter : array( $filter );
$q = $this->db->query( $sql, array_merge( $filter, array( $type ) ) );
您还应该稍微重写一下查询:
$sql = "SELECT t1.*, t2.*
FROM Table1 t1
INNER JOIN Table2 t2
ON t1.id = t2.id
WHERE
t2.title IN (". implode(',', array_fill(0, count($filter), '?')).")
AND t1.type = ?
ORDER BY t1.id";
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)