mysql join problem

My join is inserting the foreign key in place of the primary key.

PHP Code:

$builder = $this->db->table('elongate');
$builder->select('elongate.id, elongate.catalog_num, elongate.city_id, elongate.venue_id, city.id, city.city_name');
$builder->join('city', 'city.id = elongate.city_id');

The result is:
Array
(
    [0] => Array
        (
            [id] => 1
            [catalog_num] => OR-ALB-HC-01
            [city_id] => 1
            [venue_id] => 32
            [city_name] => Albany
        )

    [1] => Array
        (
            [id] => 1
            [catalog_num] => OR-ALB-HC-02
            [city_id] => 1
            [venue_id] => 32
            [city_name] => Albany
        )

    [2] => Array
        (
            [id] => 1
            [catalog_num] => OR-ALB-HC-03
            [city_id] => 1
            [venue_id] => 32
            [city_name] => Albany
        )

    [3] => Array
        (
            [id] => 1
            [catalog_num] => OR-ALB-HC-04
            [city_id] => 1
            [venue_id] => 32
            [city_name] => Albany 


But the [id] should be 1,2,3,4 respectively

@magiwells ,

Maybe in your join statement you should specify which join you wish to use... Options are: left, right, outer, inner, left outer, and right outer.

Your statement:
$builder->join('city', 'city.id = elongate.city_id');

Try (one of the options listed above. I chose left for this example):
$builder->join('city', 'city.id = elongate.city_id', 'left');

@magiwells ,

Maybe in your join statement you should specify which join you wish to use... Options are: left, right, outer, inner, left outer, and right outer.

Your statement:
$builder->join('city', 'city.id = elongate.city_id');

Try (one of the options listed above.  I chose left for this example):
$builder->join('city', 'city.id = elongate.city_id', 'left');

I tried the join options before I posted and got the same result.   It's weird when I do a sql query outside of Codeigniter, I get the expected result.
There must be something Im overlooking.  I am just learning Codeigniter.

Try this.

PHP Code:

$builder = $this->db->table('elongate');
$builder->select('elongate.id, elongate.catalog_num, elongate.city_id, elongate.venue_id, city.id, city.city_name');
$builder->join('city', 'city.id = elongate.city_id');
$query = $builder->get();

var_dump($query->getResultArray()); 


Also you can use $db->getLastQuery(); to see what your query looks like.

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

try to remove city.id from the select section.
you already got the city.id as elongated.city_id

in your case elongate.id will be replaced with city.id

try to remove city.id from the select section.
you already got the city.id as elongated.city_id

in your case elongate.id will be replaced with city.id

This was it 
Thank you

@magiwells ,

Also, if you still want city.id as a part of your results you could do this...

$builder->select('elongate.id, elongate.catalog_num, elongate.city_id, elongate.venue_id, city.id as city_id, city.city_name');

Then city.id would be called city_id instead of id.