[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">
<?php if(($row->photo)!=NULL){?>
<a >screenname?>" href="<?php echo base_url();?>photo/<?php echo $row->photo?>" rel="lightbox[roadtrip]"><img height="100" height="100">photo?>"></a><br />
<?php } else{?>
<a >screenname?>" href="<?php echo base_url();?>images/noimage.jpg" rel="lightbox[roadtrip]"><img src="<?php echo base_url();?>images/noimage.jpg" height="100" width="100" /></a>
<?php }?>
<a class="newshead">id?>"><?php echo $row->screenname?></a>
</div>
<?php } ?>
<div><?php echo $this->pagination->create_links(); ?></div>
Now basically its displaying all contents from the table 'members'. The join statement and the conditions are not working..