PHP & MySQL: Linking two tables (part one)...?

Question

Hi folks...

I'm a Graphic Designer & Web Developer. I'm creating a site that will help me better organize my clients and their projects. I've created a "client_table" and now I want to create a "project_table". There are already too many fields in the "client_table" and I feel if I add any more fields this whole project is going to get really messy and confusing.

So basically I want two tables, one of which I already have:

1. client_table

2. project_table

I want to be able to link these two tables. I'm trying to figure out the best way to do this.

1. My "project_table" will have project_id as the primary field. Do I need to add this field to the "client_table" and then do a join?

Answer 1

Yes, you are on the right track. You need to put the same field name and type in the client_table as project_id as it will be the foreign key. Then you can use this to join tables.  There different ways to right it but this should work on most MySQL versions.

$project_id = 123456;

SELECT * FROM client_table ct, project_table pt WHERE ct.project_id = pt.project_id and pt.project_id = $project_id;

Hope this helps.