[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!