MySQL - How to insert into table that has many-to-many relationship

Question

I have a table of persons. Each person has a property and many persons may have a certain property. So this is a many-to-many relationship. This is the schema:

CREATE TABLE persons (   person_id int(11) NOT NULL AUTO_INCREMENT,   firstname varchar(30) NOT NULL,   lastname varchar(30) NOT NULL,   PRIMARY KEY (person_id) );  CREATE TABLE properties (   property_id int(11) NOT NULL AUTO_INCREMENT,   property varchar(254) NOT NULL UNIQUE,   PRIMARY KEY (property_id) );  CREATE TABLE has_property (   person_id int(11) NOT NULL,   property_id int(11) NOT NULL,   PRIMARY KEY (person_id,property_id),   FOREIGN KEY (person_id) REFERENCES persons (person_id),   FOREIGN KEY (property_id) REFERENCES properties (property_id) ); 

Now lets say i want to insert to the database this person:

• firstname:'John'
• lastname:'Doe'
• properties:'property_A','property_B','property_C'

persons

+-----------+-----------+----------+ | person_id | firstname | lastname | +-----------+-----------+----------+ |         1 | John      | Doe      | +-----------+-----------+----------+ 

properties

+-------------+------------+ | property_id |  property  | +-------------+------------+ |           1 | property_A | |           2 | property_B | |           3 | property_C | +-------------+------------+ 

has_property

+-----------+-------------+ | person_id | property_id | +-----------+-------------+ |         1 |           1 | |         1 |           2 | |         1 |           3 | +-----------+-------------+ 

So far the best thing i have thought is to do a regular insert in the persons table:

INSERT INTO persons (firstname,lastname) VALUES ('John','Doe'); 

and then do a select to find the id of the person i just inserted

SELECT person_id FROM persons WHERE firstname='John' AND lastname='Doe'; 

in order to insert into the other two tables (because i need to know the person_id). But i think there must be a better way, isn't it?

Answer 1

Here is what i ended up doing. I hope it helps someone.

INSERT INTO persons (firstname,lastname) VALUES ('John','Doe'); SET @person_id = LAST_INSERT_ID();  INSERT IGNORE INTO properties (property) VALUES ('property_A'); SET @property_id = LAST_INSERT_ID(); INSERT INTO has_property (person_id,property_id) VALUES(@person_id, @property_id);  INSERT IGNORE INTO properties (property) VALUES ('property_B'); SET @property_id = LAST_INSERT_ID(); INSERT INTO has_property (person_id,property_id) VALUES(@person_id, @property_id);  INSERT IGNORE INTO properties (property) VALUES ('property_C'); SET @property_id = LAST_INSERT_ID(); INSERT INTO has_property (person_id,property_id) VALUES(@person_id, @property_id);