+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: mysql join problem (/showthread.php?tid=79927)
mysql join problem - magiwells - 08-17-2021
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
RE: mysql join problem - php_rocs - 08-17-2021
@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');
RE: mysql join problem - magiwells - 08-17-2021
(08-17-2021, 07:26 AM)php_rocs Wrote: @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.
RE: mysql join problem - InsiteFX - 08-18-2021
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.
RE: mysql join problem - iRedds - 08-18-2021
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
RE: mysql join problem - magiwells - 08-18-2021
(08-18-2021, 03:05 AM)iRedds Wrote: 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
RE: mysql join problem - php_rocs - 08-18-2021
@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.