Question 63732 in PhpMyAdmin Question posted in
The official documentation can be found here.

How to insert name filed in the query, but to the table to pass the corresponding id of that name – Phpmyadmin

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    in case you just want to give the genre_name and band_name and rest the query must understand to first insert the genre_name and then the band_name with the foreign key from the table genres_table..

    you can manage the duplication by transaction lock..

    INSERT INTO genres_table(genre_name) VALUES ('genre_name'); 
SET @last_id_in_table1 = LAST_INSERT_ID();
INSERT INTO bands_table(band_name,genre_id) VALUES ('band_name',@last_id_in_table1);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search