How to JOIN three tables in Codeigniter

Viewed 118 times

I'm using codeigniter framework to develop one music cms. i have 3 tables in mysql database, Currently im working in "Album" Table and "Model, Controller". i want to SELECT "Album" Table 1 and JOIN "Album" -> "cat_id" with "Category" -> "cat_id", and fetch all categories records.

Then i want to JOIN "Album" -> "album_id" on "Soundtrack" -> "album_id" then fetch all soundtrack records A to Z.

Please somebody help me to show proper codeigniter query, how i can SELECT and JOIN Tables then fetch records from 3 tables, ?

Table 1 -> Category

cat_id
cat_name
cat_title
date

Table 2 -> Album
cat_id
album_id
album_title
album_details

Table 3 -> Soundtrack
album_id
track_title
track_url
date

Answers

Use this code in model

public function funcname($id)
{
    $this->db->select('*');
    $this->db->from('Album a'); 
    $this->db->join('Category b', 'b.cat_id=a.cat_id', 'left');
    $this->db->join('Soundtrack c', 'c.album_id=a.album_id', 'left');
    $this->db->where('c.album_id',$id);
    $this->db->order_by('c.track_title','asc');         
    $query = $this->db->get(); 
    if($query->num_rows() != 0)
    {
        return $query->result_array();
    }
    else
    {
        return false;
    }
}

Saturday, October 15, 2022

vladimir_zdenek

Ok well I managed to find a "clean" solution, using codeigniter's join, set, etc. So what's cool is that you will have all CI's benefits of using $this->db->join(), $this->db->join(), etc. like escaping and adding quotes.

So first do all your CI stuff:

$this->db->join(..) // Set all your JOINs
$this->db->set(..) // Set your SET data
$this->db->where(..) // Set all your WHEREs

Then you can build the query using Active Record's ready, cleaned and escaped query elements:

// JOIN
$sql = "UPDATE $this->baseTable ";
$sql .= implode(' ', $this->db->ar_join);

// SET
$sql .= ' SET';
$setArray = array();
foreach ($this->db->ar_set as $column=>$newValue)
    array_push($setArray, " $column = $newValue");
$sql .= implode(',', $setArray);

// WHERE
$sql .= ' WHERE '.implode(' ', $this->db->ar_where);

$this->db->query($sql);

If someone has a better solution, I will gladly accept it and use it instead

Sunday, September 25, 2022

lazyone

Jon is right. Here's an example:

$this->db->select('movies.id, 
                   movies.title, 
                   movies.year, 
                   movies.runtime as totaltime,  
                   posters.poster_url');
$this->db->from('movies');
$this->db->join('posters', 'movies.id= posters.id');
$this->db->where('movies.id', $id);
$q = $this->db->get();

This will return objects that have ->id, ->title, ->year, ->totaltime, and ->poster_url properties. You won't need the additional code to fetch the data from each row.

Don't forget, if the Active Record syntax gets a little unwieldy, you can use full SQL queries and get the same results:

$sql = "SELECT movies.id,
        movies.title,
        movies.year,
        movies.runtime as totaltime,
        posters.poster_url
        FROM movies
        INNER JOIN posters ON movies.id = posters.id
        WHERE movies.id = ?"

return $this->db->query($sql, array($id))->result();

Both forms will ensure that your data is escaped properly.

CodeIgniter Active Record

Query Binding in CodeIgniter

Tuesday, November 29, 2022

mr.j

Try this:

SELECT a.sales_id, d.bus_title, s.cat_id
FROM tbl_sales a
INNER JOIN tb_sales_category s ON a.sales_id = s.sales_id
INNER JOIN tbl_business      d ON a.bus_id   = d.bus_id
INNER JOIN tb_category       b ON s.cat_id   = b.cat_id

The idea is fairly simple, the first field in your new table tb_sales_category which is sales_category_id is working as a surrogate key, it has nothing to do with the relations between the two other tables. Then we come to the other two fields which are sales_id, cat_id, these what you should map to the other two sides of the relations.

You can't Join tb_category b ON a.cat_id = b.cat_id on the new schema becouse we no longer have a.cat_id, and here comes the new table tb_sales_category role, by inserting it with two binding sides, one with INNER JOIN tb_category b ON s.cat_id = b.cat_id and the other with INNER JOIN tb_sales_category s ON a.sales_id = s.sales_id we should be done.

Hope this makes sense.

Thursday, December 8, 2022

mike_brant

select *
from dbo.seat
where noseat not in (
    select noseat
    from dbo.booking
    where statusbooking = 1
)

Here's my guess. I can't say I really understand what "noseat" means (unless it's "number of seat")?

Sunday, October 9, 2022

ron_crosler

Only authorized users can answer the search term. Please sign in first, or register a free account.

Not the answer you're looking for? Browse other questions tagged :

php

mysql

codeigniter

join

How to JOIN three tables in Codeigniter

Answers

php,mysql,codeigniter,join