Add a contact script. Confused with MySQL Query

[ade234uk]

As you can see from the query below the users contacts are displayed.
The user_id session is used to query MySQL to pull the contacts information from the db.

This works fine. But what I really wanted was a query that displayed the company information in the other users control panel.

To keep it simple.
Company A adds Company B to their contacts. Company B information is displayed in Company A control panel (This works)

What I need:
I need the reverse so if Company B logs in, Company A information is displayed in Company B control panel.

$result = mysql_query("SELECT a.id, a.company_id, a.contact_id, a.status, b.user_id, b.company_name, b.address, b.town, b.county, b.telephone_number1, c.county_id, c.county_name FROM contacts a
LEFT JOIN users b ON a.contact_id = b.user_id
LEFT JOIN county c ON b.county = c.county_id
WHERE a.company_id='". $_SESSION['user_id'] ."' AND a.status='1'");

My table has the following scheme
id | company_id | contact_id | status

Hope this above makes some sort of sense.

[johanafm]

ade234uk;10986396 wrote:

The user_id session is used to query MySQL to pull the contacts information from the db.

WHERE a.company_id='". $_SESSION['user_id'] ."' AND a.status='1'");  

You use a user_id which is the same as a company id, which means that either several users from the same company are treated as being the same person, or you can only have one person per company.

ade234uk;10986396 wrote:

To keep it simple.
Company A adds Company B to their contacts. Company B information is displayed in Company A control panel (This works)

What I need:
I need the reverse so if Company B logs in, Company A information is displayed in Company B control panel.

Really? Let's say I add you because you provide goods or services that I want in the future and because of that I plan to contact you the next month. So when I find your company, I add it to my contacts to have the information handy.
You however, still have no idea who I am. But suddenly I show up among your contacts, which I'm not.

And even if this scenario can't happen, I still believe it's a crappy user experience to have stuff appear in my contact list due to other people making choices. At the very least, put "people having you/your company registered as a contact" in a separate list.

Also, I believe you have to change your entire table structure to make this work.

Tables:
company (id, name, address, phone, email etc)
Phone and email assuming they exist for the company with a one-to-one relation, such as [e-mail]. Otherwise you need email and phone tables as well.

user (id, company_id, name, phone, email, address etc)
As with company, storing phone and email in this table requires a one-to-one relationship between user and this information. Else you need separate tables for phone and email. In the same way, if a user can work for more than one company, you can't store company_id in the user table but will need the table company_user (user_id, company_id)

contact (user_id, contact_id, status)
Note that both user_id and contact_id relates to the user table. All the contact information you need is there. And when you have this structure, you can reverse the process from listing a user's contacts or lisiting those who have a user set as their contact.

company