PHP retrieve distinct data from database based on two rows [duplicate]

Question

I have table named messages with some rows:

+---------+
| columns |
+---------+
| id      |
| from    |
| to      |
| body    |
| date    |
+---------+

What I want is to retrieve a list of users I message or users who messaged me.

public function get_users_m($where)
{
    $this->db->select('*');
    $this->db->from('messages');
    $this->db->where($where); // to = my_id or from = my_id 
    $this->db->group_by('from, to');

    return $this->db->get()->result_array();
}

I made that using Codeigniter but the problem is when I reply, for example to C, I get A (me) send message to C and C send message to A and I don't want that I want just C one time because it doesn't matter if i'm the one who sent him message or he is the one who send me the message it still the same user.

Can you break up your last sentence into two or more sentences? It's long and hard to read. — user151841
– user151841, Commented Dec 15, 2015 at 14:40
@user151841 i guess the important thing is to understand the idea. this is what i need . i have a table named messages and i want a list of all users i exchange messages with. i hope you get the idea. — ler
– ler, Commented Dec 15, 2015 at 14:45

Community · Accepted Answer · 2017-05-23 12:00:16Z

3

What about this

public function get_users_m()
{
    $my_id = 5; # some id
    $this->db->select('*');
    $this->db->from('messages');
    $this->db->where('from == $my_id');
    $this->db->or_where('to == $my_id'); # add this or_where Clause
    $this->db->group_by('user_id');
    $query = $this->db->get();
    $result = $query->result_array();
    return $result;
}

Check comment of Tpojka

edited May 23, 2017 at 12:00

CommunityBot

11 silver badge

answered Dec 15, 2015 at 14:52

Abdulla Nilam

39.8k18 gold badges85 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tpojka Over a year ago

Comparision should be with one equal sign (i.e. $this->db->where('from = $my_id');) or in CI way $this->db->where('from', $my_id);.

ler Over a year ago

@Tpojka yes i know i remove one equal sign, thanks :)

Alpesh Panchal · Accepted Answer · 2015-12-15 14:53:55Z

1

You are using user_id and profile_id both. You have mentioned which field belongs to other user. Is it user_id or profile_id.

Let's consider that other user id is profile_id.

Following query will help you get distinct profile_id who replied to you or you replied to them but not yours.

public function get_users_m($where)
    {
        $this->db->select('profile_id');
        $this->db->from('messages');
        $this->db->where('profile_id != your profile id'); 
        $this->db->group_by('profile_id');
        return $this->db->get()->result_array();
    }

answered Dec 15, 2015 at 14:53

Alpesh Panchal

1,71313 silver badges9 bronze badges

Collectives™ on Stack Overflow

PHP retrieve distinct data from database based on two rows [duplicate]

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related