i've been working on setting small site users log in , post messages. i'm in process of setting table stores contacts (so users can view , send messages each other on board). i'm done configuring have run roadblock.
when user registers first time, entry created in each of 2 tables; users , contacts. users table stores username , password stuff, contacts table stores directory of people on system , whether they've been added other people's contact lists. tables this; mysql> select userid, firstname, lastname, username users; +--------+-----------+----------+----------+ | userid | firstname | lastname | username | +--------+-----------+----------+----------+ | 63 | chris | smith | csmith | | 64 | susan | smith | ssmith | | 65 | roger | smith | rsmith | | 66 | diane | smith | dsmith | +--------+-----------+----------+----------+ 4 rows in set (0.00 sec) mysql> select * contacts; +----------+---------+-----------+ | username | contact | confirmed | +----------+---------+-----------+ | csmith | csmith | 0 | | ssmith | ssmith | 0 | | rsmith | rsmith | 0 | | dsmith | dsmith | 0 | | csmith | dsmith | 2 | | dsmith | csmith | 2 | | dsmith | ssmith | 1 | | dsmith | rsmith | 1 | +----------+---------+-----------+ 8 rows in set (0.00 sec) users able authenticate, they're able add , remove each other no problem. "confirmed" column stores state of contacts. 0 used indicate initial entry in contacts made user upon registration. 1 indicates if invite has been sent username contact has not been confirmed yet. 2 indicates contact has been confirmed. doing select query on different parts of site, can show user's contact list or list of people on system may add haven't yet. works fine except directory list of uninvited contacts.
the initial list of contacts should composed of select query's output users have confirmed state of 0, should omit user's own entry (so, shouldn't adding let's say), , (the part that's vexing me), should omit confirmation of 1 (invitation sent) or 2 (contact confirmed) own username.
so, csmith should see in directory of users ssmith , rsmith should not see dsmith because dsmith has been added contacts (confirmation of 2 @ 5th entry in contacts).
so, have been having hard time thinking of way wright query directory lookups given these rules. how can create rule does;
select contact contacts username!=$authenticated_user , confirmation='0'
this give me list of users on system except own, people who've not been invited yet.
but pulls out
select contact contacts username=$authenticated_user , confirmation='1' , confirmation='2' from output list of invited and/or added users doesn't show more?
any appreciated.
select distinct `contact` test.test `contact` not in (select `contact` test.test `username` = "csmith"); any entry mutual friendships given in format (a -> b) , (b -> a), invites given in format (a -> b) simplifying testing - have user doesn't have $username (p.ex. "csmith") username in contact entry.
(this exclude user default connected himself via (a,a,0).)
