CODEWITHSUNDEEP
phpmysqlinsertforeign-keys
user989952
user989952

Reputation: 683

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?

Upvotes: 1

Views: 500

Answers (2)

revo
revo

Reputation: 48751

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
    );

Upvotes: 2

Naveed Ramzan
Naveed Ramzan

Reputation: 3593

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

Upvotes: 0

Related Questions