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



Use this code in model

public function funcname($id)
    $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');
    $query = $this->db->get(); 
    if($query->num_rows() != 0)
        return $query->result_array();
        return false;
Saturday, October 15, 2022

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:

$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);

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


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

Sunday, September 25, 2022

Jon is right. Here's an example:

                   movies.runtime as totaltime,  
$this->db->join('posters', '');
$this->db->where('', $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.runtime as totaltime,
        FROM movies
        INNER JOIN posters ON =
        WHERE = ?"

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

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
select *
where noseat not in (
    select noseat
    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
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 :