I have 2 tables:

genres_table:                     bands_table:

genre_id | genre_name            band_id | genre_id | band_name
   1     |   Rock                   1    |    8     | Blink 182
   3     |   Jazz                   3    |    1     | Foo Fighters
   8     |   Punk                   4    |    1     | RHCP

Genre_id is a foreign key in bands_table taken from genre_id in genres_table.

I would like to insert new rows to bands_table, currently I do it like this:

INSERT INTO bands_table (genre_id, band_name) VALUES(1, "Rammstein") - band_id column is auto-incremented by phpmyadmin, so I don't insert it.

However, I would like to insert not genre_id and band_name, but genre_name and band_name.
But, I need to keep genre_id in the table since it’s connected by a FK and it needs to be so.

How can I achieve this? So when I insert ("Rock", "Rammstein") it will automatically compare Rock to gender_id = 1 , and will insert instead of "Rock", 1 to gender_id.

>Solution :

You need to insert using a select query:

insert into bands_table (genre_id, band_name)
select genre_id, 'Rammstein'
from genres_table
where genre_name = 'Rock';

Note, string literals should be specified with single quotes.