CODEWITHSUNDEEP
phpmysql
lll
lll

Reputation: 476

MySQL Insert into table with different column name

I have 3 tables. "machines", "products", "machine_products".

The idea in my mind is to store machine information in "machines" table and set "machine_id" column as PRIMARY & UNIQUE and do same with "products" table the column name here is "product_id".

One machine may have one or more product so they both match in "machine_products" table

Table samples are below.

machines table

+------------+------------------+
| machine_id |   machine_code   |
+------------+------------------+
|      1     | C01.C03.C23 M.1  |
+------------+------------------+
|      2     | C07.08.09.10 M.1 |
+------------+------------------+
|      3     | C11.12 MONT.1    |
+------------+------------------+
|      4     | C13.14.21 MONT.1 |
+------------+------------------+
|      5     | C22 MONT.1       |
+------------+------------------+

products table

+------------+--------------+
| product_id | product_code |
+------------+--------------+
|      1     | C01.00       |
+------------+--------------+
|      2     | C01.11       |
+------------+--------------+
|      3     | C01.21       |
+------------+--------------+
|      4     | C03.00       |
+------------+--------------+
|      5     | C03.01       |
+------------+--------------+

machine_products table

+----+------------+------------+
| id | machine_id | product_id |
+----+------------+------------+
|  1 |      1     |     70     |
+----+------------+------------+
|  2 |      1     |     73     |
+----+------------+------------+
|  3 |      1     |     78     |
+----+------------+------------+
|  4 |      1     |     83     |
+----+------------+------------+
|  5 |      2     |     100    |
+----+------------+------------+
|  6 |      2     |     208    |
+----+------------+------------+
|  7 |      3     |     101    |
+----+------------+------------+
|  8 |      3     |     108    |
+----+------------+------------+
|  9 |      3     |     112    |
+----+------------+------------+
| 10 |      4     |     113    |
+----+------------+------------+

My problem is I want to insert data to "machine_products" table by using "machine_code" column data

How do I add records to the machine_products table when what I have are the product_code and machine_code?

Upvotes: 0

Views: 1273

Answers (3)

Arulraj
Arulraj

Reputation: 11

1) Insert the data in Machines table Machines table image You get the machine table insert id ($machine_id)

2) Insert in the Products table product table image and get the product_id ($product_id) Note : You should update the $machine id in product table

3) Insert the machine_products table machine_products table image Note : Here you should update the $machine id & $product id

Upvotes: 0

Jayish
Jayish

Reputation: 76

Can you try this

Insert into machine_products ( machine_id, product_id) values( (select machine_id from machines where machine_code = $machine_code), (select product_id from products where product_code=$product_code))

You can the substitute $machine_code and $product_code for your own values

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133380

You could use a cartesian product between machine and product
and insert select 

insert into  machine_products ( machine_id, product_id)
select  a.id, b.id 
from machine a 
cross join products b

this way all the possibile combination betwen machine and product are inserted in machine_products table

Upvotes: 0

Related Questions