phpcodeigniterjoinquery-builderquoting

Numeric string used in JOIN ON expression is incorrectly quoted as an identifier by CodeIgniter's join() method

Here is the code -

$this->db->select('*');
$this->db->from('friendRequests');
$this->db->where(array('friendRequests.status' => 1, 'users.status' => 1)); 
$this->db->or_where(array('friendRequests.senderId' => $this->session->userdata('userId'), 'friendRequests.receiverId' => $this->session->userdata('userId'))); 
$this->db->join('users', 'users.id = '.$this->session->userdata('userId'));             
$query = $this->db->get();

It provides me this error -

Unknown column '1' in 'on clause'

SELECT *
FROM (`friendRequests`) 
JOIN `users` ON `users`.`id` = `1`
WHERE `friendRequests`.`status` = 1
  AND `users`.`status` = 1 
   OR `friendRequests`.`senderId` = '1' 
   OR `friendRequests`.`receiverId` = '1'
Solution

  • If an entry is surrounded in backticks it counts as a column even if it would not be one without the backtics. It thinks that 1 is a column on the JOIN line because of this.

    Apparently this is a product of the join method in CI. You can fix it very easily by moving that condition to the WHERE clause. There's no need for it to be in the JOIN clause.