Multiple functions for several joins in a query - how to do it?

#1
[eluser]titanite[/eluser]
Hello,

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.

Code:
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

Code:
<?php
class Movie extends Controller {

    function Movie()
    {
        parent::Controller();
        $this->load->helper('url');
        $this->load->helper('form');
    }

    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()
    {
        $this->load->model('Movie_model');
        $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

Code:
<?php

class Movie_model extends Model {

    function Movie_model()
    {
        parent::Model();
    }

    function get_review($id)
    {
        $this->db->select('*');
        $this->db->from('movie_review');
        $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?

Code:
<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>Review:</p>
<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

#2
[eluser]Mischievous[/eluser]
Not sure about what your exactly trying to do but I always setup my DB requests like this:

Code:
function fetch_review($where, $join, $type = 'array')
    {
        $type = strtolower($type);
        if(is_array($where))
        {
             foreach($where as $column => $value)
             {
                 $this->db->where_in($column, $value);
             }
        }
        if(is_array($join))
        {
             foreach($join as $table => $fields)
             {
                 $this->db->join($table, $fields);
             }
        }
        $this->db->join('authors', 'authors.id = movie_review.author');
        $result = $this->db->get('movie_review');
        if($this->db->count_all_results() > 0)
        {
            switch($type)
            {
                case "object":
                    $request = $result->result();
                break;
                case "bool":
                    return TRUE;
                break;
                default:
                    $request = $result->result_array();
            }
            return $request;
        } else {
            return FALSE;
        }
    }
Usage:
Code:
$where = array('id' => $id);
    $join = array('persons' => 'persons.id = movie_review.director_1');
    $this->fetch_review($where, $join);
Which allows you to pass where statements and join statements as you need dynamically?
As for the naming, setup a format_result() function and run through the result and add your prefix's as needed d1_director, d2_director etc. etc.

#3
[eluser]Federico Baña[/eluser]
You can make a second join to the persons table by director_2 on the reviews table, in the case you dont have a second director, all the values that should match would return NULL. Also you'd need to set field aliases to avoid ambiguity.

Code:
&lt;?php

class Movie_model extends Model {

    function Movie_model()
    {
        parent::Model();
    }

    function get_review($id)
    {
        $this->db->select('*'); // change this line and select all fields one by one, for it as a good practice and also you need to set field aliases there
        // here with the aliases (dont know the field names you use)
        $this->db->select('movie_review.field_x');
        $this->db->select('fst_directors.name AS director_1_name');
        $this->db->select('sec_directors.name AS director_2_name');
        $this->db->from('movie_review');
        $this->db->where('movie_review.id', $id);
        $this->db->join('authors', 'authors.id = movie_review.author');
        // match first director, you MUST USE an alias here, otherwise you'll get an error
        $this->db->join('persons AS fst_directors', 'fst_directors.id = movie_review.director_1');
        // march second director also with alias
        $this->db->join('persons AS sec_directors', 'sec_directors.id = movie_review.director_2');
        $q = $this->db->get();

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

}

?&gt;

This way, a row on movie_review has a NULL director_2, the columns on the result set (director_2_name in the example) will be NULL as well.
There on the view you use a conditional to know if there's or there's not a second director.
Code:
if($info[$i]->director_2_name !== NULL){ echo 'This review has a second director' }
For example.

Hope this helps you...

#4
[eluser]titanite[/eluser]
Dear Mischevious: You are WAY too advanced for me... I'll study your method carefully and find out why you do it that way.

Federico: Thank you so much for your help! It was exactly what I needed to learn! Smile

However, I have run into one error. In the cases where $info->director_2 is NULL, I have the "Message: Trying to get property of non-object" error message.

When director_2 is not null, I have his name printed.

Why is there such an error? Sad

Code:
<h3>Directed by: &lt;?php echo "$info->director_1_firstname $info->director_1_lastname"; ?&gt;&lt;?php if($info->director_2_firstname !== NULL){ echo ", $info->director_2_firstname $info->director_2_lastname"; }  ?&gt;</h3>

#5
[eluser]titanite[/eluser]
Hi guys,

I don't get it. Why is it that when the database entry is NULL, I get this error message:

A PHP Error was encountered
Severity: Notice
Message: Trying to get property of non-object
Filename: libraries/Loader.php(673) : eval()'d code
Line Number: 9

I've checked the Loader.php file, it seems that the problem is with short tags?

What's wrong? Sad


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.