Welcome Guest, Not a member yet? Register   Sign In
Multiple functions for several joins in a query - how to do it?


I'm a novice at this, so please bear with me.

I'm doing this query for a movie review (stored in a table called movie_review), and in the review, there is normally a director whose name I store in another table called persons.

Now, there could be a second director, whose name I would like to show.

In non-MVC formats, I would do first do a query to retrieve the the movie review and if Director 2 exists, I would do another query.

print "$row[movietitle]";
print "by director $row[director1]";
if ($row[director2]) {
$query = "select first_name, last_name from persons where id = '$row[director2]'"; etc etc etc  
print "and also by $first_name $last_name.";

In CI, this is my controller, which I name movie.php

class Movie extends Controller {

    function Movie()

    function index()
        $data['title'] = "All Movies";
        $data['heading'] = "All Movies";
        $this->db->order_by("date", "desc");
        $data['query'] = $this->db->get('movie_review');
        $this->load->view('movie_listing_all', $data);

    function movie_review()
        $id = $this->uri->Segment(3);
        $data['info'] = $this->Movie_model->get_review($id);
        $this->load->view('movie_view', $data);


My model is called movie_model.php


class Movie_model extends Model {

    function Movie_model()

    function get_review($id)
        $this->db->where('movie_review.id', $id);
        $this->db->join('authors', 'authors.id = movie_review.author');
        $this->db->join('persons', 'persons.id = movie_review.director_1');
        $q = $this->db->get();

        $info = $q->row();
        return $info;



As you can see, I have done a join. However, this only works for director_1, I will not be able to do a second join for director_2.

Another problem is in the view file. How can I rename a variable so as to distinguish between the names of Director_1 and Director_2?

<h2>Title: &lt;?php echo "$info->title"; ?&gt;</h2>
<h2>Marketed title: &lt;?php echo "$info->title_marketed"; ?&gt;</h2>
<h3>Directed by: &lt;?php echo "$info->first_name $info->last_name"; ?&gt;&lt;?php if($info->director_2) { echo ", $info->first_name $info->last_name";} ?&gt;</h3>
<p>&lt;?php echo "$info->text;"?&gt;</p>

<p>Written by author &lt;?php echo "$info->name"; ?&gt;</p>
<p>on date &lt;?php echo "$info->date" ?&gt;</p>

Can anyone advise a stupido like me? Sad

Messages In This Thread
Multiple functions for several joins in a query - how to do it? - by El Forum - 04-20-2010, 02:07 PM

Theme © iAndrew 2016 - Forum software by © MyBB