mysql - Inserting Persons with IDs in one query? -

i need add data mysql database that:

person: pid, nameid, titleid, age

name: nameid, name

title: titleid, title

i don't want have names or title more once in db didn't see solution last_insert_id()

my approach looks that:

insert ignore name(name) values ("peter"); insert ignore title(title) values ("astronaut"); insert person(nameid, titleid, age) values ((select nameid name name = "peter"), (select nameid name name = "astronaut"), 33); 

but guess that's quite dirty approach!? if possible want add multiple persons 1 query , without having more 1 times in db. possible in nice way? thanks!

you put title , name 2 columns of table , then:

• set 1 unique index on each column if don"t want have 2 titles or 2 names identical in db
• or set unique index on (title,name) if don't want have 2 entries having both same name and same title.

---

if really want have separate tables, suggested in post, wrapping insert statements in transaction allow rollback if detect duplicate somewhere.

see design dilemma: if e-mail address used, send e-mail "e-mail address registered", can't because can't add duplicate table appear same problem, having name & email instead of name & titles.

start transaction; insert title(value) values ("prof."); select last_insert_id() @title_id;      -- instead of using user-defined variable,      -- should able use last_insert_id      -- equivalent host language mysql driver. insert username(value) values ("sylvain"); select last_insert_id() @username_id;     -- instead of using user-defined variable,      -- should able use last_insert_id      -- equivalent host language mysql driver.  insert account(username_id, email_id) values (@username_id,@title_id);  commit; 

see last_insert_id()

---

a third solution select before doing insert see in entry present. wouldn't push check-before-set approach @ least, require query superfluous if use correctly indexes.