CODEWITHSUNDEEP
phpmysqlcodeigniter
Vlad
Vlad

Reputation: 2773

How to select data from two tables with query builder in CodeIgniter?

I have this query which works perfectly on MySql 5

'SELECT a.id, a.name, a.date, c.name FROM artist as a, country as c WHERE a.country_id=c.id'

Basically it selects the id, name and date from the artist table and then replaces the id of the country with its appropriate name from the country table.

Note: artist.country_id is foreign key i.e. country.id. Every artist belongs to some country and it has a country_id in its table. I need to take data for the artist but also replace the country_id with its appropriate name. I don't want to show number for the country, I want to show country's name.

 $artists = $this->db->query('SELECT a.id, a.name, a.date, c.name FROM artist as a, country as c WHERE a.country_id=c.id');

The problem I face is, artist.name gets overwritten by country.name and I don't see the artist name in the result, only the country name.

How can I get this working under CodeIgniter query builder?

I use CodeIgniter version 3.

Edit: More code added:

    $this->db->select('artist.id', 'artist.name', 'artist.date', 'country.name', false);
    $this->db->from('artist');
    $this->db->join('country', 'country.id=artist.country_id');
    $artists = $this->db->get();

Part of the result:

[0] => stdClass Object
    (
        [id] => 1
        [name] => Macedonia
        [date] => 1979-10-10
    )

[1] => stdClass Object
    (
        [id] => 2
        [name] => Britain
        [date] => 2003-01-01
    )

No artist name in the result. Country name overwrites this field.

artist table query with country name

Upvotes: 1

Views: 8059

Answers (1)

denny
denny

Reputation: 2254

you can write subquery as below

 $this->db->
 select('a.id, a.name, a.date, c.name')
 ->from('artist as a, country as c ')
 ->where('a.country_id','c.id');

you can also do using joins

$this->db->select('a.id as artist_id, a.name as artist_name, a.date as artist_date, c.name as country_name',false)
$this->db->from('artist as a')
$this->db->join('country as c','a.country_id = c.id');

to access the values.

$row->artist_id
$row->artist_name etc...

Upvotes: 2

Related Questions