Insert mysql on foreign key id php

问题:

I have 2 tables, clubs and fixtures

clubs

id (int)
name (text)

fixtures

id (int)
hometeam (int, foreign key to clubs id)
awayteam (int, foreign key to clubs id)
datetime (datetime)

Each fixtures record uses an ID for the hometeam and awayteam as per the foreign key relationship.

I need to do an insert into the fixtures table, but I only have the hometeam name not the hometeam id. Is there a way of doing this through the foreign key relationship, without having to separately lookup the relevant id number?

回答1:

There is nothing wrong with looking for foreign key value through a separate select query:

INSERT INTO `fixtures`
    VALUES ( NULL,
             (SELECT `id` FROM `clubs` WHERE `name` = 'NAME'),
             AWAYTEAM_ID,
             CURRENT_TIMESTAMP
    );

回答2:

You need to do in 2 steps:

• Insert hometeam in clubs table (get hometeamID if already have, otherwise insert and get ID)
• Then insert into fixtures table