Working with many-to-many relationships

Report · Jan 25, 2007

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.

Report · Jan 25, 2007

> 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.

What you need to do is a multi-step insert.

First, you insert the new participant into the participant table, then use
the @@identity command to get the uniqueID of that newly entered record.
Then you can take that uniqueID to build the entry for the
programs_participants table.

If you use a stored procedure, you should be able to do all of that with
only having to create one call to the DB from your Application.

_Darrel