phpmysqlcodeigniteractiverecordquoting

CodeIgniter active record query is inappropriately wrapping an addition expression in identifier quotes

I'm trying to get the SQL format below

SELECT *
FROM `ci_nest`
WHERE `lft` > 9
  AND `rgt` < 28
  AND `rgt` = `lft` + 1
ORDER BY `lft`

However, Codeigniter 3 is inserting some backticks at the wrong place.

My code:

$this->db->where($leftcol . ' > ' . $leftval . ' AND ' . $rightcol . ' < ' . $rightval);
$this->db->where($rightcol . " = " . $leftcol . " +1");
$this->db->order_by($leftcol);
$query = $this->db->get($this->table_name);

The rendered SQL:

SELECT *
FROM `ci_nest`
WHERE `lft` > 9 AND `rgt` < 28
AND `rgt` = `lft` `+1`
ORDER BY `lft`

As you can see, the intended AND `rgt` = `lft` + 1 is being formatted incorrectly because the +1 is being quoted as an identifier. How can this be avoided?

Solution

  • Disable the backticks by the optional 3rd parameter and create them by your own.

    $this->db->where($rightcol, '`'.$leftcol.'`+1', FALSE);

    Or with double quotes, seems better.

    $this->db->where($rightcol, "`$leftcol`+1", FALSE);