1054 error on Join using active record for multiple sites using single code base (erkanaauth modification)

1054 error on Join using active record for multiple sites using single code base (erkanaauth modification) - Printable Version

+- 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: 1054 error on Join using active record for multiple sites using single code base (erkanaauth modification) (/showthread.php?tid=20926)

1054 error on Join using active record for multiple sites using single code base (erkanaauth modification) - El Forum - 07-25-2009

[eluser]oppenheimer[/eluser]
Hello

I am getting the following error:

Code:
Error Number: 1054

Unknown column '12' in 'on clause'

SELECT `roles`.`name` FROM (`12-users`) JOIN `roles` ON `12`-`users`.`role_id` = roles.id WHERE `12-users`.`id` = '1' LIMIT 1

I have modified the Erkanaauth library to use a different table depending on the URL (to handle multiple sites on the same code base). The code is:

Code:
function getRole($schoolid) {

        $this->CI->db->select('roles.name');

        $this->CI->db->JOIN('roles', $schoolid . '-users.role_id = roles.id');

        $query = $this->CI->db->getwhere($schoolid . '-users', array($schoolid . '-users.id'=>$this->CI->session->userdata('user_id')), 1, 0);

        if ($query->num_rows() == 1) {

            $row = $query->row();

            return $row->name;

        }

    }

For this example, $schoolid=12
The tables are:

Code:
TABLE: 12-users

Field    Type

id     int(11)

email     varchar(40)

password     varchar(128)

role_id     int(11)

attempts     tinyint(3)

Code:
TABLE: roles

Field    Type

id     smallint(5) 

name     varchar(10)

It seems to me that the trying to make the table name variable is causing a syntax error but I can't seem to figure out a way to fix. Other queries work great but I'm hung up on the join.

Thank you.

1054 error on Join using active record for multiple sites using single code base (erkanaauth modification) - El Forum - 07-26-2009

[eluser]oppenheimer[/eluser]
D'oh!

It turns out that in Version 1.6.0, getwhere was deprecated and was replaced by get_where. The Erkanaauth code was still using the getwhere. Once I changed to get_where, everything worked!

1054 error on Join using active record for multiple sites using single code base (erkanaauth modification) - El Forum - 07-26-2009

[eluser]oppenheimer[/eluser]
Double D'oh.

That wasn't the problem! I was using an old version of the code. I think it may be a bug in the active record $this->db->join where it interprets the the beginning of the table named "12-users" as a number. However, I don't think it should because the table name does contain text. The following should be to demonstrate.

Code:
$this->db->select('roles.name');

$this->db->JOIN('roles', '12-users.role_id = roles.id');

$query = $this->db->get('12-users');

1054 error on Join using active record for multiple sites using single code base (erkanaauth modification) - El Forum - 07-26-2009

[eluser]oppenheimer[/eluser]
Looks like a bug report was already filed on incorrect escaping with active record join:

http://codeigniter.com/bug_tracker/bug/7692/

1054 error on Join using active record for multiple sites using single code base (erkanaauth modification) - El Forum - 07-26-2009

[eluser]oppenheimer[/eluser]
The workaround was to build the join without the active record class. I used manual query instead: $query = $this->db->query

It was very tricky to get exactly the right syntax with correct escapes.

Code:
$query = $this->CI->db->query('SELECT roles.name FROM `' .$schoolid . '-users` JOIN roles ON `' . $schoolid . '-users`.`role_id` = roles.id where `' . $schoolid . '-users`.`id` = 1 LIMIT 1');