CODEWITHSUNDEEP
phpmysqlcodeigniter
Zoe
Zoe

Reputation: 99

codeigniter inner join tables syntax

error msg: Unknown column 'category' in 'where clause'

i have to inner join two tables. what should be the correct query? Or what parameter should i put in $query->get()? If I only put 'film', it cannot find 'category' column in another table. 

    $query = $this->db->select('title, name as category, rental_rate, length')->order_by($sort_by, $sort_order);

    $query = $this->db->join('film_category', 'film_category.film_id = film.film_id');

    $query = $this->db->join('category', 'film_category.category_id = category.category_id');

    if(strlen($query_array['title'])) {
        $query->like('title', $query_array['title']);
    }

    if(strlen($query_array['category'])) {
        $query->where('category', $query_array['category']);
    }

    $data['films'] = $query->get('film', 20, $this->uri->segment(6));

Upvotes: 0

Views: 7440

Answers (1)

user2037979
user2037979

Reputation: 

$this->db->select('title, name as category, rental_rate, length')->order_by($sort_by, $sort_order);
$this->db->from('film'); /*I assume that film was the table name*/
$this->db->join('film_category', 'film_category.film_id = film.film_id');
$this->db->join('category', 'category.category_id = film_category.category_id');

$query = $this->db->get();

var_dump($query);

Double check that code I added and make sure that on category table, the column is called category_id, and not just id, and that under film_category, there's a category_id column. If with the code I submitted, you still get the error, try to replace the first line with

$this->db->select('title, name, rental_rate, length')->order_by($sort_by, $sort_order);

I'm not sure if using a name that matches a table will cause a trouble with CodeIgniter and ActiveRecord.

Hope that helps.

Upvotes: 2

Related Questions