friends and users

-2 down vote favorite



i am making a friend system for a social networking kind of website.i have completed the search part now i am working on adding friends.so when a logged in user visits a other guy's profile he can send him friend request.so on clicking the button i am taking two parameter one is friend's id and other is user's id and passing it to the model.my tables are user_login for users and friends have 1)user_id1 2)friend_id1 and so on.so now i want to pass my data that i have recieved after clicking on the add friend button to the database and for that i have to join the two tables.i.e user_login and pivot table friends.so thaton clicking these id's get saved in that pivot table.just wanted someone to help me with the joining of tables.since it is showing me error. my code for model is

Code:

public function display_followers($user_id,$friend_id)
{

$this->db->select('*');
$this->db->from('user_login');
$this->db->where('id',$user_id);
$this->db->where('id',$friend_id);

$this->db->join('user_login', 'users_login.id = friends.user_id');
$this->db->join('user_login', 'users_login.id = friends.friend_id');


$query=$this->db->get();
if($query->num_rows()>0){
return $query->Result();

}
else{
return false;


}
}

}
my code for controller is

Code:

public function followers(){
   $friend_id=$this->uri->segment(3);


   $user_id = $this->session->userdata['logged_in']['id'];




$this->load->model('Main_Model');
$this->Main_Model->display_followers($user_id,$friend_id);

}

You mentioned you receive an error and I don't see any mention of the error. So, let's look at what you have. Your query builder doesn't look correct to me. I believe your query ends up like this:

Code:

SELECT * FROM user_login
JOIN user_login ON users_login.id = friends.user_id
JOIN user_login ON users_login.id = friends.friend_id
WHERE id = $user_id AND id = $friend_id

There are several things wrong with this. You are joining the same table without giving it an alias, so the columns will be ambigious if the database even allows it. On top of that, your ON statement references users_login instead of user_login or vice versa. I also see no join for a friends table. Then your WHERE clause is saying the ID has to be the $user_id AND the $friend_id. This query simply will not work. Without knowing your table structure, it is difficult for people to help out.

One thing you can do to troubleshoot queries is instead of calling get(), you can do the following:

PHP Code:

$sql = $this->db->get_compiled_select();
echo $sql; 


This will display the actual query that CodeIgniter has compiled. You can run it directly against your database to see more specific results.