CODEWITHSUNDEEP
phpmysqlsqlcodeigniter
atomaprchya
atomaprchya

Reputation: 175

joining 3 tables in codeigniter

I have gone through many questions about joining 3 tables in Codeigniter. But, I am stuck in these lines of coding that includes returning products array.

function products_sorter($region) {
        $this -> db -> select('*');
        $this -> db -> from('wiyo_products');
        $this -> db -> join('wiyo_products_distribution', 'wiyo_products.id = wiyo_products_distribution.product_id');
        $this -> db -> join('wiyo_regions', 'wiyo_regions.id = wiyo_products_distribution.region_id');
        $query = $this -> db -> get(array('wiyo_regions.slug' => $region));

        if ($query -> num_rows() > 0) {
            return $query;

        }

    }

But I am getting error which look like this

A Database Error Occurred
Error Number: 1103

Incorrect table name ') JOIN '

SELECT * FROM (`wiyo_products`, `) JOIN `wiyo_products_distribution` ON `wiyo_products`.`id` = `wiyo_products_distribution`.`product_id` JOIN `wiyo_regions` ON `wiyo_regions`.`id` = `wiyo_products_distribution`.`region_id`

What sort of error is this? How to solve this?

Upvotes: 0

Views: 409

Answers (2)

mr_mark88
mr_mark88

Reputation: 1059

You are getting the error because of the improper use of the Active Record get function, which uses the table name as the first parameter.

http://ellislab.com/codeigniter/user-guide/database/active_record.html

As you appear to be passing an array into the get function, I'm guessing you want the get_where function instead?

Try the below - removing the from line in your current query and placing the table name as the first parameter in the get_where function, with your current array being passed in as the second parameter:

function products_sorter($region) {
    $this -> db -> select('*');
    $this -> db -> join('wiyo_products_distribution', 'wiyo_products.id = wiyo_products_distribution.product_id');
    $this -> db -> join('wiyo_regions', 'wiyo_regions.id = wiyo_products_distribution.region_id');
    $query = $this -> db -> get_where('wiyo_products' array('wiyo_regions.slug' => $region));    
    if ($query -> num_rows() > 0) {
        return $query;

    }    
}

Hope that helps!

Upvotes: 1

Zarathuztra
Zarathuztra

Reputation: 3251

That syntax error should give you a bit clue: (wiyo_products, `)

In your $this->db->get() statement you're also passing in an additional table as raw associative array, which according to the documentation (and as far as I've ever seen, folks feel free to correct me and I'll edit the answer or take it down) is not allowed in the get param set, only strings of table names. It's valid to pass in a TABLE name to get(), but not a column. So, if you want to specify multiple tables, separate them by comma as you would in a normal FROM clause:

$this->db->from('wiyo_products,wiyo_regions');//or something to that effect. I don't bother with these, usually just use raw queries with transactions and bindings.

and change

$this -> db -> get(array('wiyo_regions.slug' => $region));

to

$this->db->get();

Select Active Record docs

Upvotes: 0

Related Questions