CODEWITHSUNDEEP
mysqlrelationshiprelation
Tomasz Kasperczyk
Tomasz Kasperczyk

Reputation: 2093

Using relations in MySQL

Let's say that I have the following tables:

Elements:
  (int) ID
  (int) Name_id

Names:
  (int) ID
  (varchar) Name

The Elements.Name_id has an internal relation with Names.id. The user enters an element name in a html form. Then a php script inserts the element name to the Elements table. And here comes my question:

Is it possible to specify the element name instead of the element name_id in the insert query? If not I will have to:

SELECT `ID` FROM `Names` WHERE `Names`.`Name` LIKE "$php_variable";
...
INSERT INTO `Elements` SET `Name` = "$php_variable";

Of course the second $php_variable is a result from the previous query.

Upvotes: 0

Views: 52

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can just do:

insert into elements(name)
    select n.name
    from names n
    where n.id = $php_variable;

Now, having said this, do not take this approach. Your data structure is wrong. You want to connect to names.id, not names.name, in most cases. (There are some exceptions where copying the name might be one solution for a slowly changing dimension, but I doubt that is your use-case.)

So, fix the data structure. The create table statements would look like:

create table names (
    name_id int auto_increment primary key,
    name varchar(255) unique
);

create table elements (
    element_id int auto_increment primary key,
    name_id int,
    foreign key (name_id) references names(name_id)
);

Then use an explicit JOIN to get the name when you need it.

Note: I changed the ids of the table to be "tablename_Id". This is not required, but I find it helpful to have foreign keys and primary keys have the same names.

Upvotes: 1

Related Questions