Asked  7 Months ago    Answers:  5   Viewed   48 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

55

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;
    }
}
Wednesday, March 31, 2021
 
LoicTheAztec
answered 7 Months ago
81

Don't be afraid to change core files, just alter FCPATH/system/database/drivers/mysqli/mysqli_driver.php

function escape_str($str, $like = FALSE)
{
    if (is_array($str))
    {
        foreach ($str as $key => $val)
        {
            $str[$key] = $this->escape_str($val, $like);
        }

        return $str;
    }

    if (function_exists('mysqli_real_escape_string') AND is_object($this->conn_id))
    {
        $str = mysqli_real_escape_string($this->conn_id, $str);
    }
    else
    {
        $str = addslashes($str);
    }

    // escape LIKE condition wildcards
    if ($like === TRUE)
    {
        $str = str_replace(array('%', '_'), array('\%', '\_'), $str);
    }

    return $str;
}

I had the same issue


Better solution -> https://ellislab.com/forums/viewthread/228288/ "stated in github that it will be fixed in CodeIgniter 3.0 the fix already exists in that repository"

Wednesday, March 31, 2021
 
themihai
answered 7 Months ago
84

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

Wednesday, March 31, 2021
 
Owen
answered 7 Months ago
50

I don't think your sample data is really what you have based on the information provided. My best guess here is that your query is doing a fan-out on either or both of those joins which is messing up your sums. You need to sum them separately, else additional rows in either on of those joins will fan out the other join, duplicating your results in the sum. This is evident in your result since 001 looks to be double (even though your sample data doesn't show it).

Something like this would ensure sums independent of each other:

SELECT PR.product_no, 
    ( SELECT sum(I.qty)
      FROM invoice I 
      WHERE I.product_no=PR.product_no ) invoice_qty,
    ( SELECT sum(P.qty)
      FROM purchase P 
      WHERE P.product_no=PR.product_no ) purchase_qty
FROM products PR 
Thursday, August 5, 2021
 
Nate
answered 3 Months ago
16

You'll need to join the games_teams and teams twice, like:

SELECT ga.game_id
        , ga.game_name
        , ga.game_duration
        , t1.team_name, gt1.team_score
        , t2.team_name, gt2.team_score
FROM games ga
JOIN games_teams  gt1 ON gt1.game_id = ga.game_id
JOIN games_teams  gt2 ON gt2.game_id = ga.game_id
JOIN teams t1 ON t1.team_id = gt1.team_id
JOIN teams t2 ON t2.team_id = gt2.team_id
WHERE gt1.team_id < gt2.team_id
        ;

A clean way to do squeeze out the {games_teams * teams} sub-join and refer to it twice is by putting it into a CTE: (unfortunately mysql does not support CTEs)

WITH gtx AS (
        SELECT gt.game_id
        , gt.team_score
        , te.team_id
        , te.team_name
        FROM games_teams gt
        JOIN teams te ON te.team_id = gt.team_id
        )
SELECT ga.game_id 
        , ga.game_name
        , ga.game_duration
        , g1.team_name, g1.team_score
        , g2.team_name, g2.team_score
FROM games ga
JOIN gtx g1 ON g1.game_id = ga.game_id
JOIN gtx g2 ON g2.game_id = ga.game_id
WHERE g1.team_id < g2.team_id
  ;

Result:

 game_id | game_name | game_duration | team_name | team_score | team_name | team_score 
---------+-----------+---------------+-----------+------------+-----------+------------
       1 | Game A    |           300 | Team A    |         45 | Team B    |         55
       2 | Game B    |           258 | Team C    |         60 | Team D    |         65
(2 rows)
Sunday, October 17, 2021
 
moister
answered 2 Weeks ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share