Mysql get values from table with join (or and) ?

Mysql get values from table with join (or and) ? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Mysql get values from table with join (or and) ? (/showthread.php?tid=1292)

Mysql get values from table with join (or and) ? - Marcel - 02-26-2015

Hi i have the following tables

table buddies |id|b1|b2|status|

(note status = 0/1/2 pending/accepted/blocked) also b1 always is the userid sending the request.

table messages |id|userid|message|

table accounts |userid|name|email|

basically id like to show all messages from 'table messages' that have a buddies status on 1 and are my buddies knowing that in 'table buddy' my userid can be in either b1 or b2 (not in both at the sames time) depending if i request or buddy has requested .

hope im clear on this

thanks

RE: Mysql get values from table with join (or and) ? - aurelien - 02-26-2015

Hi,

Try this (if i understood you correctly)

// Query for b1
SELECT * FROM messages m1 JOIN buddies b on b.b1=m1.userid and status=1

// Query for b2
SELECT * FROM messages m2 JOIN buddies b on b.b2=m2.userid and status=1

// Query for all messages
SELECT * FROM messages m1 JOIN buddies b on b.b1=m1.userid and status=1
UNION ALL
SELECT * FROM messages m2 JOIN buddies b on b.b2=m2.userid and status=1

RE: Mysql get values from table with join (or and) ? - obiron - 02-27-2015

(02-26-2015, 01:41 AM)Marcel Wrote: Hi i have the following tables

table buddies |id|b1|b2|status|

(note status = 0/1/2 pending/accepted/blocked) also b1 always is the userid sending the request.

table messages |id|userid|message|

table accounts |userid|name|email|

basically id like to show all messages from 'table messages' that have a buddies status on 1 and are my buddies knowing that in 'table buddy' my userid can be in either b1 or b2 (not in both at the sames time) depending if i request or buddy has requested .

hope im clear on this

thanks

Hi Marcel, You have already had a suitable reply that answers your question, but I would like to point out that this is a really bad database design. You would do better to have a table for BuddyLists, a table for Users and a table for UserBuddyList.

BL: BL_ID (int)
BL_ID=>1
BL_ID=>2

User: UserID (int), Name (char), dob (date) etc..
UserID=>1, Name=>"bob", dob=>2004-04-01
UserID=>2, Name=>"jim", dob=>1984-08-15
UserID=>3, Name=>"max" dob=>1971-06-25

UserBL: BL_ID (int) [FK to BL.BL_ID], UserID (int) [FK to User.UserID]
BL_ID=>1,UserID=>1
BL_ID=>1,UserID=>2
BL_ID=>2,UserID=>1
BL_ID=>2,UserID=>3

Bob is friends with Jim (BL_ID = 1)
Jim is friends with Bob (BL_ID = 1)
Bob is friends with Max (BL_ID = 2)
Max is friends with Bob (BL_ID = 2)

Max might know Jim because they both know Bob

Bob has 2 friends (count UserBL where UserID = 1)

You can extend this so that you can have more than 2 UserBLs for a BuddyList (i.e. Google circles)

Read up on 3rd Normal form - it is your friend when you are building databases

RE: Mysql get values from table with join (or and) ? - Marcel - 02-28-2015

Thanks for your help i have rewritten my code so that i pass ony one query to get the results this seems to work or though havnt completely tested it in thourough.

my code here.

Code:
$where = "user_messages.msg_user_id = $user_id OR user_buddys.bud_one_id = $user_id OR user_buddys.bud_two_id = $user_id ";

        $wherestat = "user_buddys.bud_status= '1'";

        $this->db->select('user_accounts.uacc_id, user_accounts.uacc_username, user_profiles.pro_uacc_fk, user_profiles.pro_birthday, 

        user_profiles.pro_town, user_messages.msg_id,etc.........) ;

        $this->db->join('user_buddys', 'user_messages.msg_user_id = user_buddys.bud_one_id OR user_messages.msg_user_id = user_buddys.bud_two_id');

        $this->db->join('user_profiles', 'user_profiles.pro_uacc_fk = user_accounts.uacc_id');

        $this->db->group_by ('user_messages.msg_id');

        $this->db->order_by ('user_messages.msg_id','desc');

        $this->db->where($where);

        $this->db->where($wherestat);

        $result = $this->db->get();

        if ($result->num_rows() > 0):

            return $result->result();

        else:

            return false;

        endif;

         return $query->result();

i don't seem to see why i should have an extra table ?
surely i can query my buddies table to find friends of friends using and 'OR' and 'AND'
i already query this table to find out if a profile page i'm on is a friend of mine or not by

Code:
$query = $this->db->query("SELECT bud_one_id, bud_two_id, bud_status FROM user_buddy WHERE bud_status = '1' AND (bud_one_id = $user_id OR bud_two_id = $user_id) AND (bud_one_id = $freindid OR bud_two_id = $freindid)");

please explain thx