Welcome Guest, Not a member yet? Register   Sign In
1054 error on Join using active record for multiple sites using single code base (erkanaauth modification)


I am getting the following error:
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:
function getRole($schoolid) {
        $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:
TABLE: 12-users
Field    Type
id     int(11)
email     varchar(40)
password     varchar(128)
role_id     int(11)
attempts     tinyint(3)

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.


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!

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.

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

Looks like a bug report was already filed on incorrect escaping with active record join:


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.

$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');

Theme © iAndrew 2016 - Forum software by © MyBB