+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Update issue (Query Builder Class) (/showthread.php?tid=71098)
Update issue (Query Builder Class) - ogomez - 07-05-2018
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.
RE: Update issue (Query Builder Class) - jreklund - 07-05-2018
You need to use join instead.
https://www.codeigniter.com/user_guide/database/query_builder.html#CI_DB_query_builder::join
RE: Update issue (Query Builder Class) - ogomez - 07-06-2018
(07-05-2018, 11:39 PM)jreklund Wrote: You need to use join instead.
https://www.codeigniter.com/user_guide/database/query_builder.html#CI_DB_query_builder::join
I tried that it does not work with an update.
RE: Update issue (Query Builder Class) - dave friend - 07-06-2018
(07-05-2018, 11:39 PM)jreklund Wrote: You need to use join instead.
https://www.codeigniter.com/user_guide/database/query_builder.html#CI_DB_query_builder::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.
RE: Update issue (Query Builder Class) - jreklund - 07-06-2018
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);
RE: Update issue (Query Builder Class) - dave friend - 07-06-2018
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";
RE: Update issue (Query Builder Class) - ogomez - 07-06-2018
(07-06-2018, 10:33 AM)jreklund Wrote: 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.
(07-06-2018, 11:44 AM)dave friend Wrote: 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.