inner join with OR using codeigniter

Hello 
am trying to get all my friends from database i have a table called friends it has id, user_one, user_two and date
now i used this code and i was getting them but i couldn't get the date from friends
here is the code:

PHP Code:

function getfriends($my_id, $send_data){

  $CI =& get_instance();
  $where = "user_one ='".$my_id."' OR user_two = '".$my_id."'";
  $CI->db->where($where);
  $query = $CI->db->get('friends');
  //die(print_r($query->result_array()));
  $all_users = $query->result_array();
  $return_data = [];

  if ($send_data) {
    foreach($all_users as $row){
      if($row['user_one'] == $my_id){
          $query = $CI->db->get_where('users', array('user_id' => $row['user_two']));
          $results = $query->result_array();
          array_push($return_data, $results);
      }else{
          $query = $CI->db->get_where('users', array('user_id' => $row['user_one']));
          $results = $query->result_array();
          array_push($return_data, $results);
      }
    }
    return $return_data;

  }else{
    return $query->num_rows();
  }

} 


so to get all the friends with the date in which they became friends i used this code:

PHP Code:

function getfriends($my_id){
  $CI =& get_instance();
  $where = "user_one ='".$my_id."' OR user_two = '".$my_id."'";
  $CI->db->where($where);
  $query = $CI->db->get('friends');
  $CI->db->select('*');
  $CI->db->from('friends');
  $CI->db->join('users as b', 'friends.user_id = b.user_id', 'LEFT OUTER');
  $CI->db->join('users as c', 'friends.user_id = c.user_id' 'LEFT OUTER');
  $query = $CI->db->get();
  return $query->result_array();
} 


i have two friends but it returns 1 friend and me, i only want the 2 friends
can anyone help please.
Thanks in advance

i have a table called friends it has id, user_one, user_two and date

What do you use 'user_one' and 'user_two' for? That's not clear to me.

i have a table called friends it has id, user_one, user_two and date

What do you use 'user_one' and 'user_two' for? That's not clear to me.

user_one is the current user_id and user_two is the other user which is a friend with the user_id, but the current user myth be user_two
and user_one myth be the other user vise versa depending on who befriended who.
 it is a friendship system.

---

ok i did this and it worked i dont know if it will give me problems in the near future 

PHP Code:

function getfriends($my_id, $send_data){

  $CI =& get_instance();
  $where = "user_one ='".$my_id."' OR user_two = '".$my_id."'";
  $CI->db->where($where);
  $query = $CI->db->get('friends');
  //die(print_r($query->result_array()));
  $all_users = $query->result_array();
  $return_data = [];

  if ($send_data) {
    foreach($all_users as $row){
      if($row['user_one'] == $my_id){
          $CI->db->join('friends', 'friends.user_two = users.user_id');
          $query = $CI->db->get_where('users', array('user_id' => $row['user_two']));
          $results = $query->result_array();
          array_push($return_data, $results);
      }else{
          $CI->db->join('friends', 'friends.user_one = users.user_id');
          $query = $CI->db->get_where('users', array('user_id' => $row['user_one']));
          $results = $query->result_array();
          array_push($return_data, $results);
      }
    }
    return $return_data;

  }else{
    return $query->num_rows();
  }

} 


Why is your function in a library? Database operations should be in a model (or in the controller).
In a model, you don't need to define $CI. Simply use $this->db.
You can do it all in one query, without any JOINs.

PHP Code:

$this->db
->where('user_one', $my_id)
->or_where('user_two', $my_id);
$query = $this->db->get('friends');
$users = $query->result_array();
$my_friends = array();
foreach ($users as $user) {
    if ($user['user_one'] == $my_id) {
        $my_friends[] = array('id'=>$user['user_two'], 'date'=>$user['date']);
    }
    elseif ($user['user_two'] == $my_id) {
         $my_friends[] = array('id'=>$user['user_one'], 'date'=>$user['date']);
    }
}
if ($send_data) {
    return $my_friends;
}
else {
    return count($my_friends);
} 


This will return an array with the user_id's and dates of your friends. The ones you have chosen as friends, and the ones who have chosen you as their friend.

Why is your function in a library? Database operations should be in a model (or in the controller).
In a model, you don't need to define $CI. Simply use $this->db.
You can do it all in one query, without any JOINs.

PHP Code:

$this->db
->where('user_one', $my_id)
->or_where('user_two', $my_id);
$query = $this->db->get('friends');
$users = $query->result_array();
$my_friends = array();
foreach ($users as $user) {
    if ($user['user_one'] == $my_id) {
        $my_friends[] = array('id'=>$user['user_two'], 'date'=>$user['date']);
    }
    elseif ($user['user_two'] == $my_id) {
         $my_friends[] = array('id'=>$user['user_one'], 'date'=>$user['date']);
    }
}
if ($send_data) {
    return $my_friends;
}
else {
    return count($my_friends);
} 


This will return an array with the user_id's and dates of your friends. The ones you have chosen as friends, and the ones who have chosen you as their friend.

 no i was testing and writing some helper functions so i ended up writing this function in helpers.
Your code is very clean and it works thanks.
I also want each friend's data like their names, profile_pics  and usernames that's why, but  i will try to integrate it with more queries to get the friends data.