Connect two MySQL tables

I have a website that is using SMF forum (simplemachines.org). I have been using it also as the site's login by leveraging the SSI.php file which is basically an API for the forum.

Now that I am this far in my site project, I want to add additional functionality. One thing I want to do is add lots of custom variables that a user can have, which is difficult when modifying the built-in users table of SMF. I also want to allow users to join "teams". A team can have multiple users in it.

I have been playing around with PHP and MySQL and have learned how to create a user registration script and add a user to a simple single database table (no longer using SMF).

The database table has:

ID, Username, Password (not plaintext), Date

How do I take my SQL knowledge to the next level; I want to now create a table called Teams which will logically hold this data: An ID, a Name, Admin True/False (I want multiple team members to be admins if needed).

I figure a form should be created that will make a new Team. When submitted, if the team name doesn't already exist, create a new team, and the user that MADE the team should automatically be the first admin.

1) I have no idea how to link 2 tables, which is the entire point of this topic. When I create a user profile page, I want it to list the teams he is in.

This means it needs to take his user ID from the Users tabl开发者_StackOverflow社区e and connect it to the Teams table and if it finds his ID in that Teams table, return the array containing all of his teams he is in and I'll display them how I want.

I think that I posted way too much detail but it will help you help me. Additionally, that above knowledge will help me with everything else I want to do, like adding multiple teams to some other table. It's all the same once you know how to do it!!

Thanks!

2) Just realized another problem of mine, once I have this custom database, I lose the ability to provide single sign-on on the forum, which is awesome. Two accounts to interact on website and interact on forum is really stupid. Does phpBB or other "Big Player" Forum have a way to easily use custom database logins?

---

One way of 'linking' the two tables would be to create a membership table such as

MembershipTable
-----------------------------
| UserID | TeamID | IsAdmin |
-----------------------------

This way, the user's details is kept in the Users table and team definition information stays in the teams table.

If for a user with an userid $id, you want to find all the teams it is a member of, you can run a simple SQL query:

SELECT t.team_name
FROM   MembershipTable m
LEFT JOIN team_table t on M.userID = t.UserID

Should be simple to infer from the above what the definitions of the user and team tables should look like.