• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
inner join with OR using codeigniter

#1
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
Reply

#2
Quote: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.
Reply

#3
(09-24-2019, 01:27 PM)Wouter60 Wrote:
Quote: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();
  }


Reply

#4
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.
Reply

#5
(09-25-2019, 02:53 AM)Wouter60 Wrote: 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.
Smile 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.
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.