SQL Update problem (inner join)

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

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

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

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