Update issue (Query Builder Class)

For some reason I'm getting extra back ticks in my update statement.

       $this->db->where('table2acct_id', $acct_id);
       $this->db->where('table1id', $id);
       $this->db->where('enabled', 1);
       $this->db->where('checkin_close_time >= UTC_TIMESTAMP()');
       $this->db->update("table1 JOIN table2 ON table1id= table2id JOIN table3 ON table2id=table3id", $update_data);

The issue is in the update statement. The query returns:
UPDATE `table1 JOIN table2 ON table1id= table2id JOIN table3 ON` `table2id=table3id` SET ...

For some reason there is an extra back tick between my last ON statement.

Thanks in advance.

You need to use join instead.
https://www.codeigniter.com/user_guide/d...lder::join

You need to use join instead.
https://www.codeigniter.com/user_guide/d...lder::join

I tried that it does not work with an update.

You need to use join instead.
https://www.codeigniter.com/user_guide/d...lder::join

Makes sense and I thought so too - but it won't help. The way I interpret the source code of CI_DB_query_builder::update() "join" clauses, created by a call to db->join(...), are not considered when building the update statement.

Okey, my bad. I actually never tested it. Don't know exactly how join works, so here's how you can modify it to disable escaping instead.

https://pastebin.com/A5U7Y0i6
\system\database\DB_query_builder.php

Code:

1788:
public function get_compiled_update($table = '', $reset = TRUE, $escape = NULL)
1793:
if ($this->_validate_update($table, $escape) === FALSE)
1821:
public function update($table = '', $set = NULL, $where = NULL, $limit = NULL, $escape = NULL)
1831:
if ($this->_validate_update($table, $escape) === FALSE)
1863:
protected function _validate_update($table, $escape = NULL)
1872:
$this->qb_from = array($this->protect_identifiers($table, TRUE, $escape, FALSE));

Use it like this:

PHP Code:

$this->db->update('`table1` JOIN `table2` ON `table1id`=`table2id` JOIN `table3` ON `table2id`=`table3id`', $update_data, NULL, NULL, FALSE); 


I couldn't figure out how to disable escaping and it might not be wise to do so anyway.

The solution might be to not use Query Builder and fall back to good old 'query()' with bindings instead.

PHP Code:

$binds = [$valA, $valB, $valC, $acct_id, $id,];

$sql = "UPDATE table1 
    JOIN table2 ON table1id = table2id 
    JOIN table3 ON table2id = table3id 
    SET colA = ?, colB = ?, ColC = ?
    WHERE table2acct_id = ?
    AND table1id = ?
    AND enabled = 1
    AND checkin_close_time >= UTC_TIMESTAMP()";

$result = $this->db->query($sql, $binds);
echo $results ? "It worked": "Did not work"; 


Okey, my bad. I actually never tested it. Don't know exactly how join works, so here's how you can modify it to disable escaping instead.

https://pastebin.com/A5U7Y0i6
\system\database\DB_query_builder.php

Code:

1788:
public function get_compiled_update($table = '', $reset = TRUE, $escape = NULL)
1793:
if ($this->_validate_update($table, $escape) === FALSE)
1821:
public function update($table = '', $set = NULL, $where = NULL, $limit = NULL, $escape = NULL)
1831:
if ($this->_validate_update($table, $escape) === FALSE)
1863:
protected function _validate_update($table, $escape = NULL)
1872:
$this->qb_from = array($this->protect_identifiers($table, TRUE, $escape, FALSE));

Use it like this:

PHP Code:

$this->db->update('`table1` JOIN `table2` ON `table1id`=`table2id` JOIN `table3` ON `table2id`=`table3id`', $update_data, NULL, NULL, FALSE); 


I thought about this but I didn't want to modify the system files.

I couldn't figure out how to disable escaping and it might not be wise to do so anyway.

The solution might be to not use Query Builder and fall back to good old 'query()' with bindings instead.

PHP Code:

$binds = [$valA, $valB, $valC, $acct_id, $id,];

$sql = "UPDATE table1 
 JOIN table2 ON table1id = table2id 
 JOIN table3 ON table2id = table3id 
 SET colA = ?, colB = ?, ColC = ?
 WHERE table2acct_id = ?
 AND table1id = ?
 AND enabled = 1
 AND checkin_close_time >= UTC_TIMESTAMP()";

$result = $this->db->query($sql, $binds);
echo $results ? "It worked": "Did not work"; 


Yes, this was the alternative that I went with. I just wanted to bring this up in case it was a bug.

Thank you both for your responses.