[Resolved] Linking Tables in Database

[bpat1434]

Hey guys.

I'm working on a script for email piping. What I'd like to do is allow users to sign-up for a list, and unsubscribe from the list.

To be honest, it's really kind of easy (not tested yet) or so it seems. What I need to do is create a database called Email and in it hold a tables called: users, list1, list2, list3 etc. for as many lists as the user chooses.

Now, I'd like to allow users to log-in (using sessions (lookin for good tutorials on it... wink wink nudge nudge)) and view all lists they are subscribed to.

Now, I need to link the tables so that a user can view all subscriptions. So the search would have to go through each database and if the user is in there, then they list it and such.

So here's my question:
How is it I can "link" the tables together?
I would want the link to be on the FirstName and LastName column or email address column. most likely, Email address.

The table setup would be as follows:

users
id
FirstName
LastEame
Email
Username
Password

List#
id
FirstName
LastName
Email

Thanks for the help guys.

~Brett

[mattotto77]

Hi

I think you would be replicating data in several tables that isnt really necessary possibly something like

-Users-
Id
Last name
First name
Email
username
password

-list#-
Id

then you would have a script that would check to see if the Id existed in all lists, or depending on how many lists you have you might as well have just the 2 tables ie

-subscription list-
Id
list1
list2
list3

This would make it easier for updating and querying etc.

[mourisj]

You need (only) 3 tables:

• Users (keep as is)

• Lists
  list_id
  list_name
  list_description
  etc.

• subscriptions
  user_id
  list_id
  etc. (e.g. subscription_date, end_subscription_date, ...)

With this design, you can have many users and as many lists as you want, you will never need to change your design!

JJ Mouris

[bpat1434]

Thanks for the help, but I'm a bit lost.

If I use 3 tables like you said, how would I see link them together?

I mean, what I want is the following:

• A user sends an email to: [e-mail].

• That email is forwarded (through email piping) to a php script which does 2 things:
-- Checks to see if that user is subscribed to that list (so there is no spam)
-- Retrieves all subscribed emails to that list and puts out a form-mail to them.

Now, I have the php script to retrieve the information & check as well as email all users.

My problem is checking to see what subscriptions they have. Could you explain your example please?

THe only problem I'm seeing is adding one user to multiple lists. I wish to do this using an HTML form, and using an email interface as well. Any ideas.

Thanks.

~Brett

[Roger_Ramjet]

Try this one, works right-out-of-the-box.

http://www.sitepoint.com/print/users-php-sessions-mysql

I've attached the code download that Kevin Yank kindly supplies with his article. I use it and it works great

[Roger_Ramjet]

You use a JOIN in your query to get the data you are after. Read this to learn about joins http://dev.mysql.com/doc/mysql/en/JOIN.html

I'm not going to do it for you as you need to learn how to do it yourself, but I will help with any problems once you have tried.

[bpat1434]

Thanks, I'll look at joining tables...

~Brett

[bpat1434]

Ok, well having a small question using the "join" method.

I want to select all subscriptions where the user is subscribed. So my query looks like so:

$query = "SELECT users.*, subscriptions.* FROM `users`, `subscriptions` WHERE users.Email=$email, subscriptions.User_ID=users.User_ID";
$result = mysql_query($query, $dbc);

Would that work?

Now, I how is it I can select all the subscriptions that the user has? If they are based upon User_ID in the subscription table. I think I have to run a separate query, but not sure. Help would be appreciated.

Yes I read the mySQL article, but that didn't help me much. Thanks.

~Brett

Hey got it to work. yay!!!

Thank you very much for your help. Most appreciative.

~Brett