Most efficient way to do Mysql Multi select (and insert if not exists)

Question

I have 20 tables with 2 fields, id and name description

Colors
id  name
1 black
2 blue 
...

Sizes
id name  
1 100
2 105
...

Now i have the main table with 21 fields

Main table `shoes`

id Colors Sizes style ...
1    2     24     11

I have a form post with 20 text inputs (one for each kind of feature, color, size..) what i want is to learn the most efficient way to look at 20 features tables (Select id from colors where name='blue') and get the id value, if not exists, create it ( insert into colors (name) values (pink) ) and then get the id ,once I have the 20 values will do the big insert into the main field

score 0 · Answer 1 · answered Jan 24 '12 at 22:06

0

Use the INSERT ... ON DUPLICATE KEY UPDATE syntax.

answered Jan 24 '12 at 22:06

FtDRbwLXw6

27,774
13
70
107

thanks for your answer, i know your way, but mean this to do 20 differents sentences? – Colas Jan 24 '12 at 22:15
I have no idea what you're asking. – FtDRbwLXw6 Jan 24 '12 at 22:20
i mean.. should i do 20 times $db->(insert.. on duplicate key) ? is this the most efficient way? – Colas Jan 24 '12 at 22:25

Most efficient way to do Mysql Multi select (and insert if not exists)

1 Answers1