Login

04-12-2011, 10:45 PM

[eluser]Roy MJ[/eluser]
Im trying for some days now to make this join statement work. But its not working properly as i want it to work.

Here is the controller :

Code:
function index($pgoffset='')

{

    $this->data['pagetitle'] ='Friends List';

    if(($this->session->userdata('user_id'))!=NULL)

    {

    $row    =$this->Profile_model->get_selected($this->session->userdata('user_id'));

        $this->data['username'] = $row->username;

        $id =  $row->id;

    }

    $config['per_page'] = 20;

    $config['total_rows'] = $this->Friends_model->get_total($id);

    $config['base_url'] = site_url().'/friends/index/';

    $config['uri_segment'] = 3;

    $this->data['friends'] = 

                $this->Friends_model->select_friends($config['per_page'],$pgoffset,$id);

    $this->data['pgoffset'] = $pgoffset;

    $this->pagination->initialize($config);

    $this->load->view('profile/friends', $this->data);

}

The model part is as follows :

Code:
function get_total($id)

{

    $count=0;

    $this->db->select('COUNT(friends.id) AS total');

    $this->db->distinct();

    $this->db->from('friends');

    $this->db->join('members','friends.member_id','left');

    $this->db->where('friends.member_id =', $id);

    $this->db->where('friends.status =', 1);

    $result_total = $this->db->get();

    if($result_total->num_rows()>0){

    $row    = $result_total->row();

    echo $count    =    $row->total;

    }

return $count;

    }

function select_friends($limit,$pgoffset,$id)

   {

           $this->db->select('members.id,members.screenname,members.photo,members.email');

$this->db->limit($limit,$pgoffset);

$this->db->distinct();

$this->db->from('members');

$this->db->join('friends','friends.member_id','left');

$this->db->where('friends.member_id =', $id);

$this->db->where('friends.status =', 1);

$result_news = $this->db->get();

return $result_news->result();

}

View is as follows :

Code:
&lt;?php    foreach($friends as $row){?&gt;

                                <div id="friends">

                                &lt;?php if(($row->photo)!=NULL){?&gt;    

                                    <a >screenname?&gt;" href="&lt;?php echo base_url();?&gt;photo/&lt;?php echo $row->photo?&gt;" rel="lightbox[roadtrip]"><img height="100" height="100">photo?&gt;"></a><br />

                                    &lt;?php } else{?&gt;

                                        <a >screenname?&gt;" href="&lt;?php echo base_url();?&gt;images/noimage.jpg" rel="lightbox[roadtrip]"><img src="&lt;?php echo base_url();?&gt;images/noimage.jpg" height="100" width="100" /></a>

                                    &lt;?php }?&gt;

                                    <a class="newshead">id?&gt;">&lt;?php echo $row->screenname?&gt;</a>

                                </div>                    

                            &lt;?php } ?&gt;

                            <div>&lt;?php echo $this->pagination->create_links(); ?&gt;</div>

Now basically its displaying all contents from the table 'members'. The join statement and the conditions are not working..