PDA

View Full Version : Working with many-to-many relationships


Eiolon
01-25-2007, 04:30 PM
I am about to start my first experience with many-to-many relationships using PHP and MySQL.

My project is to create an events registration form. As you know, there can be many events and many participants attending many events.

I am not sure on how to structure my database and tables so I will first show you what I have:


create table programs (
program_id int not null primary key auto_increment,
program_name varchar(100) not null,
program_date varchar(25) not null,
program_time varchar(25) not null,
program_coordinator varchar(100),
program_seats int not null
);

create table participants (
participant_id int not null primary key auto_increment,
participant_name varchar(100) not null,
participant_phone varchar(12) not null
);

I know that I need a middle table to join the two.


create table programs_participants (
program_id int references program(id),
participants_id int references participants(id),
primary key (program_id, participants_id)
);

My problem is, how do I submit to both the participants AND the programs_participants table together? Or is this not possible? The participants are not already in the database when we register them. We enter their personal info and select their desired events from checkboxes on the same page.

Thanks for your help.

davidj
01-25-2007, 08:17 PM
ok look at it like this

in your programs table you will have an auto increment id

imagine having these programs in a dropdown and a textfield which would allow you to insert a participant so all you do is insert the program auto id into the participants table so you have a table with a relationship to the progames table. You now have the power to do a join on both tables with in a select statement