How to link two tables in MySQL

Question

I have two tables as below:

Table-1-contacts
-id (primary key, AUTO_INCREMENT)
-firstname(varchar)
-lastname (varchar)
-email (varchar)

Table-2-comments
-id (primary key, AUTO_INCREMENT)
-fullname (varchar)
-email (varchar)
-comment(text)
-date(varchar)

I want to write php and MySQL query to get and write comments which will be connected to the contact.I want to to get output as below:

url-contact.php?id=1
name-contact1
comment-contact1 is great.

url-contact.php?id=2
name-contact2
comment-contact2 is okay.

How can i do this? First I need to write comments which will be connected to each contact when entering from that contacts page. Second I need to get all the comments of that contact when that contact is accessed.

I appreciate the help from you now. Thanks in advance.

Answer 1

Assuming that your tables already have data in them, the best I can imagine doing is to select it based upon the email.

In order to complete this, do a SELECT email FROM contacts WHERE id='id'. Then using the return from that, then SELECT * FROM comments WHERE email='email' in order to retrieve all the associated comments.

The only thing you would need to watch out for is empty email values, in which case you most likely will need to redesign your approach using the answer suggested by Patashu.

Answer 2

You should design your database schema so that the two tables have a natural join. e.g. every comment should have a contact_id which is the id of the contact who posted it. Then you can do things like

select * from comment where comment.contact_id = (insert contact_id here)

You can't use contact name, because what if two contacts have the same name? (This happens very frequently on Facebook, for example)