CODEWITHSUNDEEP
phpmysqlcodeigniter
georgesamper
georgesamper

Reputation: 5179

MySQL UNION query separation

I have this query that uses a UNION:

$this->db->query('
        SELECT DISTINCT users.user_pic, users.id, users.username, contacts.accepted 
        FROM users
        LEFT JOIN contacts ON users.id = contacts.user_1
        WHERE contacts.user_2 = ' . $this->session->userdata('user_id') . '
        UNION DISTINCT
        SELECT DISTINCT users.user_pic, users.id, users.username, contacts.accepted 
        FROM users 
        LEFT JOIN contacts ON users.id = contacts.user_2
        WHERE user_1 = ' . $this->session->userdata('user_id')
);

Is there a way to see if the sessions 'user_id' was encountered in contacts.user_1 or contacts.user_2? Maybe by changing the query, or if i can use some if statements in the view?

Thanks in advance

George

Upvotes: 1

Views: 133

Answers (2)

TSquared
TSquared

Reputation: 95

I typically use:

$id = $this->session->userdata('user_id');

then use $id in the query. Reads cleaner and on MySQL at least, if the query fails, it lists the value of $id. Never tried calling the session in the query before.

Upvotes: 1

Haim Evgi
Haim Evgi

Reputation: 125526

yes you can add another field to select , that you can recognise like (1,2)

$this->db->query('
        SELECT 1 as table_id,DISTINCT users.user_pic, users.id, users.username, contacts.accepted 
        FROM users
        LEFT JOIN contacts ON users.id = contacts.user_1
        WHERE contacts.user_2 = ' . $this->session->userdata('user_id') . '
        UNION DISTINCT
        SELECT 2 as table_id,DISTINCT users.user_pic, users.id, users.username, contacts.accepted 
        FROM users 
        LEFT JOIN contacts ON users.id = contacts.user_2
        WHERE user_1 = ' . $this->session->userdata('user_id')
);

Upvotes: 2

Related Questions