Unknown column 'xxx' in 'on clause'

[eluser]ibnclaudius[/eluser]
I getting this error when running this query:

Error Number: 1054

Unknown column 'ab24da0197abbd63800d981f18e3ca11b545851d' in 'on clause'

Code:

$query = $this->db->select($this->users_table . '.id AS id, ' . $this->users_table . '.name AS name, ' . $this->users_table . '.email AS email, ' . $this->users_table . '.type AS type')
    ->from($this->users_table )
    ->join($this->sessions_table, $this->sessions_table . '.user_id = ' . $this->users_table . '.id AND ' . $this->sessions_table . '.token = ' . $token, 'INNER')
    ->get();

Code:

SELECT `users`.`id` AS id, `users`.`name` AS name, `users`.`email` AS email, `users`.`type` AS type
FROM (`users`)
INNER JOIN `sessions` ON `sessions`.`user_id` = `users`.`id`
AND sessions.token = ab24da0197abbd63800d981f18e3ca11b545851d

What am I doing wrong?

[eluser]vbsaltydog[/eluser]
never mind

[eluser]ibnclaudius[/eluser]

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`this->users_table` . users.id AND sessions.token = $this->users_table . 8a4668b' at line 3

Code:

SELECT `users`.`id` AS id, `users`.`name` AS name, `users`.`email` AS email, `users`.`type` AS type
FROM (`users`)
INNER JOIN `sessions` ON `sessions`.`user_id` = $`this->users_table` . users.id
AND sessions.token = $this->users_table . 8a4668b91e275e020c54444ae9e5cbbc81fba13a
WHERE `sessions`.`token` = '8a4668b91e275e020c54444ae9e5cbbc81fba13a'

I can make it work, like this:

Code:

$query = $this->db->select($this->users_table . '.id AS id, ' . $this->users_table . '.name AS name, ' . $this->users_table . '.email AS email, ' . $this->users_table . '.type AS type')
    ->from($this->users_table)
    ->join($this->sessions_table, $this->sessions_table . '.user_id = ' . $this->users_table . '.id', 'INNER')
    ->where($this->sessions_table . '.token', $token)
    ->get();

But I want to put the where($this->sessions_table . '.token', $token) inside the join clause, because I have another $query like this and works.

Code:

$query = $this->db->select($this->schools_table . '.id AS id, ' . $this->schools_table . '.name AS name')
    ->from($this->schools_table)
    ->join($this->users_schools_table, $this->users_schools_table . '.school_id = ' . $this->schools_table . '.id AND ' . $this->users_schools_table . '.user_id = ' . $user_id, 'INNER')
    ->order_by($this->users_schools_table . '.created', 'DESC')
    ->limit(1)
    ->get();

[eluser]vbsaltydog[/eluser]
Try this:

Code:

$query = $this->db->
select($this->users_table . '.id AS id, ' . $this->users_table . '.name AS name, ' . $this->users_table . '.email AS email, ' . $this->users_table . '.type AS type')
->from($this->users_table )
->join($this->sessions_table, $this->sessions_table . '.user_id = ' . $this->users_table . '.id AND ' . $this->sessions_table . '.token = ' . $token)
->get();

[eluser]ibnclaudius[/eluser]
This is the same I post in my first post.

Code:

->join($this->sessions_table, $this->sessions_table . '.user_id = ' . $this->users_table . '.id AND ' . $this->sessions_table . '.token = ' . $token, 'INNER')

[eluser]vbsaltydog[/eluser]
Without the INNER. Even though inner is the default. I am just trying to help you find the bug.

[eluser]ibnclaudius[/eluser]
I tried, same error in the first post.

[eluser]vbsaltydog[/eluser]
put $token in single quotes.

[eluser]scottwire[/eluser]
The token is a string and it looks like you need to wrap it in single quotes

Code:

->join($this->sessions_table, $this->sessions_table . '.user_id = ' . $this->users_table . '.id AND ' . $this->sessions_table . '.token = ' . "'" . $token. "'")

[eluser]ibnclaudius[/eluser]
This worked:

Code:

->join($this->sessions_table, $this->sessions_table . '.user_id = ' . $this->users_table . '.id AND ' . $this->sessions_table . '.token = ' . "'" . $token. "'")

But I have another query, with single quotes, that works, why? Look:

Code:

$query = $this->db->select($this->schools_table . '.id AS id, ' . $this->schools_table . '.name AS name')
    ->from($this->schools_table)
    ->join($this->users_schools_table, $this->users_schools_table . '.school_id = ' . $this->schools_table . '.id AND ' . $this->users_schools_table . '.user_id = ' . $user_id, 'INNER')
    ->order_by($this->users_schools_table . '.created', 'DESC')
    ->limit(1)
    ->get();