Join problem with two fields named the same

Join problem with two fields named the same - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Development (https://forum.codeigniter.com/forumdisplay.php?fid=6)
+--- Forum: CodeIgniter 3.x (https://forum.codeigniter.com/forumdisplay.php?fid=17)
+--- Thread: Join problem with two fields named the same (/showthread.php?tid=1499)

Join problem with two fields named the same - frocco - 03-15-2015

Code:
       $this->db->select('*,products.image_path as image_path1, brands.image_path as image_path2');

        $this->db->from('products');

        $this->db->join('brands', 'brands.id = products.brand_id');

        $this->db->where('products.brand_id', $id,'left');

        $product['query'] = $this->db->get();

I have two image fields named the same in both tables (products, brands)
I cannot just use the select * and reference the fields like products.image_path and brands.image_path
I have to rename them as above.

Am I doing something wrong, or is this how CI3 does this?
If I just use select *, I only get one image_path returned because of the collision in name.

RE: Join problem with two fields named the same - includebeer - 03-15-2015

It's not because of CI3, it's how PHP work. You can't have two item with the same key in an array or an object.

It's the same as doing this :

PHP Code:
$data = array('image_path' => 'aaa', 'image_path' => 'bbb'); 

You will end up with :

PHP Code:
Array
(
    [image_path] => bbb
) 

RE: Join problem with two fields named the same - spjonez - 03-15-2015

Write the query yourself, it's reasons like this I never use CI's ActiveRecord methods.

Code:
$sql = "

    SELECT 

        p.*,

        b.id AS brands_id, b.image_path AS brands_image_path

    FROM 

        products p 

        INNER JOIN brands b on p.brand_id = b.id

    WHERE

        p.brand_id = ?

";

$results = $this->db->query( $sql, array( $id ) )->results_array( );

RE: Join problem with two fields named the same - frocco - 03-15-2015

>>$data = array('image_path' => 'aaa', 'image_path' => 'bbb'); <<
Ok, that makes sense now, I thought I was getting a result object, not array.
Thanks includebeer.

Thanks spjonez,
plain old sql is sometimes better.

RE: Join problem with two fields named the same - includebeer - 03-15-2015

(03-15-2015, 08:25 AM)frocco Wrote: >>$data = array('image_path' => 'aaa', 'image_path' => 'bbb'); <<
Ok, that makes sense now, I thought I was getting a result object, not array.

It's the same with an object. You can't have two properties with the same name. The result you get from the database only contain the column names, not the table names. So you have no other choice than rename the column like you did.

@spjonez : This has nothing to do with ActiveRecord. Your solution is the exact same thing frocco already did (using "AS" to rename the column).

RE: Join problem with two fields named the same - frocco - 03-15-2015

Thanks for letting me know. I used yii before coming back ti ci and did not have that problem. Perhaps yii is doing something different in their code. Anyway, so far I am enjoying using hmvc and ci3.