+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: SQL Update problem (inner join) (/showthread.php?tid=8047)
SQL Update problem (inner join) - El Forum - 05-03-2008
[eluser]Unknown[/eluser]
Hi all,
I'm just getting started with CI and having an issue with converting an SQL statement to Active Record calls.
I have two tables involved in this query, boards and servers. Boards has a last_modified column I want to get/set. The user supplies board and server name, each is stored in their own table and linked with a FK (so an inner join is required to check the server is correct for the supplied board before the set/get can play with the boards table).
The select function works fine:
Code:
function get_last_modified($board, $server) {
/*
SELECT
boards.last_modified
FROM
boards
INNER JOIN
servers
ON
(
boards.server_id = servers.id
)
WHERE
boards.name = '$board'
AND servers.name = '$server' ;
*/
$this->db->select('boards.last_modified');
$this->db->from('boards');
$this->db->join('servers', 'boards.server_id = servers.id', 'inner');
$this->db->where('boards.name', $board);
$this->db->where('servers.name', $server);
$this->db->limit(1);
$query = $this->db->get();
return $query->result();
}However my update function complains:
Code:
function set_last_modified($board, $server, $last_modified) {
/*
UPDATE
boards
INNER JOIN
servers
ON
(
boards.server_id = servers.id
)
SET
boards.last_modified = '$last_modified'
WHERE
boards.name = '$board'
AND servers.name = '$server';
*/
$this->db->join('servers', 'boards.server_id = servers.id', 'inner');
$this->db->where('boards.name', $board);
$this->db->where('servers.name', $server);
$this->db->set('boards.last_modified', $last_modified);
$this->db->update('boards');
}This throws back:
'Unknown column 'servers.name' in 'where clause'
UPDATE `boards` SET boards.last_modified = '2' WHERE boards.name = 'w' AND servers.name = 'zip'
From the error message it seems the join is being missed from the update statement. I've tried reordering my AR calls with no luck so far...
Any help would be appreciated!
SQL Update problem (inner join) - El Forum - 05-03-2008
[eluser]Thorpe Obazee[/eluser]
Have you run your SQL on your Database GUI if it works?
Code:
UPDATE `boards` SET boards.last_modified = ‘2’ WHERE boards.name = ‘w’ AND servers.name = ‘zip’SQL Update problem (inner join) - El Forum - 05-03-2008
[eluser]Unknown[/eluser]
Yup, it works as expected.
I suppose I could skip the join and just update based on the board name. Checking the server and board match up in a separate query...
SQL Update problem (inner join) - El Forum - 05-03-2008
[eluser]Seppo[/eluser]
The AR update function only accepts a table, where, limit and order by clauses. You can change CI code to allow this (in system/database/drivers/mysql/mysql_driver.php, line 553), or run your query without ActiveRecord...