CodeIgniter Forums
Join statement not workin properly... - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Libraries & Helpers (https://forum.codeigniter.com/forumdisplay.php?fid=22)
+--- Thread: Join statement not workin properly... (/showthread.php?tid=40581)



Join statement not workin properly... - El Forum - 04-12-2011

[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:
<?php    foreach($friends as $row){?>
                                <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..


Join statement not workin properly... - El Forum - 04-12-2011

[eluser]CroNiX[/eluser]
I'm not sure why you are joining on your first function. You are only retrieving one value (count) from one table.

Quote:Now basically its displaying all contents from the table ‘members’. The join statement and the conditions are not working..
That's because that's all you are selecting...from one table, 'members'.
Code:
$this->db->select('members.id,members.screenname,members.photo,members.email');
Try something like
Code:
$this->db->select('members.id as members_id,members.screenname as members_screenname,members.photo as members_photo,members.email as members_email');
$this->db->select('friends.name as friends_name');  //don't know what you are trying to get from friends table
In your second function, I don't think you are joining on anything.
Code:
$this->db->join('friends','friends.member_id','left');

should be something like
Code:
$this->db->join('members','friends.member_id = members.id','left');



Join statement not workin properly... - El Forum - 04-13-2011

[eluser]Roy MJ[/eluser]
Yeah but the $id is being passed dynamically. So

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

doesnt work since its $id, a variable right?