Guzzyman Guzzyman - 26 days ago 6
MySQL Question

How to resolve Error 1054 in my code igniter query

I have a code igniter project hosted here but I'm having Error Number: 1054
Unknown column 'p.id' in 'on clause' displayed on the most popular section of the website.

Below is the sql query that is giving this error.

public function get_popular()
{
// Using active records
$this->db->select('P.*, COUNT(O.product_id) as total');
$this->db->from('orders AS O');
$this->db->join('products AS P', 'O.product_id = p.id','INNER');
$this->db->group_by('O.product_id');
$this->db->order_by('total','desc');
$query = $this->db->get();
return $query->result();
}


with this line of code coursing the issue

$this->db->join('products AS P', 'O.product_id = p.id','INNER');


The project was working correctly on my local machine until I hosted it here for view

Answer

This one is quite simple. You declare an alias for the table as P - capital letter:

$this->db->join('products AS P', ...)

but then refer to it via a lower letter: p.id

On Windows the case of the letters - P or p - doesn't matter, on Linux (which is probably used by your hosting) it does. So to fix this error just type:

$this->db->join('products AS p', 'O.product_id = p.id','INNER');

In the future you would do well using strictly lowercase names for tables and fields as that will work on any OS.